触发器(Trigger)(五)

  建立触发器的SQL语句

回顾一下，在Management Studio新建一个触发器的时候，它在查询分析对话框给预设了一些SQL代码，这些代码其实上就是建立触发器的语法提示。现在来看一下完整的触发器语法代码：

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>

   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>

   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>

AS

BEGIN

         -- SET NOCOUNT ON added to prevent extra result sets from

         -- interfering with SELECT statements.

         SET NOCOUNT ON;

    -- Insert statements for trigger here

END

GO

用中文改了一下，以上代码就一目了然了：

CREATE TRIGGER 触发器名

   ON  数据表名或视图名

   AFTER INSERT或DELETE或UPDATE

AS

BEGIN

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

END

GO

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

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

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

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

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

例一：以下是一个包含提醒电子邮件的触发器例子，如果订单表里记录有改动的的话（无论增加订单还是修改、删除订单），则给物流人员张三发送电子邮件：

CREATE TRIGGER 订单_Insert

ON 订单

AFTER INSERT, UPDATE, DELETE

AS

   EXEC master..xp_sendmail '张三',

      '订单有更改，请查询确定'

GO

例二：在订单明细表里，折扣字段不能大于0.6，如果插入记录时，折扣大于0.6的话，回滚操作。

CREATE TRIGGER 订单明细_Insert

   ON  订单明细

   AFTER INSERT

AS

BEGIN

         if (Select 折扣 from inserted)>0.6

         begin

                   print '折扣不能大于0.6'

                   Rollback Transaction

         end

END

GO

在示例二中运用了两个方法，一个是前面说过的，在Inserted表里查询某个字段，还有一个是用Rollback Transaction来回滚操作。如果用下面的SQL语句来进行Insert操作的话，插入记录将会不成功。

INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣)  

VALUES (11077,1,18,1,0.7)

运行结果如图7所示：

图7 插入记录不符合触发器里的约束，则回滚操作

设置After触发器的激活顺序

对于同一个操作，如Insert、Update或Delete来说，可以建立多个After Insert触发器，在 11.5.1 节中，已经建立了一个名为“产品_Insert”的触发器，现在再建立一个After Insert触发器，作用也是输出一句有好提示，提示内容为：“再一次告诉你，你又添加了一种产品”。

CREATE TRIGGER 产品_Insert1

   ON  产品

   AFTER INSERT

AS

BEGIN

         print '再一次告诉你，你又添加了一种产品'

END

GO

重新运行一下插入产品的SQL语句：

INSERT INTO 产品(产品名称)

VALUES ('大苹果')

如图8所示，运行一个Insert语句，在【消息】可以看到一共输出了两句话，说明激活两个不同的触发器。

图8 一个语句激活两个触发器

当同一个操作定义的触发器越来越多的时候，触发器被激活的次序就会变得越来越重要了。在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 触发器名,

        激活次序,

        激活触发器的动作

解释如下：

l  触发器名，要用单引号括起来，因为它是一个字符串。

l  激活次序可以为First、Last和None：First是指第一个要激活的触发器；Last是指它最后一个要激活的触发器；None是不指激活序，由程序任意触发。

l  激活触发器的动作可以是：Insert、Update和Delete。

上面的例子里，先激活的是【产品_Insert】触发器，后激活的是【产品_Insert1】触发器。如果把【产品_Insert1】触发器设为First触发器，把【产品_Insert】触发器设为Last触发器，那么结果将会完全不一样。设置语句如下：

Exec sp_settriggerorder

         '产品_Insert1','First','Insert'

go

Exec sp_settriggerorder

         '产品_Insert',’Last’,'Insert'

Go

重新运行一下插入产品的SQL语句：

INSERT INTO 产品(产品名称)

VALUES ('大苹果')

运行结果如图9，与图8比较一下，是不是激活次序已经发生变化了？

图9 按次序激活的激活器

在设置After触发器激活顺序时，还有几点是需要注意的：

l  每个操作最多只能设一个First触发器和一个Last触发器。

l  如果要取消已经设好的First触发器或Last触发器，只要把它们设为None触发器即可。

l  如果用Alter命令修改过触发器内容后，该触发器会自动变成None触发器。所以用Alter命令也可以用来取消已经设好的First触发器或Last触发器。

l  只有After触发器可以设置激活次序，Instead Of触发器不可以设置激活次序。

l  激活触发器的动作必须和触发器内部的激活动作一致。举例说明：After Insert触发器，只能为Insert操作设置激活次序，不能为Delete操作设置激活次序。以下的设置是错误的：

Exec sp_settriggerorder

         '产品_Insert1','First',’Update’

go