SQL Server 2005触发器2

SQL Server 2005触发器2

11.5.2 测试触发器功能

建好After Insert触发器之后，现在来测试一下触发器是怎么样被激活的。

（1）在Management Studio里新建一个查询，在弹出的【查询编辑器】对话框里输入以下代码： INSERT INTO 产品(产品名称) VALUES ('大苹果')

（2）单击【执行】按钮，可以看到【消息】对话框里显示出一句提示：“又添加了一种产品”，这说明，After Insert触发器被激活，并运行成功了。

而如果在【查询编辑器】里执行的不是一个Insert语句，而是一个Delete语句的话，After Insert触发器将不会被激活。如在【查询编辑器】输入以下语句： DELETE FROM 产品 WHERE (产品名称= '大苹果')

单击【执行】按钮，在【消息】对话框里只显示了一句“(1行受影 响)”的提示，而没有“又添加了一种产品”的提示。这是因为Delete语句是不能激活After Insert触发器，所以After Insert触发器里的“print ‘又添加了一种产品’”语句并没有执行。 11.5.3 建立触发器的SQL语句

回顾一下，在Management Studio新建一个触发器的时候，它在查询分析对话框给预设了一些SQL代码，这些代码其实上就是建立触发器的语法提示。现在来看一下完整的触发器语法代码： CREATE TRIGGER . ON . AFTER 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) 运行结果如图11.3所示：

图11.3 插入记录不符合触发器里的约束，则回滚操作 11.6 设置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 ('大苹果')

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

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

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