\t\t触发器

触发器,实际上就是一种特殊类型的存储过程，它是在执行某些特定的T-SQL语句时自动执行的一种存储过程。在SQL Server 2005中，根据SQL语句的不同，把触发器分为两类：一类是DML触发器，一类是DLL触发器。

11.1.1 触发器的概念和作用

在SQL Server 2005里，可以用两种方法来保证数据的有效性和完整性：约束(check)和触发器(Trigger)。约束是直接设置于数据表内，只能现实一些比较简 单的功能操作，如：实现字段有效性和唯一性的检查、自动填入默认值、确保字段数据不重复(即主键)、确保数据表对应的完整性(即外键)等功能。

触发器是针对数据表(库)的特殊的存储过程，当这个表发生了 Insert、Update或Delete操作时，会自动激活执行的，可以处理各种复杂的操作。在SQL Server 2005中，触发器有了更进一步的功能，在数据表(库)发生Create、Alter和Drop操作时，也会自动激活执行。

触发器常用的一些功能如下：

* 完成比约束更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束

* 检查所做的SQL是否允许：触发器可以检查SQL所做的操作是否被允许。例如：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消该删除操作。

* 修改其它数据表里的数据：当一个SQL语句对数据表进行操作的时候，触发器可以根据该SQL语句的操作情况来对另一个数据表进行操作。例如：一个订单取消的时候，那么触发器可以自动修改产品库存表，在订购量的字段上减去被取消订单的订购数量。

* 调用更多的存储过程：约束的本身是不能调用存储过程的，但是触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多过存储过程。

* 发送SQL Mail：在SQL语句执行完之后，触发器可以判断更改过的记录是否达到一定条件，如果达到这个条件的话，触发器可以自动调用SQL Mail来发送邮件。例如：当一个订单交费之后，可以物流人员发送Email，通知他尽快发货。

* 返回自定义的错误信息：约束是不能返回信息的，而触发器可以。例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用程序。

* 更改原本要操作的SQL语句：触发器可以修改原本要操作的SQL语句，例如原本的SQL语句是要删除数据表里的记录，但该数据表里的记录是最要记录，不允许删除的，那么触发器可以不执行该语句。

* 防止数据表构结更改或数据表被删除：为了保护已经建好的数据表，触发器可以在接收到Drop和Alter开头的SQL语句里，不进行对数据表的操作。

11.1.2 触发器的种类

在SQL Server 2005中，触发器可以分为两大类：DML触发器和DDL触发器

* DML触发器：DML触发器是当数据库服务器中发生数据操作语言(Data Manipulation Language)事件时执行的存储过程。DML触发器又分为两类：After触发器和Instead Of触发器

* DDL触发器：DDL触发器是在响应数据定义语言(Data Definition Language)事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。

只在表或视图上INSERT, UPDATE, or DELETE时触发的触发器 (DML触发器)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name
-------------------------------------------------------------------
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL触发器)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

11.2 DML触发器的分类

SQL Server 2005的DML触发器分为两类：

* After触发器(事后触发器)：这类触发器是在记录已经改变完之后(after)，才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。

* Instead Of触发器(事前触发器)：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作(Insert、Update、Delete)，而去执行触发器本身所定义的操作。

11.3 DML触发器的工作原理

在SQL Server 2005里，为每个DML触发器都定义了两个特殊的表，一个是插入表，一个是删除表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。

这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。

插入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。

删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新前的记录(更新完后即被删除)；对于删除记录操作来说，删除表里存入的是被删除的旧记录。

After触发器的工作原理

After触发器是在记录更变完之后才被激活执行的. 数据库操作步骤：

(1)接收SQL语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。

(2)从产品库存表里删除该产品记录。

(3)从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，完成操作，从内存里清除删除表；如果不为零的话，用Rollback Transaction语句来回滚操作。

激活触发器的动作

Inserted表

Deleted表

Insert

存放要插入的记录

Update

存放要更新的记录

存放更新前的旧记录

Delete

存放要删除的旧记录

11.3.2 Instead Of触发器的工作原理

Instead Of触发器与After触发器不同。Instead Of触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。

设计Instead Of触发器

Instead Of触发器看起来就简单多了，在SQL Server服务器接到执行SQL语句请求后，先建立临时的Inserted表和Deleted表，然后就触发了Instead Of触发器，至于那个SQL语句是插入数据、更新数据还是删除数据，就一概不管了，把执行权全权交给了Instead Of触发器，由它去完成之后的操作。

Instead Of触发器的使用范围

Instead Of触发器可以同时在数据表和视图中使用，通常在以下几种情况下，建议使用Instead Of触发器：

* 数据库里的数据禁止修改：例如电信部门的通话记录是不能修改的，一旦修改，则通话费用的计数将不正确。在这个时候，就可以用Instead Of触发器来跳过Update修改记录的SQL语句。

* 有可能要回滚修改的SQL语句：如11.5.3节中的例二，用After触发器并不是一个最好的方法，如果用Instead Of触发器，在判断折扣大于0.6时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。

* 在视图中使用触发器：因为After触发器不能在视图中使用，如果想在视图中使用触发器，就只能用Instead Of触发器。

* 用自己的方式去修改数据：如不满意SQL直接的修改数据的方式，可用Instead Of触发器来控制数据的修改方式和流程。

设计简单的Instead Of触发器

Instead Of触发器的语法如下：

CREATE TRIGGER 触发器名
   ON 数据表名或视图名
   Instead Of INSERT或DELETE或UPDATE
AS
BEGIN
SET NOCOUNT ON --屏蔽在触发器里Insert语句执行完之后返回的所影响行数的消息

         --这里是要运行的SQL语句
END
GO

从上面可以看得出，Instead Of触发器与After触发器的语法几乎一致，只是简单地把After改为Instead Of。前面说过的11.5.3节中的例二，用After触发器并不是一个最好的方法，如果用Instead Of触发器，在判断折扣大于0.6时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。

其他注意事项

* After触发器只能用于数据表中，Instead Of触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。
* 一个数据表可以有多个触发器，但是一个触发器只能对应一个表。
* 在同一个数据表中，对每个操作（如Insert、Update、Delete）而言可以建立许多个After触发器，但Instead Of触发器针对每个操作只有建立一个。
* 如果针对某个操作即设置了After触发器又设置了Instead Of触发器，那么Instead of触发器一定会激活，而After触发器就不一定会激活了。
* Truncate Table语句虽然类似于Delete语句可以删除记录，但是它不能激活Delete类型的触发器。因为Truncate Table语句是不记入日志的。
* WRITETEXT语句不能触发Insert和Update型的触发器。
* 不同的SQL语句，可以触发同一个触发器，如Insert和Update语句都可以激活同一个触发器。

建立触发器的SQL语句
CREATE TRIGGER 触发器名
   ON 数据表名或视图名
   AFTER INSERT或DELETE或UPDATE
AS
BEGIN
         --这里是要运行的SQL语句
END
GO

现在再对上面的代码进行进一步的说明：

* CREATE TRIGGER 触发器名：这一句声明SQL语句是用来建立一个触发器。其中触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的，所以有很多人都 以为只要是在不同的数据表中，触发器的名称就可以相同，其实触发器的全名(Server.Database.Owner.TriggerName)是必须唯一的，这与触发器在哪个数据表或视图无关。

* ON 数据表名或视图名：这是指定触发器所在的数据表或视图，但是请注意，只有Instead Of触发器才能建立在视图上。并且有设置为With Check Option的视图也不允许建立Instead Of触发器。

* AFTER INSERT或 DELETE UPDATE：这是指定触发器的类型。其中After可以用For来代取，它们的意思都是一样的，代表只有在数据表的操作都已正确完成后才会激活的触发器。INSERT、 DELETE和UPDATE至少要指定一个，当然也可以指定多个，若指定多个时，必须用逗号来分开。其顺序可以任意摆放。

* With Encryption：With Encryption是用来加密触发器的，放在“On 数据表名或视图名”的后面，“For”的前面。如果使用了这句话，该触发器将会被加密，任何人都看不到触发器的内容了。

当同一个操作定义的触发器越来越多的时候，触发器被激活的次序就会 变得越来越重要了。在SQL Server 2005里，用存储过程【sp_settriggerorder】可以为每一个操作各指定一个最先执行的After触发器和最后执行的After触发器。 sp_settriggerorder语法如下：

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
        , [ @order = ] 'value'
        , [ @stmttype = ] 'statement_type'
        [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

翻译成中文就是

sp_settriggerorder 触发器名,
        激活次序,
        激活触发器的动作

解释如下：

* 触发器名，要用单引号括起来，因为它是一个字符串。
* 激活次序可以为First、Last和None：First是指第一个要激活的触发器；Last是指它最后一个要激活的触发器；None是不指激活序，由程序任意触发。
* 激活触发器的动作可以是：Insert、Update和Delete。
* 每个操作最多只能设一个First触发器和一个Last触发器。
* 如果要取消已经设好的First触发器或Last触发器，只要把它们设为None触发器即可。
* 如果用Alter命令修改过触发器内容后，该触发器会自动变成None触发器。所以用Alter命令也可以用来取消已经设好的First触发器或Last触发器。
* 只有After触发器可以设置激活次序，Instead Of触发器不可以设置激活次序。
* 激活触发器的动作必须和触发器内部的激活动作一致。举例说明：After Insert触发器，只能为Insert操作设置激活次序，不能为Delete操作设置激活次序。

触发器的嵌套

当一个触发器执行时，能够触活另一个触发器，这种情况就是触发器的嵌套。在SQL Server 2005里，触发器能够嵌套到32层。

触发器的递归[ 直接递归 | 间接递归 ]

触发器的递归是指，一个触发器从其内部又一次激活该触发器。例如一个Insert触发器的内部还有一条对本数据表插入记录的SQL语句，那么这个插入语句就有可能再一次激活这个触发器本身。当然，这种递归的触发器内部还会有判断语句，要一定的情况下才会执行那个SQL语句，否则的话，就会变成死循环了。

上面的例子说的是直接递归的触发器，还有一种是间接递归的触发器， 举例说明：当向A表插入一条记录时，激活了A表的Insert触发器，A表的Insert触发器里有一个SQL语句是对B表进行Insert操作的，而在 B表的Insert触发器里也有一句话是对A表进行Insert操作的。这样就是触发器的间接递归。

一般情况来说，SQL Server服务器是不允许递归的，如果要打开触发器递归的功能，同样是将【允许触发器激活其他触发器】设为True.

2005新增功能：DDL触发器
DDL触发器是SQL Server 2005新增的一个触发器类型，是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发。一般用于数据库中执行管理任务。

与DML触发器一样，DDL触发器也是通过事件来激活，并执行其中 的SQL语句的。但与DML触发器不同，DML触发器是响应Insert、Update或Delete语句而激活的，DDL触发器是响应Create、 Alter或Drop开头的语句而激活的。一般来说，在以下几种情况下可以使用DDL触发器：

* 数据库里的库架构或数据表架构很重要，不允许被修改。
* 防止数据库或数据表被误操作删除。
* 在修改某个数据表结构的同时修改另一个数据表的相应的结构。
* 要记录对数据库结构操作的事件。