《数据库》课件第9章-触发器

1回顾事务的概念事务的操作2思考Student(StuID,StuName,StuAge,StuSex,StuCity,StuScore,DepID)Department(DepID,DepName,Total)为学生表添加一位新生，数据库需要做哪些变化？StuIDStuNameStuAgeStuSexStuCityStuScoreDepIDA00001Mary21女BeiJing921A00002Tom20男NanJing902DepIDDepNameTotal1computer12English1A00003Mike18男ShangHai8812第九章触发器3目标4创建各种类型的触发器触发器的概念触发器的种类Inserted与deleted表59.1触发器

触发器是一种特殊的存储过程，当数据发生变化时，触发器会产生某种动作。使用触发器有助于强制保持数据库的数据完整性。69.2触发器分类INSTEADOF触发器：在指定的操作（INSERT、UPDATE或DELETE语句）之前执行触发器。AFTER触发器。在执行了INSERT、UPDATE或DELETE语句操作之后执行触发器。79.2触发器分类AFTER触发器。在执行了INSERT、UPDATE或DELETE语句操作之后执行触发器。触发因子触发器触发结果StuIDStuNameStuAgeStuSexStuCityStuScoreDepIDA00001Mary21女BeiJing921A00002Tom20男NanJing902DepIDDepNameTotal1computer12English1A00003Mike18男ShangHai881289.2触发器分类触发因子触发器触发结果StuIDStuNameStuAgeStuSexStuCityStuScoreDepIDA00001Mary21女BeiJing921A00002Tom20男NanJing902A00003Mike18男ShangHai881DepIDDepNameTotal1computer22English199.2触发器分类INSTEADOF触发器：在指定的操作（INSERT、UPDATE或DELETE语句）之前执行触发器。触发因子触发器触发结果DeletefromDepartmentWhereDepID=2DeletefromStudentWhereDepID=2DeletefromDepartmentWhereDepID=2替代109.2inserted与deleted表

当执行INSERT,DELETE,或UPDATE语句且触发器触发时,会产生两种特殊表。inserted和deleted表.注：只有使用了触发器，才会产生inserted与deleted表。11inserted和deleted表存放的内容-插入通常在插入数据时，可以从inserted表中读取新插入的值，此时deleted表不会发生变化。当用户执行:INSERTINTODepartmentVALUES(1,'Computer',0)时，触发器触发产生inserted与deleted表：DepIDDepNameTotal1Computer0Inserted：DepIDDepNameTotaldeleted：12inserted和deleted表存放的内容-更新在更新数据时，inserted表和deleted表都发生变化。可以从deleted表中读取原有的值，从inserted表中读取修改后的值。当用户执行:UPDATEDepartmentSETTotal=1WHEREDepID=1时，触发器触发产生inserted与deleted表：

DepIDDepNameTotal1Computer1Inserted：deleted：DepIDDepNameTotal1Computer013inserted和deleted表存放的内容-删除在删除数据时，可以从deleted表中读取已经删除的值，而inserted表不会发生变化。当用户执行:DELETEFROMDepartmentWHEREDepID=1时，触发器触发产生inserted与deleted表：DepIDDepNameTotalInserted：deleted：DepIDDepNameTotal1Computer19.3创建触发器14语法：CREATETRIGGER触发器名ON{表名|视图名}{FOR|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASSQL语句[...n]示例INSERT触发器15有“Student”和“Department”表，假设学生表中插入一条学生记录，则相应系人数加1。createtriggertrgInsertStudentonstudentforinsertasdeclare@DepIDintselect@DepID=DepIDfromupdatedepartmentsetTotal=Total+1whereDepID=@DepID

执行：insertintostudentvalues('A00016','mary',21,'女','Nanjing',90,1)inserted示例16有“Student”和“Department”表，假设学生表中插入一条学生记录，则相应系人数加1，如果总人数超过50人，则拒绝插入该学生信息。createtriggertrgInsertStudentonstudentforinsertasdeclare@DepIDintdeclare@Totalintselect@DepID=DepIDfrominsertedupdatedepartmentsetTotal=Total+1whereDepID=@DepIDselect@Total=TotalfromdepartmentwhereDepID=@DepIDif(@Total>50)begin rollbacktranend执行：insertintostudentvalues('A00020','mary',21,'女','Nanjing',90,1)示例UPDATE触发器17有“Student”和“Department”表，创建触发器，如果有学生转系，则相应系人数也一并修改。createtriggertrgUpdateStudentTransferonStudentforupdateasdeclare@OriginalDepIDint,@NewDepIDintselect@OriginalDepID=DepIDfromdeletedselect@NewDepID=DepIDfrominsertedupdatedepartmentsetTotal=Total+1whereDepID=@NewDepIDupdatedepartmentsetTotal=Total-1whereDepID=@OriginalDepID执行：

updatestudentsetDepID=2whereStuID='A00001'示例DELETE触发器18在“SC”表中创建一个DELETE触发器，如果删除记录的成绩为不及格，则不执行删除操作，并提示用户；如果成绩及格，则允许删除。createtriggertrgDeleteSConSCfordeleteasdeclare@Scoreintselect@Score=Scorefromif(@Score<60)Begin print‘不能删除该系’ rollbacktransactionenddeleted示例19示例：在“Department”表中创建一个INSERT触发器，如果插入记录的院系在“系”表中已存在，则不执行插入操作，并提示用户。解决方案：createtriggertrgInserDepartmentonDepartmentforinsertasdeclare@DepNamevarchar(20)select@DepName=DepNamefrominsertedifexists(select*fromdepartmentwhereDepName=@DepName)begin print‘该系已存在’ rollbacktransactionend示例演示20为了验证触发器是否正常工作，在查询分析器中执行如下语句：

insertintodepartmentvalues(5,'music',20)因为使用的是after触发器，在执行此语句时，触发器触发，该记录已加入表中，在做if语句判断之前，该记录已存在，所以无论插入什么系，都会执行回滚。编译器提示：消息3609，级别16，状态1，第1行事务在触发器中结束。批处理已中止。修改后的触发器21createtriggertrgInserDepartmentonDepartmentinsteadofinsertasdeclare@DepNamevarchar(20)declare@DepIDintdeclare@Totalintselect@DepID=DepID,@DepName=DepName,@Total=Totalfrominsertedifexists(select*fromdepartmentwhereDepName=@DepName) print'该系已存在'elseinsertintodepartmentvalues(@DepID,@DepName,@Total)示例演示22在查询分析器中执行如下语句：

insertintodepartmentvalues(5,'music',20)

insertintodepartmentvalues(6,'music',20)INSTEADOF触发器23有学生表和系表，如删除某个系，则相应的学生全部删除。createtriggertrgDeleteDepartmentonDepartment

insteadofdeleteAs declare@DepIDint select@DepID=DepIDfromdeleted deletefromSCwhereStuIDin(selectStuIDfromstudentwhereDepID=@DepID) deletefromStudentwhereDepID=@DepID deletefromDepartmentwhereDepID=@DepID示例对特定列进行更新24在学生表上创建更新触发器，当学生更新其他字段时，提示用户更新成功；如果更新了姓名，则让更新操作回滚。可以针对某些列创建触发器，使用语句：IFUPDATE(列名)解决方案：createtriggertrgUpdateStudentonstudentforupdateasifupdate(StuName)begin rollbacktransactionEndElseprint‘更新成功！’更新及删除触发器自学25作业：2626在“Department”表中创建一个UPDATE触发器，如果修改院系名称的长度超过10个字符，则不执行修改操作，并提示用户。createtriggertrgUpdateDepartmentonDepartmentfo