SQLServer之触发器简介

触发器定义

触发器是数据库服务器中发生事件时自动执行的一种特殊存储过程。sqlserver允许为任何特定语句创建多个触发器。它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对数据库进行操作时就会激活它执行。

触发器分类

分为dml触发器、ddl触发器、登录触发器、嵌套触发器、递归触发器。

dml触发器

定义

如果用户要通过数据操作语言 (dml) 事件编辑数据，则执行 dml 触发器。 dml 事件是针对表或视图的 insert、update 或 delete 语句。 在激发任何有效的事件时，将会激发这些触发器，而无论是否会影响任何表行。 

分类

after触发器（之后触发）

在执行 insert、update、merge 或 delete 语句的操作之后执行 after 触发器。 如果违反了约束，则永远不会执行 after 触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。 对于在 merge 语句中指定的每个 insert、update 或 delete 操作，将为每个 dml 操作触发相应的触发器。

instead of触发器

instead of触发器替代下列触发语句的标准操作。 因此，触发器可用于对一个或多个列执行错误或值检查，然后在插入、更新或删除行之前执行其他操作。instead of触发器的主要优点是可以使不能更新的视图支持更新。 instead of触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

下表对 after 触发器和 instead of 触发器的功能进行了比较。

函数	after 触发器	instead of 触发器
适用范围	表	表和视图
每个表或视图包含触发器的数量	每个触发操作（update、delete 和 insert）包含多个触发器	每个触发操作（update、delete 和 insert）包含一个触发器
级联引用	无任何限制条件	不允许在作为级联引用完整性约束目标的表上使用 instead of update 和 delete 触发器。
执行	晚于： 约束处理 声明性引用操作 创建插入的 和 删除的 表 触发操作	之前：约束处理 代替：触发操作 之后：创建 插入的 和 删除的 表
执行顺序	可指定第一个和最后一个执行	不适用
插入的和 删除的表中的 varchar(max) 、 nvarchar(max) 和 varbinary(max) 列引用	allowed	allowed
插入的和 删除的表中的 text 、 ntext 和 image 列引用。	不允许	allowed

clr 触发器

clr 触发器可以是 after 触发器或 instead of 触发器。 clr 触发器还可以是 ddl 触发器。 clr 触发器将执行在托管代码（在 .net framework 中创建并在 transact-sql 中上载的程序集的成员）中编写的方法，而不用执行 sql server存储过程。

应用场景

dml 触发器可用于强制业务规则和数据完整性、查询其他表并包括复杂的transact-sql 语句。 将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。 如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

优点

dml 触发器类似于约束，因为可以强制实体完整性或域完整性。 一般情况下，实体完整性总应在最低级别上通过索引进行强制，这些索引应是 primary key 和 unique 约束的一部分，或者是独立于约束而创建的。 域完整性应通过 check 约束进行强制，而引用完整性 (ri) 则应通过 foreign key 约束进行强制。 当约束支持的功能无法满足应用程序的功能要求时，dml 触发器非常有用。

下面的列表比较 dml 触发器和约束，并在 dml 触发器优于约束时进行标识。

• dml 触发器可以将更改通过级联方式传播给数据库中的相关表；不过，使用级联引用完整性约束可以更有效地执行这些更改。 除非 references 子句定义了级联引用操作，否则 foreign key 约束只能用与另一列中的值完全匹配的值来验证列值。

• dml 触发器可以防止恶意或错误的 insert、update 以及 delete 操作，并强制执行比 check 约束定义的限制更为复杂的其他限制。

  与 check 约束不同，dml 触发器可以引用其他表中的列。 例如，触发器可以使用另一个表中的 select 比较插入或更新的数据，以及执行其他操作，如修改数据或显示用户定义错误信息。

• dml 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。

• 一个表中的多个同类 dml 触发器（insert、update 或 delete）允许采取多个不同的操作来响应同一个修改语句。

• 约束只能通过标准化的系统错误消息来传递错误消息。 如果应用程序需要（或能受益于）使用自定义消息和较为复杂的错误处理，则必须使用触发器。

• dml 触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。 当更改外键且新值与其主键不匹配时，这样的触发器将生效。 但是，foreign key 约束通常用于此目的。

• 如果触发器表上存在约束，则在 instead of 触发器执行后但在 after 触发器执行前检查这些约束。 如果违反了约束，则回滚 instead of 触发器操作并且不执行 after 触发器。

ddl触发器

定义

ddl 触发器用于响应各种数据定义语言 (ddl) 事件。 这些事件主要与以关键字 create、alter、drop、grant、deny、revoke 或 update statistics 开头的 transact-sql 语句对应。 执行 ddl 式操作的系统存储过程也可以激发 ddl 触发器。

分类

transact-sql ddl 触发器

用于执行一个或多个 transact-sql 语句以响应服务器范围或数据库范围事件的一种特殊类型的 transact-sql 存储过程。 例如，如果执行某个语句（如 alter server configuration）或者使用 drop table 删除某个表，则激发 ddl 触发器。

clr ddl 触发器

clr 触发器将执行在托管代码（在 .net framework 中创建并在 transact-sql 中上载的程序集的成员）中编写的方法，而不用执行 sql server存储过程。

仅在运行触发 ddl 触发器的 ddl 语句后，ddl 触发器才会激发。 ddl 触发器无法作为 instead of 触发器使用。 对于影响局部或全局临时表和存储过程的事件，不会触发 ddl 触发器。

ddl 触发器不会创建特殊的 inserted 和 deleted 表。

可以使用 eventdata 函数捕获有关激发 ddl 触发器的事件以及触发器导致的后续更改的信息。

为每个 ddl 事件创建多个触发器。

与 dml 触发器不同，ddl 触发器的作用域不是架构。 因此，不能将 object_id、object_name、objectproperty 和 objectpropertyex 之类的函数用于查询有关 ddl 触发器的元数据。 请改用目录视图。

服务器范围的 ddl 触发器显示在 sql server management studio 对象资源管理器的“触发器”文件夹中。 此文件夹位于 “服务器对象” 文件夹下。 数据库范围的 ddl 触发器显示在“数据库触发器”文件夹中。 此文件夹位于相应数据库的 “可编程性” 文件夹下。

应用场景

• 防止对数据库架构进行某些更改。

• 希望数据库中发生某种情况以响应数据库架构的更改。

• 记录数据库架构的更改或事件。

优点

更加安全

通过使用 eventdata( ) 函数，可以在触发器中使用xml信息。

登录触发器

定义

登录触发器将为响应 logon 事件而激发存储过程。 与 sql server实例建立用户会话时将引发此事件。 登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。 因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 print 语句的消息）会传送到 sql server 错误日志。 如果身份验证失败，将不激发登录触发器。

应用场景

可以使用登录触发器来审核和控制服务器会话，例如通过跟踪登录活动、限制 sql server的登录名或限制特定登录名的会话数。

指定第一个和最后一个触发器

可以对 logon 事件定义多个触发器。 通过使用 sp_settriggerorder 系统存储过程，可以将这些触发器中的任何一个指定为针对某事件激发的第一个或最后一个触发器。 sql server 不保证其余触发器的执行顺序。

嵌套触发器

如果一个触发器在执行操作时调用了另外一个触发器，而这个触发器又接着调用了下一个触发器，那么就形成了嵌套触发器。嵌套触发器在安装时就被启用，但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。

注意事项

• 默认情况下，嵌套触发器配置选项是开启的。
• 在同一个触发器事务中，一个嵌套触发器不能被触发两次。
• 由于触发器是一个事务，如果在一系列嵌套触发器的任意层次中发生错误，则整个事物都将取消，而且所有数据回滚。

递归触发器

定义

触发器的递归是指一个触发器从其内部再一次激活该触发器。

分类

sqlserver中的递归触发器包括两种：直接递归和间接递归。

• 直接递归：触发器被触发后并执行一个操作，而该操作又使用一个触发器再次被触发。
• 间接递归：触发器被触发并执行一个操作，而该操作又使另一个表中的某个触发器被触发，第二个触发器使原始表得到更新，从而再次触发第一个触发器。

默认情况下，递归触发器选项是禁用的。递归触发器最多只能递归16层，如果递归中的第16个触发器激活了第17个触发器，则结果与发布的rollback命令一样，所有数据都将回滚。

触发器优缺点

优点：

　　1、触发器是自动的。当对表中的数据做了任何修改之后立即被激活。

　　2、触发器可以通过数据库中的相关表进行层叠修改。

　　3、触发器可以强制限制。这些限制比用check约束所定义的更复杂。与check约束不同的是，触发器可以引用其他表中的列。

缺点：

　　1、增加了系统的复杂性。

　　2、嵌套触发器容易出现死锁现象。

　　3、触发器并没有提升多少性能。

　　4、可移植性差。

　　5、占用服务器资源，给服务器造成压力。

　　6、复杂的触发器维护困难。