1、第四章 触发器【部分】触发器概触发器概 触发器的概念触发器的概念 是一种特殊的存储过程，在指定标的数据发生变化时自动生效。具体地讲，就是当对某一表进行数据插入（insert）、删除（delete）以及更改（update）操作时，自动执行做某些处理的SQL 语句。触发器的特点v特殊的存储过程v特殊点是不需要由用户维护。当对表作UPDATE、INSERT、DELETE时自动触发执行。v保证业务规则和完整性。触发器应用场合v完成比CHECK约束更复杂的数据约束。v为保证数据库性能而维护的非规范化数据。v实现复杂的商业规则。v评估数据修改前后的表状态，并根据其差异采取对策。v一个表中的多个同类触发器（
2、INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。触发器创建时包含的4个核心要素v指定触发器的名称。v指出触发器所作用的表。v指出引发触发器的操作：INSERT、UPDATE 或 DELETE多个或者其中的一个。v编写SQL语句，实现触发器中腰完成的工作。8.1 创建触发器CREATE TRIGGER 触发器名称ON 表名|视图名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT ,DELETE ,UPDATE AS IF UPDATE(column)AND|OR UPDATE(column).SQL 语句 创建触发
3、器注意事项vWITH ENCRYPTION：加密，不看看到原来的SQL脚本vFOR 或AFTER类型【后触发】：所有SQL语句都执行完且执行成功的情况下才能执行触发器。可以在同一种操作上建立多个触发器；vINSTEAD OF类型【替代触发】：仅执行触发器本身，而不执行引起触发操作的SQL语句。在同一种操作上只能建立一个触发器。v在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由所有三个操作来引发。v大部分Transact-SQL语句都可用在触发器中，但所有的建立和更改数据库以及数据库对象的语句、所有的DROP语句都不允许在触发器中使用。v在触发器定义中，可以使用IF UPDATE
4、子句来测试在INSERT和UPDATE语句中是否对指定字段有影响。v通常不在触发器中返回任何结果。v在触发器中可使用特殊的二个表：INSERTED、DELETED inserted 表和表和 deleted 表表 在触发器执行时，会产生2个临时表：表 表 表:用于存储 insert 和 update 语句所影响的副本 表:用于存储 delete 和 update 语句所影响的副本 vINSERTED和DELETED是与操作表结构一样的临时表，其驻留内存，可用于测试修改结果等，不能直接修改，只能在触发器语句中使用。vINSERTED保存新插入的数据和UPDATE后的数据副本。vDELETED保存
5、删除的数据和UPDATE更改前数据副本。v在用户执行数据的更改操作时，SQL Server自动创建和管理v驻留在内存中。v结构同触发器所作用的基本表的结构。v只可以被触发器使用。v由系统维护，在触发器中像普通表一样查询，不能直接对这两个临时表中的数据进行更改。删除、插入、更改后数据副本的保存v在执行DELETE操作时，被删除的数据被保存到DELETED表中。它和执行操作的基本表没有相同的行。v在执行INSERT操作时，新插入的数据同时被保存到INSERTED表中。基本表中新数据行的副本。v在执行UPDATE操作时，先从触发器表中删除旧行，然后再插入新行。因此，将更改前的数据保存到DELETED
6、表中。将更改后的数据行保存到INSERTED表。8.2 后触发型触发器 v使用FOR或AFTER选项定义的触发器为后触发的触发器，即只有在引发触发器执行的语句中指定的操作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。v注意：不能在视图上定义AFTER触发器。vCREATE TABLE Student v (v Sno CHAR(9)PRIMARY KEY,v Sname CHAR(20),v Ssex CHAR(2),v Sage int ,v Sdept CHAR(20)v )建立一个“学生选课”表SCvCREATE TABLE SCv (v Sno CHAR(9),v Cno
7、 CHAR(4),v Grade int v )例例1：创建如下触发器，在插入、修改和删除：创建如下触发器，在插入、修改和删除记录时，自动显示表中内容记录时，自动显示表中内容create trigger studenttrig1 on student for insert,update,delete as select *from student 执行如下语句，激发触发器 insert into student values(0000a,张三,男,19,cs)update student set sname=张五 where sno=0000av例例2：下例说明：下例说明 inserted 表
8、和表和 deleted 表的作用表的作用v v create trigger student _trig2 on studentv for insert,update,deletev asv print inserted表v select*from insertedv print deleted表v select*from deleted v 执行触发语句：执行触发语句：v 执行触发语句：v insert student values(00ab,pppp,男,20,cs)v update student set sage=21 where sno=00abv delete student wh
9、ere sno=00abv1.insert 和 update触发器v 这两种触发器用来检查插入或者修改后的数据是否满足要求v 例例3，创建触发器，检查插入记录的学生年龄是否在，创建触发器，检查插入记录的学生年龄是否在18-20之间之间v create trigger student_trig3 on studentv for insert,updatev asv declare s_age intv select s_age=sage from insertedv if s_age 20v beginv rollbackv print(年龄值必须在18-20)v endv执行触发语句：vins
10、ert student values(0044,张户,女,23,cs)vselect*from studentvinsert student values(0033,赵强,女,20,cs)vupdate student set sage=30 where sno=0033v2.delete 触发器v delete触发器通常用于下面的情况：v 在删除数据记录时，保持数据的一致性v 级联删除操作v 例例4，关于学生表和成绩表的数据删除，关于学生表和成绩表的数据删除vdelete studentvdelete scvinsert student values(01,aaaa,男,19,sc)vins
11、ert sc values(01,1,90)vinsert sc values(01,2,96)vselect*from studentvselect*from scv创建触发器创建触发器vcreate trigger student_tr1 on studentvfor deletevas v delete sc where sc.sno=(select sno from deleted)v 执行触发语句：执行触发语句：vdelete student where sno=01v3.触发器的其它应用v 例例5，在，在student 表上创建一个禁止删除和插入操作的触发器表上创建一个禁止删除和插
12、入操作的触发器v create trigger student_tr1 on student for insert,updatev asv begin v print 不允许你插入或删除任何数据！v rollback transactionv returnv endv执行触发语句：执行触发语句：insert student values(003,aa,女,20,cs)v 例例6，在，在student表上创建一个禁止修改主键操作的触发器表上创建一个禁止修改主键操作的触发器v create trigger student_tr2 on studentv for updatev asv if upd
13、ate(sno)v beginv print 不能修改学号v rollbackv endv 执行触发语句：执行触发语句：update student set sno=001 where sname=张力v4.嵌套触发器的应用v 嵌套触发器指一个触发器触发后，执行了某个动作，该动作又触发了第二个触发器，以此类推，最多可32级。v 例例6，创建一个级联删除操作的触发器，创建一个级联删除操作的触发器vcreate trigger student_tr1 on studentvfor deletevas v delete sc where sc.sno=(select sno from deleted
14、)create trigger sc_tr1 on scv for deletev asvprint 该生的成绩信息均已删除！v执行触发语句：执行触发语句：delete student where sno=01v5.instead of 触发器的应用v instead off 指定执行触发器而不是执行触发语句。v 【先删除所做触发器【先删除所做触发器】例例7，创建一个，创建一个instead off触发器，执行触发器，不执行触发语句触发器，执行触发器，不执行触发语句 create trigger t1 on scvinstead of insertvas vinsert sc values(0
15、01,01,90)v执行以下语句：insert into sc values(xxx,99,79)v 在表中插入的数据记录是：(001,01,90)v 而不是：(xxx,99,79)v例8创建限制删除表t1中c2列中值为“a”的记录的触发器。【避免删除超级用户，很有意义【避免删除超级用户，很有意义】CREATE TRIGGER tri3 ON t1 INSTEAD OF deleteas if not exists(select*from deleted where c2=a)delete from t1 where c2 in(select c2 from deleted)v触发器的查看、修改与删除触发器的查看、修改与删除v v 1.触发器的查看v .使用系统存储过程：sp_helptext 触发器名v .使用企业管理器v 2.触发器的修改v .使用 alter trigger 语句v .使用企业管理器v 3.触发器的删除v .使用 drop trigger 语句v .使用企业管理器
