《SQL Server 数据库案例教程》课件第12章触发器

课程内容回顾存储过程是一组预编译的SQL语句，可以包含数据操纵语句、逻辑控制语句或数据检索语句。数据库开发人员或管理员可以通过编写存储过程来运行经常执行的任务、或者应用复杂的业务规则。存储过程可提高应用程序访问数据的速度，帮助实现模块化编程，提高数据库性能和数据安全性存储过程可分为：系统存储过程用户定义的存储过程CREATEPROCEDURE语句用于创建用户定义的存储过程。EXECUTE语句用于运行存储过程。存储过程的参数分为输入参数和输出参数，输入参数用来向存储过程传入值，输出参数从存储过程中返回（输出）值。RAISERROR语句用来向用户报告错误本章目标了解触发器的用途理解触发器的工作原理掌握如何使用inserted表和deleted表掌握如何创建INSERT、UPDATE、DELETE触发器触发器介绍触发器是在数据库中发生事件时自动执行的特殊存储过程，这些事件主要是发生在表上的DML（INSERT,UPDATE,DELETE）操作触发器与数据操作有关在数据库服务器端实现业务规则和强制业务规则存储过程实现业务规则约束强制业务规则强制业务规则约束触发器触发器可以实现比约束更复杂的数据完整性触发器的特点触发器是在对表进行插入、更新或删除操作时自动执行的存储过程触发器通过事件进行触发而被执行的特点:与表相关联自动触发不能直接调用是事务的一部分触发器的种类AFTER触发器AFTER触发器要求只有执行某一操作INSERT、UPDATE、DELETE之后触发器才被触发且只能在表上定义INSTEADOF触发器可在表上定义INSTEADOF触发器，也可以在视图上定义INSTEADOF触发器AFTER触发器AFTER触发器包括:INSERT触发器：当向表中插入数据时触发，自动执行触发器所定义的SQL语句。UPDATE触发器：当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句。DELETE触发器：当删除表中记录时触发，自动执行触发器所定义的SQL语句。触发器的两个特殊表插入表（inserted表）删除表（deleted表):

这两个表是逻辑表，并且是由系统管理的，存储在内存中，不是存储在数据库中，因此，不允许用户直接对其修改。

inserted和deleted表触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作inserted和deleted表修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息创建触发器语法:CREATETRIGGER

trigger_name

ONtable_name

[WITHENCRYPTION]

FOR[DELETE,INSERT,UPDATE]

AS

T-SQL语句GOWITHENCRYPTION表示加密触发器定义的SQL文本DELETE,INSERT,UPDATE指定触发器的类型触发器示例问题:在emp表上创建一个触发器，该触发器保证每次最多只能删除一个雇员

USEempDBGO/*--创建触发器--*/CREATETRIGGERemp_deleteONempFORDELETEASIF(SELECTCOUNT(*)FROMDeleted)>1BEGINRAISERROR(‘不能删除多于一个雇员’，16，1)ROLLBACKTRANSACTIONEND测试:删除多于一条记录,看结果如何?禁用或启用触发器用户可以禁用、启用一个指定的触发器或一个表的所有触发器

语法示例:禁用触发器emp_delete示例:启用触发器emp_deleteALTERTABLEemp{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[，…n]}

ALTERTABLEempDISABLETRIGGERemp_delete

ALTERTABLEempENABLETRIGGERemp_delete

更改和删除触发器修改触发器语法:删除触发器语法:示例:删除触发器emp_deleteALTERTRIGGERTrigger_nameONtable_name[WITHENCRYPTION]FOR{[DELETE,INSERT,UPDATE]}ASSQL语句DROPTRIGGERtrigger_name

DROPTRIGGERemp_deleteINSERT触发器INSERT触发器的工作原理：insertedempnoenamesal002

JAK3000empempnoEnamesal001003JONE2000smiths

1500002jAK3000插入记录行触发insert触发器。向inserted表中插入新行的副本触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作INSERT触发器示例-1问题:当在员工表(emp)中输入数据时，确保输入的员工工资不超过5000元人民币分析:在emp表上创建一个INSERT触发器CheckSal从inserted临时表中获取插入的数据行判断工资的值是否超过5000INSERT触发器示例-1----主要代码-----CREATETRIGGERCheckSalONempFORINSERTASDECLARE@salmoneySELECT@sal=salFROMinsertedIF@sal>5000BEGINPRINT'工资不能超过5000'PRINT'请将工资修改为小于5000的值'ROLLBACKTRANSACTIONEND

…GODELETE触发器DELETE触发器的工作原理：deletedempnoenamesal002

JAK3000empempnoEnamesal001003JONE2000smiths

1500002jAK3000删除记录行触发delete触发器向deleted表中插入被删除的副本触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作DELETE触发器示例-1问题:当删除雇员时，自动备份被删除的数据到表backupTable中分析:应在雇员表上创建DELETE触发器被删除的数据可以从deleted表中获取DELETE触发器示例-1USEempDBGO/*---检测是否存在，触发器存放在系统表sysobjects中--------*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname=’trig_delete_emp’)DROPTRIGGERtrig_delete_empGO/*----创建DELETE触发器：在emp上创建删除触发器-----*/CREATETRIGGERtrig_delete_empONempFORDELETEASprint'开始数据备份，请稍侯......'IFNOTEXISTS(SELECT*FROMsysobjectsWHEREname='backupTable')SELECT*INTObackupTableFROMdeleted--从deleted表中获取被删除的数据

ELSEINSERTINTObackupTableSELECT*FROMdeletedprint'备份数据成功，备份表中的数据为：'SELECT*FROMbackupTableGO/*--测试触发器：删除数据--*/SETNOCOUNTON--不显示T-SQL语句影响的记录行数DELETEFROMemp--查看结果print‘雇员表中的数据：'SELECT*FROMempUPDATE触发器UPDATE触发器的工作原理Deleted（更新前的数据）empnoenamesal002JAK3000empempnoename

sal001

Jone2000

向deleted表中插入被删除的副本检查deleted和inserted表中的数据，确定是否需要回滚或执行其他操作向inserted表中插入被添加的副本Inserted（更新后的数据）empnoenamesal002JAK3500插入记录行002JAK3500UPDATE触发器示例-1问题:检测员工加薪比例，如果加薪额度超过其基本工资的20%，则停止加薪，并给出错误提示。

分析:在员工表上创建UPDATE触发器，删除更改前原有的数据行：删除的数据转移到了deleted表中。再插入更改后的新行：插入的数据同时也保存在inserted表中。UPDATE触发器示例-1--关键代码--CREATETRIGGERtrig_update_empONempFORUPDATEASDECLARE@beforeSalMoney,@afterSalMoney--定义变量

SELECT@beforeSal=salFROMdeleted--获取加薪前的工资

SELECT@afterSal=salFROMinserted--获取加薪后的工资

select@afterSal,@beforeSalIFABS(@afterSal-@beforeSal)>=@beforeSal*0.2--判断加薪额度是否超过%BEGINprint'加薪额度：'+convert(varchar(8),ABS(@afterSal-@beforeSal))RAISERROR('加薪额度不能超过其工资的百分之二十,加薪失败',16,1)ROLLBACKTRANSACTION--回滚事务，撤消加薪

ENDGO列级UPDATE触发器UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据使用UPDATE（列）函数检测是否修改了某列问题：在雇员表(emp)中,雇员号作为主键,一般不允许修改。

分析：UPDATE（列名）函数可以检测是否修改了某列

列级UPDATE触发器示例USEempDBGO/*--创建UPDATE触发器：在雇员表emp上创建更新（列）触发器--*/CREATETRIGGERemp_updateONempFORUPDATEASIFUPDATE(empno)BEGINprint'修改失败....'RAISERROR('****不允许修改雇员号！',16,1)ROLLBACKTRANSACTION--回滚事务，撤消操作ENDGO/*--测试触发器：修改雇员编号*/--SETNOCOUNTONUPDATEEMPSETEMPNO=‘111’WHEREEMPNO='101'基于视图的INSTEADOF触发器可以在表或视图上定义INSTEADOF触发器，执行这种触发器就能够替代原始的触发动作INSTEADOF触发器被用于更新那些没有办法通过正常方式更新的视图INSTEADOF触发器包含代替原始数据操作语句的代码。USEempDBGO/*--创建基于两个表emp和dept联接的视图--*/CREATEVIEWemp_deptASSELECTempno,ename,sal,dnameFROMempe,deptdWHEREe.deptno=d.deptno/*--查看视图数据--*/SELECT*FROMemp_dept/*--测试：试图从视图中删除数据--*/DELETEFROMemp_deptWHEREempno='101'基于视图的INSTEADOF触发器要使得能够对emp_dept视图进行更新，必须通过INSTEADOF触发器来删除数据

示例