数据库应用基础

学习情景6事务和触发器本章任务任务一事务的规划与设计模拟实现银行转帐功能批量插入学生考试成绩办理毕业学生离校手续任务二客户回访管理系统触发器的规划与设计本章目标学习触发器的基本用法掌握触发器的基本结构学习事务的基本用法掌握事务的基本结构为什么需要事务5-1银行转账银行转账问题：假定资金从账户A转到账户B，至少需要两步：账户A的资金减少然后账户B的资金相应增加账户A账户B假定张三的账户直接转账1000元到李四的账户为什么需要事务5-2CREATETABLEbank(customerNameCHAR(10),--顾客姓名

currentMoneyMONEY--当前余额)GOALTERTABLEbank

ADDCONSTRAINTCK_currentMoneyCHECK(currentMoney>=1)GOINSERTINTObank(customerName,currentMoney)VALUES('张三',1000)INSERTINTObank(customerName,currentMoney)VALUES('李四',1)创建账户表，存放用户的账户信息添加约束：根据银行规定，账户余额不能少于1元，否则视为销户张三开户，开户金额为1000元；李四开户，开户金额1元为什么需要事务5-3目前两个账户的余额总和为：1000+1=1001元为什么需要事务5-4模拟实现转账从张三的账户转账1000元到李四的账户/*--转账测试：张三转账1000元给李四--*/--我们可能会这样编写语句--张三的账户少1000元，李四的账户多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='张三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO--再次查看转账后的结果SELECT*FROMbankGO请问：

执行转账语句后，张三、李四的账户余额分别是多少？张三的账户没有减少但李四的账户却多了1000元1000＋1001＝2001元

总额多出了1000元！为什么需要事务5-5--张三的账户减少1000元，李四的账户增加1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='张三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO发生错误的原因执行失败，张三账户余额还是1000元继续往下执行：执行成功，李四账户余额变为1001元如何解决呢？使用事务UPDATE语句违反约束：

余额>=1元什么是事务事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作多个操作作为一个整体向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元转账过程就是一个事务它需要两条UPDATE语句来完成，这两条语句是一个整体如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元事务的特性事务必须具备以下四个属性，简称ACID属性：原子性（Atomicity）一致性（Consistency）隔离性（Isolation）永久性（Durability）事务是一个完整的操作事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行当事务完成时，数据必须处于一致状态并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务事务完成后，它对数据库的修改被永久保持如何创建事务2-1使用SQL语句管理事务开始事务提交事务回滚（撤销）事务ROLLBACKTRANSACTIONCOMMITTRANSACTIONBEGINTRANSACTION如何创建事务2-2一旦事务提交或回滚，则事务结束判断某条语句执行是否出错：使用全局变量@@ERROR@@ERROR只判断当前一条T-SQL语句执行是否有错为了判断事务中所有T-SQL语句是否有错，可以对错误进行累计如：事务可以嵌套SET@errorSum=@errorSum+@@ERROR事务模式分类显式事务用BEGINTRANSACTION明确指定事务的开始最常用的事务类型隐性事务通过设置SETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开其后的T-SQL语句自动启动一个新事务提交或回滚一个事务后，下一个T-SQL语句又将启动一个新事务自动提交事务SQLServer的默认模式每条单独的T-SQL语句视为一个事务BEGINTRANSACTION

/*--定义变量，用于累计事务执行过程中的错误--*/DECLARE@errorSumINTSET@errorSum=0--初始化为0，即无错误/*--转账：张三的账户减少1000元，李四的账户增加1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='张三'SET@errorSum=@errorSum+@@ERROR--累计是否有错误UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'SET@errorSum=@errorSum+@@ERROR使用事务解决银行转账问题4-1从张三的账户转出1000元，存入李四的账户中开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）累计是否有错误IF@errorSum<>0--如果SQL语句执行出错

BEGINPRINT'交易失败，回滚事务'ROLLBACKTRANSACTIONENDELSEBEGINPRINT'交易成功，提交事务，写入硬盘，永久的保存'

COMMITTRANSACTION

ENDGOPRINT'查看转账事务后的余额'SELECT*FROMbankGO根据执行是否有错误，决定提交事务，或撤销事务如果有错，则回滚操作，事务结束如果成功，则提交操作，事务结束从张三的账户转出1000元，存入李四的账户中使用事务解决银行转账问题4-2使用事务解决银行转账问题4-3转账事务前转账事务过程中转账事务结束后转账1000元，转账失败的情况演示案例1：利用事务实现转账1000元使用事务解决银行转账问题4-4转账事务前转账事务过程中转账事务结束后转账800元，转账成功的情况演示案例2：利用事务实现转账800元指导——批量插入学生考试成绩2-1训练要点：使用事务向表中插入多条记录需求说明：批量插入参加今天“JavaLogic”课程考试的十名学生成绩如果输入的成绩大于100分，将违反约束讲解需求说明指导——批量插入学生考试成绩2-2实现思路：使用显式事务完成批量插入10个学生考试成绩的操作使用全局变量@@ERROR判断插入操作是否成功使用IF语句判断@@ERROR值。如果插入成功，提交事务；否则回滚事务完成时间：20分钟指导——批量插入学生考试成绩2-3实现代码：BEGINTRANSACTIONDECLARE@errorSumINTSET@errorSum=0/*--插入数据--*/INSERTINTOResult(StudentNo,SubjectNo,ExamDate,StudentResult)VALUES(10012,1,'2009-5-20',102)--分数违反约束SET@errorSum=@errorSum+@@ERROR……IF(@errorSum<>0)--如果有错误BEGINPRINT'插入失败，回滚事务'ROLLBACKTRANSACTIONENDELSEBEGINPRINT'插入成功，提交事务'COMMITTRANSACTIONEND练习——办理毕业学生离校手续2-1需求说明：将毕业学生的基本信息和考试成绩分别保存到历史表中提示：使用显式事务查询Result表中所有Y2学生的考试成绩，保存到表HistoreResult中删除Result表中所有Y2学生的考试成绩查询Student表中所有Y2的学生记录，保存到表HistoryStudent中删除Studet表中所有Y2学生记录完成时间：20分钟练习——办理毕业学生离校手续2-2实现代码：BEGINTRANSACTIONDECLARE@errorSumINTSET@errorSum=0/*--查询Result表中所有Y2学生的考试成绩，保存到新表HistoreResult*/SELECTResult.*INTOHistoreResultFROMResultINNERJOINStudentONResult.StudentNo=Student.StudentNoINNERJOINGradeONGrade.GradeId=Student.GradeIdWHEREGradeName='Y2'SET@errorSum=@errorSum+@@ERROR/*--删除Result表中所有Y2学生的考试成绩*/DELETEResultFROMResultJOINStudentONResult.StudentNo=Student.StudentNoINNERJOINGradeONGrade.GradeId=Student.GradeIdWHEREGradeName='Y2'SET@errorSum=@errorSum+@@ERROR/*--将Student表中所有Y2的学生记录，保存到新表HistoryStudent*/……/*--删除Studet表中所有Y2学生记录*/……/*--根据是否有错误，确定事务是提交还是撤销--*/小结数据库事务具有哪些特性？SQLServer中，事务有几种类型？Transact-SQL使用哪些语句来管理事务？客户回访管理系统触发器的规划与设计任务描述：任务目标：学习触发器的基本用法，完成客户回访管理系统触发器的设计掌握触发器的基本用法掌握触发器的基本结构张三李四王五赵二更新触发器插入触发器触发新生注册

学生表班级表班号班级名人数112信管64212软开50312网编35触发器是在对表进行插入、更新或删除操作时自动执行的存储过程。触发器的优点：跟踪变化

相关表自动进行级联更改

强化约束

触发器触发器与存储过程的区别触发器由事件触发而被执行，例如对某个表执行insert、update、delete等操作时存储过程是通过调用存储过程名直接执行触发器触发器DML触发器DDL触发器SQLServer2008触发器类别：AFTERINSTEADOF服务器数据库触发器的类别触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作inserted和deleted表修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息

inserted和deleted表创建DML触发器

语法CREATETRIGGER

触发器名

ON

作用域[FOR/AFTER,INSTEADOF]触发事件ASBEGIN

触发器主体：SQL语句END作用域：可以是table或者viewFOR和AFTER含义一样，表示触发时机是AFTER触发事件是DELETE，UPDATE，或者INSERT创建DML触发器

鼠标创建和查看触发器创建AfterInsert触发器语法【例6-1】增加一个险种，并打印‘添加一个险种’消息。CREATETRIGGER

触发器名

ON

作用域[FOR/AFTER,INSTEADOF]触发事件ASBEGIN

触发器主体：SQL语句ENDCREATE

TRIGGERtr_c_Insert

ON

险种表AFTER

INSERTAS

print'添加一个险种'GO创建AfterInsert触发器执行该触发器，不需要显式调用，当执行insert操作后，将自动执行。insertinto险种表values('0007','福禄满堂保险','50','1',5000)触发器被执行一行已被插入表中创建AfterDelete触发器

【例6-2】创建AFTERDELETE类型的触发器，当险种表中删除某个险种时，将合同表中关联的记录一起删除创建AfterDelete触发器

【例6-2】创建AFTERDELETE类型的触发器，当险种表中删除某个险种时，将合同表中关联的记录一起删除CREATE

TRIGGERt_xzh_delete

ON

险种表

AFTER

DELETEAS

BEGIN

declare@险种编号char(20)

select@险种编号=险种编号fromdeleted

print

'开始查找并删除合同表中的相关记录...'

delete

from

合同表where

险种编号=@险种编号

print

'删除合同表的相关记录为：'+str(@@rowcount)+'条'ENDGO验证：deletefrom险种表where险种编号='0005'创建AfterDelete触发器

【例6-2】创建AFTERDELETE类型的触发器，当险种表中删除某个险种时，将合同表中关联的记录一起删除创建AfterUpdate触发器

【例6-3】创建一个修改触发器，防止用户修改险种表中的险种号CREATE

TRIGGERt_xzh_update

ON

险种表

AFTER

UPDATEAS

if

update(险种编号)

Begin

raiserror('不能在周末修改数据!',16,10)

rollback

transaction

endGO验证：update险种表set险种编号='0008'where险种编号='0006'创建DDL触发器DDL触发器：针对各种数据定义语言事件而激发，不针对增、删、改等激发。用途：要防止对数据库架构进行某些更改希望数据库中发生某种情况以响应数据库架构中的更改要记录数据库架构中的更改或事件创建DDL触发器语法CREATETRIGGER触发器名

ONALLSERVER或DATABASEAFTER

激活DDL触发器的事件ASBEGIN

触发器主体：SQL语句END创建数据级触发器【例6-4】保护数据库中的数据表不被修改，不被删除。CREATE

TRIGGERtr_safetyON

DATABASE

AFTER

DROP_TABLE,ALTER_TABLEAS

BEGIN

PRINT'对不起，您不能对数据表进行操作