SQL触发器语法参考

1、SQL触发器语法参考 语法CREATE TRIGGER trigger_name ON  table | view   WITH ENCRYPTION        FOR | AFTER | INSTEAD OF    INSERT   ,   UPDATE

2、           WITH APPEND          NOT FOR REPLICATION         AS          IF UPDAT

3、E ( column )              AND | OR  UPDATE ( column )                  n   

4、      | IF ( COLUMNS_UPDATED ( )  bitwise_operator  updated_bitmask )                 comparison_operator  column_bitmask

5、0; n                   sql_statement  n        参数trigger_name是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。Table | view是在其上执行触发器的表或视图，有时称为触发器表或触

6、发器视图。可以选择是否指定表或视图的所有者名称。WITH ENCRYPTION加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server 复制的一部分发布。AFTER指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，则 AFTER 是默认设置。不能在视图上定义 AFTER 触发器。INSTEAD OF指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。在表或视图

7、上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。 DELETE , INSERT , UPDATE 是指定在表或视图上执行哪些数据修改语句时将激活触发器的

8、关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。WITH APPEND指定应该添加现有类型的其它触发器。只有当兼容级别是 65 或更低时，才需要使用该可选子句。如果兼容级别是 70 或更高，则不必使用 WITH APPEND 子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CREATE TRIG

9、GER 的默认行为）。有关更多信息，请参见 sp_dbcmptlevel。WITH APPEND 不能与 INSTEAD OF 触发器一起使用，或者，如果显式声明 AFTER 触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有 INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持 WITH APPEND 和 FOR（将被解释为 AFTER）。NOT FOR REPLICATION表示当复制进程更改触发器所涉及的表时，不应执行该触发器。AS是触发器要执行的操作。sql_statement是触发器的条件和操作。触发器条件指定其它准则，以确定

10、 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。当尝试 DELETE、INSERT 或 UPDATE 操作时，Transact-SQL语句中指定的触发器操作将生效。触发器可以包含任意数量和种类的 Transact-SQL 语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的 Transact-SQL 语句常常包含控制流语言。CREATE TRIGGER 语句中使用几个特殊的表： · deleted 和 inserted 是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作

11、可能更改的行的旧值或新值。例如，若要检索 deleted 表中的所有值，请使用： · SELECT *· FROM deleted · 如果兼容级别等于 70，那么在 DELETE、INSERT 或 UPDATE 触发器中，SQL Server 将不允许引用 inserted 和 deleted 表中的 text、ntext 或 image 列。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值，请将 inserted 表与原始更新表联接。当兼容级别是 65 或

12、更低时，对 inserted 或 deleted 表中允许空值的text、ntext 或 image 列，将返回空值；如果这些列不可为空，则返回零长度字符串。 当兼容级别是 80 或更高时，SQL Server 允许在表或视图上通过 INSTEAD OF 触发器更新 text、ntext 或 image 列。n是表示触发器中可以包含多条 Transact-SQL 语句的占位符。对于 IF UPDATE (column) 语句，可以通过重复 UPDATE (column) 子句包含多列。IF UPDATE (column)测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 D

13、ELETE 操作。可以指定多列。因为在 ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。若要测试在多个列上进行的 INSERT 或 UPDATE 操作，请在第一个操作后指定单独的 UPDATE(column) 子句。在 INSERT 操作中 IF UPDATE 将返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。说明  IF UPDATE (column) 子句的功能等同于 IF、IF.ELSE 或 WHILE 语句，并且可以使用 BEGIN.END 语句块。有关更多信息，请参见控制流语言。  可以在触发器主体中的

14、任意位置使用 UPDATE (column)。column是要测试 INSERT 或 UPDATE 操作的列名。该列可以是 SQL Server 支持的任何数据类型。但是，计算列不能用于该环境中。有关更多信息，请参见数据类型。 IF (COLUMNS_UPDATED() 测试是否插入或更新了提及的列，仅用于 INSERT 或 UPDATE 触发器中。COLUMNS_UPDATED 返回 varbinary 位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED 函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在

15、表上创建的触发器包含 8 列以上，则 COLUMNS_UPDATED 返回多个字节，最左边的为最不重要的字节。在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。可以在触发器主体中的任意位置使用 COLUMNS_UPDATED。bitwise_operator是用于比较运算的位运算符。updated_bitmask是整型位掩码，表示实际更新或插入的列。例如，表 t1 包含列 C1、C2、C3、C4 和 C5。假定表 t1 上有 UPDATE 触发器，若要检查列 C2、C3 和 C4 是否都有更新，指定值 14；

16、若要检查是否只有列 C2 有更新，指定值 2。comparison_operator是比较运算符。使用等号 (=) 检查 updated_bitmask 中指定的所有列是否都实际进行了更新。使用大于号 (>) 检查 updated_bitmask 中指定的任一列或某些列是否已更新。column_bitmask是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。 注释触发器常常用于强制业务规则和数据完整性。SQL Server 通过表创建语句（ALTER TABLE 和 CREATE TABLE）提供声明引用完整性 (DRI)；但是 DRI 不提供数据库间的引用完整性。若要强制引用

17、完整性（有关表的主键和外键之间关系的规则），请使用主键和外键约束（ALTER TABLE 和 CREATE TABLE 的 PRIMARY KEY 和 FOREIGN KEY 关键字）。如果触发器表存在约束，则在 INSTEAD OF 触发器执行之后和 AFTER 触发器执行之前检查这些约束。如果违反了约束，则回滚 INSTEAD OF 触发器操作且不执行（激发）AFTER 触发器。可用 sp_settriggerorder 指定表上第一个和最后一个执行的 AFTER 触发器。在表上只能为每个 INSERT、UPDATE 和 DELETE 操作指定一个第一个执行和一个最后一个执行的 AFTER

18、 触发器。如果同一表上还有其它 AFTER 触发器，则这些触发器将以随机顺序执行。如果 ALTER TRIGGER 语句更改了第一个或最后一个触发器，则将除去已修改触发器上设置的第一个或最后一个特性，而且必须用 sp_settriggerorder 重置排序值。只有当触发 SQL 语句（包括所有与更新或删除的对象关联的引用级联操作和约束检查）成功执行后，AFTER 触发器才会执行。AFTER 触发器检查触发语句的运行效果，以及所有由触发语句引起的 UPDATE 和 DELETE 引用级联操作的效果。触发器限制CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。 触

19、发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。 如果指定触发器所有者名称以限定触发器，请以相同的方式限定表名。 在同一条 CREATE TRIGGER 语句中，可以为多种用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作。 如果一个表的外键在 DELETE/UPDATE 操作上定义了级联，则不能在该表上定义 INSTEAD OF DELETE/UPDATE 触发器。在触发器内可以指定任意的 SET 语句。所选择的 SET 选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。与使用存储过程一样，当触发器激发时，将向调用应用程序返回结果。若要避免由

20、于触发器激发而向应用程序返回结果，请不要包含返回结果的 SELECT 语句，也不要包含在触发器中进行变量赋值的语句。包含向用户返回结果的 SELECT 语句或进行变量赋值的语句的触发器需要特殊处理；这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。 DELETE 触发器不能捕获 TRUNCATE TABLE 语句。尽管 TRUNCATE TABLE 语句实际上是没有 WHERE 子句的 DELETE（它删除所有行），但它是无日志记录的，因而不能执行触发器。因为 TRUNCATE T

21、ABLE 语句的权限默认授予表所有者且不可转让，所以只有表所有者才需要考虑无意中用 TRUNCATE TABLE 语句规避 DELETE 触发器的问题。无论有日志记录还是无日志记录，WRITETEXT 语句都不激活触发器。触发器中不允许以下 Transact-SQL 语句：ALTER DATABASECREATE DATABASEDISK INITDISK RESIZEDROP DATABASELOAD DATABASELOAD LOGRECONFIGURERESTORE DATABASERESTORE LOG  说明  由于 SQL Server 不支

22、持系统表中的用户定义触发器，因此建议不要在系统表中创建用户定义触发器。 多个触发器SQL Server 允许为每个数据修改事件（DELETE、INSERT 或 UPDATE）创建多个触发器。例如，如果对已有 UPDATE 触发器的表执行 CREATE TRIGGER FOR UPDATE，则将创建另一个更新触发器。在早期版本中，在每个表上，每个数据修改事件（INSERT、UPDATE 或 DELETE）只允许有一个触发器。 说明  如果触发器名称不同，则 CREATE TRIGGER（兼容级别为 70）的默认行为是在现有的触发器中添加其它触发器。如果触发器名称相同

23、，则 SQL Server 返回一条错误信息。但是，如果兼容级别等于或小于 65，则使用 CREATE TRIGGER 语句创建的新触发器将替换同一类型的任何现有触发器，即使触发器名称不同。有关更多信息，请参见 sp_dbcmptlevel。  递归触发器当在 sp_dboption 中启用 recursive triggers 设置时，SQL Server 还允许触发器的递归调用。递归触发器允许发生两种类型的递归： · 间接递归· 直接递归 使用间接递归时，应用程序更新表 T1，从而激发触发器 TR1，该触发器更新表 T2。在这种情况下，触发器 T2 将激发并更

24、新 T1。使用直接递归时，应用程序更新表 T1，从而激发触发器 TR1，该触发器更新表 T1。由于表 T1 被更新，触发器 TR1 再次激发，依此类推。下例既使用了间接触发器递归，又使用了直接触发器递归。假定在表 T1 中定义了两个更新触发器 TR1 和 TR2。触发器 TR1 递归地更新表 T1。UPDATE 语句使 TR1 和 TR2 各执行一次。而 TR1 的执行将触发 TR1（递归）和 TR2 的执行。给定触发器的 inserted 和 deleted 表只包含与唤醒调用触发器的 UPDATE 语句相对应的行。说明  只有启用 sp_dboption 的 recur

25、sive triggers 设置，才会发生上述行为。对于为给定事件定义的多个触发器，并没有确定的执行顺序。每个触发器都应是自包含的。 禁用 recursive triggers 设置只能禁止直接递归。若要也禁用间接递归，请使用 sp_configure 将 nested triggers 服务器选项设置为 0。如果任一触发器执行了 ROLLBACK TRANSACTION 语句，则无论嵌套级是多少，都不会进一步执行其它触发器。嵌套触发器触发器最多可以嵌套 32 层。如果一个触发器更改了包含另一个触发器的表，则第二个触发器将激活，然后该触发器可以再调用第三个触发器，依此类推。如果链中任

26、意一个触发器引发了无限循环，则会超出嵌套级限制，从而导致取消触发器。若要禁用嵌套触发器，请用 sp_configure 将 nested triggers 选项设置为 0（关闭）。默认配置允许嵌套触发器。如果嵌套触发器是关闭的，则也将禁用递归触发器，与 sp_dboption 的 recursive triggers 设置无关。延迟名称解析SQL Server 允许 Transact-SQL 存储过程、触发器和批处理引用编译时不存在的表。这种能力称为延迟名称解析。但是，如果 Transact-SQL 存储过程、触发器或批处理引用在存储过程或触发器中定义的表，则只有当兼容级别设置（通过执行 sp

27、_dbcmptlevel 设置）等于 65 时，才会在创建时发出警告。如果使用批处理，则在编译时发出警告。如果引用的表不存在，将在运行时返回错误信息。有关更多信息，请参见延迟名称解析和编译。 权限CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员以及 db_owner 和 db_ddladmin 固定数据库角色成员，并且不可转让。若要检索表或视图中的数据，用户必须在表或视图中拥有 SELECT 语句权限。若要更新表或视图的内容，用户必须在表或视图中拥有 INSERT、DELETE 和 UPDATE 语句权限。如果视图中存在 INSTEAD OF

28、 触发器，用户必须在该视图中有 INSERT、DELETE 和 UPDATE 特权，以对该视图发出 INSERT、DELETE 和 UPDATE 语句，而不管实际上是否在视图上执行了这样的操作。示例A. 使用带有提醒消息的触发器当有人试图在 titles 表中添加或更改数据时，下例将向客户端显示一条消息。说明  消息 50009 是 sysmessages 中的用户定义消息。有关创建用户定义消息的更多信息，请参见 sp_addmessage。  USE pubsIF EXISTS (SELECT name FROM

29、 sysobjects      WHERE name = 'reminder' AND type = 'TR')   DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE AS RAISERROR (5

30、0009, 16, 10)GOB. 使用带有提醒电子邮件的触发器当 titles 表更改时，下例将电子邮件发送给指定的人员 (MaryM)。USE pubsIF EXISTS (SELECT name FROM sysobjects      WHERE name = 'reminder' AND type = 'TR')   

31、;DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETE AS   EXEC master.xp_sendmail 'MaryM',       'Don''t forget to print a

32、60;report for the distributors.'GOC. 在 employee 和 jobs 表之间使用触发器业务规则由于 CHECK 约束只能引用定义了列级或表级约束的列，表间的任何约束（在下例中是指业务规则）都必须定义为触发器。下例创建一个触发器，当插入或更新雇员工作级别 (job_lvls) 时，该触发器检查指定雇员的工作级别（由此决定薪水）是否处于为该工作定义的范围内。若要获得适当的范围，必须引用 jobs 表。USE pubsIF EXISTS (SELECT name FROM&

33、#160;sysobjects      WHERE name = 'employee_insupd' AND type = 'TR')   DROP TRIGGER employee_insupdGOCREATE TRIGGER employee_insupdON employeeFOR INSERT, UPDATEAS/*/*

34、60;Get the range of level for this job type from the jobs table. */DECLARE min_lvl tinyint,   max_lvl tinyint,   emp_lvl tinyint,   job_id smallintSELECT min_lv

35、l = min_lvl,    max_lvl = max_lvl,    emp_lvl = i.job_lvl,   job_id = i.job_idFROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id   

36、; JOIN jobs j ON j.job_id = i.job_idIF (job_id = 1) and (emp_lvl <> 10) BEGIN   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1

37、)   ROLLBACK TRANSACTIONENDELSEIF NOT (emp_lvl BETWEEN min_lvl AND max_lvl)BEGIN   RAISERROR ('The level for job_id:%d should be between %d and %d.',    

38、0; 16, 1, job_id, min_lvl, max_lvl)   ROLLBACK TRANSACTIONENDD. 使用延迟名称解析下例创建两个触发器以说明延迟名称解析。 USE pubsIF EXISTS (SELECT name FROM sysobjects      WHERE name = 'trig1' AN

39、D type = 'TR')   DROP TRIGGER trig1GO- Creating a trigger on a nonexistent table.CREATE TRIGGER trig1on authorsFOR INSERT, UPDATE, DELETEAS    SELECT a.au_lname,&

40、#160;a.au_fname,     FROM authors a INNER JOIN does_not_exist x       ON a.au_id = x.au_idGO- Here is the statement to actually see the text

41、0;of the trigger.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c    ON o.id = c.idWHERE o.type = 'TR' and  = 'trig1'- Creating a 

42、trigger on an existing table, but with a nonexistent - column.USE pubsIF EXISTS (SELECT name FROM sysobjects      WHERE name = 'trig2' AND type = &

43、#39;TR')   DROP TRIGGER trig2GOCREATE TRIGGER trig2 ON authorsFOR INSERT, UPDATEAS    DECLARE fax varchar(12)   SELECT fax = phone      FROM autho

44、rsGO- Here is the statement to actually see the text of the trigger.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c    ON o.id = c.idWHERE o.type&

45、#160;= 'TR' and  = 'trig2'E. 使用 COLUMNS_UPDATED下例创建两个表：一个 employeeData 表和一个 auditEmployeeData 表。人力资源部的成员可以修改 employeeData 表，该表包含敏感的雇员薪水信息。如果更改了雇员的社会保险号码 (SSN)、年薪或银行帐户，则生成审核记录并插入到 auditEmployeeData 审核表。通过使用 COLUMNS_UPDATED() 功能，可以快速测试对这些包含敏感雇员信息的列所做的更改。只

46、有在试图检测对表中的前 8 列所做的更改时，COLUMNS_UPDATED() 才起作用。USE pubsIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_NAME = 'employeeData')   DROP TABLE employeeDataIF EXISTS(SELECT TABLE_NAME&

47、#160;FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_NAME = 'auditEmployeeData')   DROP TABLE auditEmployeeDataGOCREATE TABLE employeeData (   emp_id int NOT NULL,   emp_bankA

48、ccountNumber char (10) NOT NULL,   emp_salary int NOT NULL,   emp_SSN char (11) NOT NULL,   emp_lname nchar (32) NOT NULL,   emp_fname nchar (32) NOT&

49、#160;NULL,   emp_manager int NOT NULL   )GOCREATE TABLE auditEmployeeData (   audit_log_id uniqueidentifier DEFAULT NEWID(),   audit_log_type char (3) NOT NULL,  

50、60;audit_emp_id int NOT NULL,   audit_emp_bankAccountNumber char (10) NULL,   audit_emp_salary int NULL,   audit_emp_SSN char (11) NULL,   audit_user sysname DEFAULT SUSE

51、R_SNAME(),   audit_changed datetime DEFAULT GETDATE()   )GOCREATE TRIGGER updEmployeeData ON employeeData FOR update AS/*/*Check whether columns 2, 3 or 4 has been updated.

52、60;If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1)+power(2,(3-1)+power(2,(4-1) = 14. To check if all colu

53、mns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/   IF (COLUMNS_UPDATED() & 14) > 0/*/*Use IF (COLUMNS_UPDATED() & 14) = 14 to&

54、#160;see if all of columns 2, 3, and 4 are updated.*/      BEGIN- Audit OLD record.      INSERT INTO auditEmployeeData        

55、; (audit_log_type,         audit_emp_id,         audit_emp_bankAccountNumber,         audit_emp_salary,         aud

56、it_emp_SSN)         SELECT 'OLD',             del.emp_id,            del.emp_bankAccountNumber,   

57、0;        del.emp_salary,            del.emp_SSN         FROM deleted del- Audit NEW record.      IN

58、SERT INTO auditEmployeeData         (audit_log_type,         audit_emp_id,         audit_emp_bankAccountNumber,       &#

59、160; audit_emp_salary,         audit_emp_SSN)         SELECT 'NEW',            ins.emp_id,       

60、     ins.emp_bankAccountNumber,            ins.emp_salary,            ins.emp_SSN         FROM inserted ins   ENDGO/*/*Inserting a new employee does not cause the UPDATE trigger to fire.*/INSERT INTO employeeData   VALUES ( 101, 'U