触发器的创建和使用.docx

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

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

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

4、语法CREATE TRIGGER trigger_nameON table|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 ） 代码意义 tr

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

6、成功执行后才激发。所有的应用级联操作和约束检查也必须成功完成后，才能执行触发器。如果仅指定 FOR 关键字，则 AFTER 是默认设置。注意该类型触发器仅能在表上创建，而不能在视图上定义该触发器。 INSTEAD OF：用于规定执行的是触发器而不是执行触发器 SQL 语句，从而用触发器替代触发语句的操作。在表或视图上，每个 INSERT、UPDATE或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果只向指定的WITH CHECK OPTION 选项的可更新视图添加 INSTEAD

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

8、进程更改触发器所涉及的表时，不应执行该触发器。 AS：触发器要执行的操作。 sql_statement：触发器的条件和操作。触发器条件指定其他准则，以确定DELETE、INSERT 或 UPDATE 语句是否导执行定触发器操作。 IF UPATE(column)：用于测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作，可以指定多列。因为在 ON 字句中指定了表名，所以在 IF UPDATE 子句的列名前不要包含表名。若要测试在多个列上进行的INSERT 或 UPDATE 操作，请在第一个操作后指定单独的UPDATE（column）子句。在 INSERT

9、操作中，IF UPDATE 将返回 TRUE 值，因为这些列插入了显示值或隐形值（NULL） 。 IF(COLUMNS_UPDATED()：用于测试是否插入或更新了所涉及的列，仅用于 INSERT 或 UPDATE 触发器。 bitwise_operator：用于比较运算的位逻辑运算符。 updated_bitmask：整型位掩码，表示实际更新或插入的列。例如，表 t1 包含 C1、C2、C3 、C4 和 C5。假定表 t1 上有 UPDATE 触发器，若要检查列C2、C3 和 C4 是否都已更新，则指定值 14；若要检查是否只有列 C2 已更新，则指定值 2。 comparison_oper

10、ato：比较运算符。使用等号（=）检查 updated_bitmask中指定的所有列是否都实际进行更新。使用大于号（）检查updated_bitmask 中指定的任一列或某些列是否已更新。 column_bitmask：检查列的整型位掩码，用来检查是否已更新或插入了这些列。（注：当创建触发器时，如果使用了相同名称的触 发器，后建立的触发器将会覆盖前面简历的触发器。用户不能在系 统表上创建用户自定义 的触发器。 ）（3） 临时表inserted 表中存储着被 ISNERT 和 UPDATE 语句影响的新的数据行。执行DELETE 或 UPDATE 语句时，新的数据行被添加到基本表中，同事这些数据

11、行的备份复制到 inserted 临时表中。deleted 表中存储着被 DELETE 和 UPDATE 语句影响的旧数据行。执行 INSERT 或 UPDATE 操作时，指定的数据行从基本表中删除，然后被转移到 deleted 表中，然后将新的数据行同事插入基本表和 inserted 表中。 五、 示例（1） AFTER UPDATE、INSERT 触发器USE studentIF EXISTS（SELECT 那么 FROM sysobjects WHERE name=scoreCheckAND type=TR）DROP TRIGGER scoreCheckGOCREATE TRIGGER

12、scoreCheckON t_scoreFOR INSERT,UPDATEASIF UPDATE(score)PRINT AFTER 触发器开始执行BEGINDECLARE ScoreValue realSELECT ScoreValue=(SELECT score FROM inserted)IF ScoreValue100 OR ScoreValue0PRINT 输入的分数有误，请确认输入的考试分数！ENDGO触发 scoreCheckUSE studentGOPRINT 在 t_score 中插入记录时触发器执行结果：PRINT INSERT INTO t_score VALUES(99

13、52123,20010203,-35)INSERT INTO t_score VALUES(9952124,20010203,152)GOPRINT在 t_score 中修改记录时触发器执行结果：PRINT 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)DR

14、OP TRIGGER Stu_DeleteGOCREATE TRIGGER Stu_DeleteON t_studentFOR DELETEASPRINT 删除触发器开始执行DECLARE StuNum char(10)PRINT 把在 t_student 表中删除的记录的 s_number 赋值给局部变量SstuNum。 SELECT StuNum=s_number FROM deletedPRINT 开始查找并删除 t_score 表中的相关记录DELETE FROM t_score WHERE s_number=StuNumPRINT 删除了 t_score 表中的学好为 +RTRIM(

15、StuNum)+ 的记录。 触发 Stu_DeleteUSE studentGODELETE FROM t_student WHERE s_number=9952123DELETE FROM t_student WHERE s_number=9952124GO（3） INSTEAD OF 触发器USE studentGOIF EXISTS(SELECT name FROM sysobjects WHERE name=NotAllowDeleteAND type=TR)DROP TRIGGER NotAllowDeleteON t_sourseINSTEAD OF DELETEASPRINT I

16、NSTEAD OF 触发器开始执行PRINT 本表中的数据不允许被删除！不能执行删除操作GO触发 NotAllowDeleteUSE studentGODELETE FROM t_course WHERE c_number=20010203GO六、 查看、修改和删除触发器（1） 查看触发器 sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help 触发器名称 sp_helptext：用于查看触发器的正文信息。sp_helptext 触发器名称 sp_depends：用于查看指定触发器所引用的表或者指定的表设计到的所有触发器。sp_depends 触发器名称

17、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(column) AND|ORUPDATE(column) . . .n| IF (COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask . . .nsql_statement . . .n2. 使用系统存储过程修改触发器名称sp_rename oldname,newname（3） 删除触发器1. 使用企业管理器删除触发器。2. 使用系统命令删除触发器。DROP TRIGGER trigger ,.n