触发器的创建和使用

1、触发器的创建和使用一、 触发器的优点l 触发器是自动执行的。当对表中的数据做任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。l 触发器可以通过数据库中的相关表进行层叠更改。例如，可以子啊t_student 表的s_number列上写入一个删除触发器，以使t_score表中的各匹配行采取删除操作。该触发器用s_number列作为唯一键，在t_score表中对各匹配行进行定位。l 触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他表中的列。二、 触发器的类型l AFTER触发器：这种烈性的触发器将在数据变动（INSERT、UPD

2、ATE和DELETE操作）完成以后才被出发。可以对变动的数据进行检查，如果发现错误，将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。l INSTEAD OF 触发器：INSTEAD OF触发器是SQL Server 2000中新增的功能。这种类型的触发器将在数据变动以前被触发，并取代变动数据的操作（INSERT、UPDATE和DELETE操作），而去执行触发器定义的操作。INSTEAD OF触发器可以在表或视图上定义。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEAD OF触发器。三、 注意事项

3、l CREATE TRIGGER 语句必须是批处理中的第一个语句。将该批处理中随后的其他所有语句解释为CREATE TRIGGER语句定义的一部分。l 创建触发器的权期限默分配给表的所有者，且不能讲该权限转给其他用户。l 触发器为数据库对象，其名称必须遵循标识符的命名规则。l 只能在当前数据库中创建触发器但触发器可以应用当前数据库以外的对象。l TRUNCATE TABLE语句不会引发DELETE触发器。l WRITETEXT语句不会引发INSERT或UPDATE触发器。四、 使用Transact-SQL语句创建触发器（1） 语法CREATE TRIGGER trigger_nameON ta

4、ble|view FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE(column) AND|ORUPDATE(column) . . .n| IF (COLUMNS_UPDATED()bitwise_operatorupdated_bitmask) comparison_operatorcolumn_bitmask . . .nsql_statement . . .n（2） 代码意义l trigger_name：用于指定触发器的名称。触发器的名称必须符合S

5、QL Server标识符规则，并且其名称在当前数据库中必须是唯一的。另外，还可以选择是否指定触发器所有者的名称。l table|view：用于指定在其上执行触发器的表或视图，有时称为触发器表或触发器试图。可以选择是否指定表或视图的所有者名称。l WITH ENCRYPTION：用于加密syscomments表中包含CREATE TRIGGER语句文本的条目。使用WITH ENCRYPTION可防止触发器作为SQL Server复制的一部分发布。l AFTER：用于规定此触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的应用级联操作和约束检查也必须成功完成后，才能执行触发器。

6、如果仅指定FOR关键字，则AFTER是默认设置。注意该类型触发器仅能在表上创建，而不能在视图上定义该触发器。l INSTEAD OF：用于规定执行的是触发器而不是执行触发器SQL语句，从而用触发器替代触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。INSTEAD OF触发器不能在WITH CHECK OPTION的可更新视图上定义。如果只向指定的WITH CHECK OPTION选项的可更新视图添加INSTEAD OF触发器，SQL Server将产生一个错误。用户必须用ALTER VIEW删除该选项后才能定义INSTE

7、AD OF触发器。l DELETE , INSERT , UPDATE ：用于指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许以任意顺序组合这些关键字。如果指定的选项多余一个，需用逗号分隔这些选项。l WITH APPEND：用于指定应该添加现有类型的其他触发器。只有当兼容级别（指某一数据库行为与以前版本的SQL Server兼容程度）是65或更低时，不应执行该触发器。l NOT FOR REPLICATION：表示当复制进程更改触发器所涉及的表时，不应执行该触发器。l AS：触发器要执行的操作。l sql_statement：触发器的条件和

8、操作。触发器条件指定其他准则，以确定DELETE、INSERT或UPDATE语句是否导执行定触发器操作。l IF UPATE(column)：用于测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因为在ON字句中指定了表名，所以在IF UPDATE子句的列名前不要包含表名。若要测试在多个列上进行的INSERT或UPDATE操作，请在第一个操作后指定单独的UPDATE（column）子句。在INSERT操作中，IF UPDATE将返回TRUE值，因为这些列插入了显示值或隐形值（NULL）。l IF(COLUMNS_UPDATED()：用于测试是否插入或

9、更新了所涉及的列，仅用于INSERT或UPDATE触发器。l bitwise_operator：用于比较运算的位逻辑运算符。l updated_bitmask：整型位掩码，表示实际更新或插入的列。例如，表t1包含C1、C2、C3、C4和C5。假定表t1上有UPDATE触发器，若要检查列C2、C3和C4是否都已更新，则指定值14；若要检查是否只有列C2已更新，则指定值2。l comparison_operato：比较运算符。使用等号（=）检查updated_bitmask中指定的所有列是否都实际进行更新。使用大于号（>）检查updated_bitmask中指定的任一列或某些列是否已更新。l

10、 column_bitmask：检查列的整型位掩码，用来检查是否已更新或插入了这些列。（注：当创建触发器时，如果使用了相同名称的触发器，后建立的触发器将会覆盖前面简历的触发器。用户不能在系统表上创建用户自定义的触发器。）（3） 临时表inserted 表中存储着被 ISNERT 和 UPDATE 语句影响的新的数据行。执行DELETE 或 UPDATE 语句时，新的数据行被添加到基本表中，同事这些数据行的备份复制到inserted临时表中。deleted 表中存储着被DELETE 和UPDATE 语句影响的旧数据行。执行 INSERT 或 UPDATE 操作时，指定的数据行从基本表中删除，然后

11、被转移到 deleted表中，然后将新的数据行同事插入基本表和inserted表中。 五、 示例（1） AFTER UPDATE、INSERT触发器USE studentIF EXISTS（SELECT 那么FROM sysobjects WHERE name=scoreCheckAND type=TR）DROP TRIGGER scoreCheckGOCREATE TRIGGER scoreCheckON t_scoreFOR INSERT,UPDATEASIF UPDATE(score)PRINT AFTER触发器开始执行BEGIN DECLARE ScoreValue real SELE

12、CT ScoreValue=(SELECT score FROM inserted)IF ScoreValue>100 OR ScoreValue<0PRINT 输入的分数有误，请确认输入的考试分数！ENDGO触发scoreCheckUSE studentGOPRINT 在t_score中插入记录时触发器执行结果：PRINT INSERT INTO t_score VALUES(9952123,20010203,-35)INSERT INTO t_score VALUES(9952124,20010203,152)GOPRINT在t_score中修改记录时触发器执行结果：PRINT

13、 UPDATE t_score SET score=115 WHERE s_number=9952123UPDATE score=-65 WHERE s_number=9952124（2） AFTER DELETE触发器USE studentGOIF EXISTS(SELECT name FROM sysobjects WHERE name=Stu_DeteleAND type=TR)DROP TRIGGER Stu_DeleteGOCREATE TRIGGER Stu_DeleteON t_studentFOR DELETEAS PRINT 删除触发器开始执行 DECLARE StuNum

14、char(10) PRINT 把在t_student表中删除的记录的s_number赋值给局部变量SstuNum。 SELECT StuNum=s_number FROM deleted PRINT 开始查找并删除t_score表中的相关记录 DELETE FROM t_score WHERE s_number=StuNum PRINT 删除了t_score表中的学好为 +RTRIM(StuNum)+ 的记录。触发Stu_DeleteUSE studentGODELETE FROM t_student WHERE s_number=9952123DELETE FROM t_student WH

15、ERE s_number=9952124GO（3） INSTEAD OF触发器USE studentGOIF EXISTS(SELECT name FROM sysobjects WHERE name=NotAllowDeleteAND type=TR)DROP TRIGGER NotAllowDeleteON t_sourseINSTEAD OF DELETEAS PRINT INSTEAD OF 触发器开始执行 PRINT 本表中的数据不允许被删除！不能执行删除操作GO触发NotAllowDeleteUSE studentGODELETE FROM t_course WHERE c_num

16、ber=20010203GO六、 查看、修改和删除触发器（1） 查看触发器l sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help 触发器名称l sp_helptext：用于查看触发器的正文信息。sp_helptext 触发器名称l sp_depends：用于查看指定触发器所引用的表或者指定的表设计到的所有触发器。sp_depends 触发器名称sp_depends 表名（2） 修改触发器1. 使用ALTER TRIGGER命令语法修改：ALTER TRIGGER trigger_nameON table|view FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE(co