关闭

触发器(Trigger)(五)

1878人阅读 评论(0) 收藏 举报

  建立触发器的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 INSERTDELETEUPDATE

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来代取,它们的意思都是一样的,代表只有在数据表的操作都已正确完成后才会激活的触发器。INSERTDELETEUPDATE至少要指定一个,当然也可以指定多个,若指定多个时,必须用逗号来分开。其顺序可以任意摆放。

l  With EncryptionWith 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触发器的激活顺序

对于同一个操作,如InsertUpdateDelete来说,可以建立多个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 触发器名,

        激活次序,

        激活触发器的动作

解释如下:

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

激活次序可以为FirstLastNoneFirst是指第一个要激活的触发器;Last是指它最后一个要激活的触发器;None是不指激活序,由程序任意触发。

激活触发器的动作可以是:InsertUpdateDelete

上面的例子里,先激活的是【产品_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触发器激活顺序时,还有几点是需要注意的:

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

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

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

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

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

Exec sp_settriggerorder

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

go 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:237600次
    • 积分:2947
    • 等级:
    • 排名:第12379名
    • 原创:24篇
    • 转载:102篇
    • 译文:0篇
    • 评论:95条
    文章分类
    最新评论