触发器(Trigger)(七)

npkaida 2015-03-12

展开全文

设计Instead Of触发器

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

Instead Of触发器的使用范围

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

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

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

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

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

设计简单的Instead Of触发器

Instead Of触发器的语法如下：

CREATE TRIGGER 触发器名

ON 数据表名或视图名

Instead Of INSERT或DELETE或UPDATE

BEGIN

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

END

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

CREATE TRIGGER 订单明细_Insert

ON 订单明细

Instead Of INSERT

BEGIN

SET NOCOUNT ON;

declare

@订单ID int,

@产品ID int,

@单价 money,

@数量 smallint,

@折扣 real

set @订单ID = (select 订单ID from inserted)

set @产品ID = (select 产品ID from inserted)

set @单价 = (select 单价 from inserted)

set @数量 = (select 数量 from inserted)

set @折扣 = (select 折扣 from inserted)

if (@折扣)>0.6

print '折扣不能大于0.6'

else

INSERT INTO 订单明细

(订单ID,产品ID,单价,数量,折扣)

VALUES

(@订单ID,@产品ID,@单价,@数量,@折扣)

END

上面的触发器里写入了一句“SET NOCOUNT ON”，这一句的作用是，屏蔽在触发器里Insert语句执行完之后返回的所影响行数的消息。

查看DML触发器

查看已经设计好的DML触发器有两种方式，一种是通用Management Studio来查看，一种是利用系统存储过程来查看。

在Management Studio中查看触发器

在Management Studio中查看触发器的步骤：

（1）启动Management Studio，登录到指定的服务器上。

（2）在如图13所示界面的【对象资源管理器】下选择【数据库】，定位到要查看触发器的数据表上，并找到【触发器】项。

图13 查看触发器列表

（3）单击【触发器】，在右边的【摘要】对话框里，可以看到已经建好的该数据表的触发器列表。如果在点击【触发器】后，右边没有显示【摘要】对话框，可以在单击菜单栏上的【视图】菜单，选择【摘要】选项，打开【摘要】对话框。如果在【摘要】对话框里没有看到本应存在的触发器列表，可以【摘要】对话框里右击空白处，在弹出的快捷菜单中选择【刷新】选项，刷新对话框后即可看到触发器列表。

（4）双击要查看的触发器名，Management Studio自动弹出一个【查询编辑器】对话框，对话框里显示的是该触发器的内容，如图14所示：

图14 查看触发器内容

用系统存储过程查看触发器

SQL Server 2005里已经建好了两个系统存储过程，可以用这两个系统存储过程来查看触发器的情况：

sp_help：

系统存储过程“sp_help”可以了解如触发器名称、类型、创建时间等基本信息，其语法格式为：

sp_help ‘触发器名’

举例：

sp_help '产品_Insert'

运行结果如图11.15所示，可以看到触发器“产品_insert”的基本情况。

图11.15 查看触发器的基本情况

sp_helptext：