巧妙应用SQL Server触发器

徐晓丽

0 前言

在大型关系数据库设计中，会经常用到触发器。它的特点是：一旦被定义，就存在于后台数据库系统（server，服务器方）中，并会在往表中插入记录、更改记录或者删除记录时，被自动地隐式执行，从而使得它的设计既与前台（client，客户机方）的平台无关，又免除了前台相关的数据操作设计。因此，触发器可以用来对表实施复杂的完整性约束，当触发器所保护的数据发生改变时，触发器会自动被激活，从而防止对数据的不正确修改。

1 触发器的种类

根据触发器被激活的时机不同，SQL Server 2000中提供了两种类型的触发器：INSTEAD OF触发器和AFTER触发器[1]。

AFTER触发器在一个INSERT、UPDATE或DELETE语句完成之后执行，进行约束检查等动作都将在AFTER触发器被激活之前发生。AFTER触发器只能用于表。

INSTEAD OF触发器用于替代引起触发器执行的T-SQL语句。除表之外，INSTEAD OF触发器也可以用于视图，用来扩展视图可以支持的更新操作。

一个表或视图的每个修改动作（INSERT、UPDATE和DELETE）都可以有一个INSTEAD OF触发器，但可以有多个AFTER触发器。

2 触发器的作用

触发器主要用于如下几个方面：自动生成派生列值；禁止非法事务；增强复杂的安全识别； 在分布式数据库中增强参照完整性；增强复杂的商业规则；提供透明的事件日志；提供高级审计；维护同步表复制；在表存取上进行聚合统计。开发人员一般是对上述几种情况作组合使用，可归为以下四种典型应用。

（1）对库中相关表进行连环更新，如：键值的同步更新，数据冗余实现，计算表的同步更新等；

（2）实现那些破坏完整性操作的拒绝，如：不匹配外键值的插入拒绝；

（3）实现库定义本身所不能实现的更为复杂的商业规则，如：更新操作的时间限制，更新数据的幅度限制等；

（4）实现简单的“如果……怎么办”的分析。

3 触发器的实现

触发器的实现离不了以下两个专用表：Inserted表和Deleted表。这是两个逻辑表，由系统来维护，用户不能对它们进行修改。它们存放在内存而不是数据库中。这两个表的结构总是与激活该触发器的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也会被删除。

Deleted表存放由于执行DELETE或UPDATE语句而要从表中删除的所有行。在执行DELETE或UPDATE操作时，被删除的行从表中被移动到Deleted表，这两个表不会有共同的行。

Inserted表存放由于执行INSERT或UPDATE语句而要向表中插入的所有行。在 INSERT或UPDATE事务中，新的行同时添加到激活触发器的表和Inserted表中，Inserted表的内容是激活触发器的表中新行的拷贝。

在创建触发器时需要制定以下内容：触发器的名称、触发器所基于的表或视图、触发器种类（AFTER 或 INSTEAD OF）、激活触发器的修改语句（INSERT、UPDATE和DELETE）、触发器执行的语句，用T-SQL语句创建触发器的具本语法如下：[2]

CREATE Trigger trigger_name

ON {table_name | view_name}

{ AFTER | INSTEAD OF}

[INSERT，UPDATE，DELETE]

AS

Sq_Statement

4 实际应用

在一个Market数据库中有Goods表、Customers表及Orders表，三个表的结构如下所示：

Goods:

Customers:

Orders:

现要求当向Orders表中插入一条记录时，ordersum（订货金额）字段的值由quantity*price而得到，并且如课该客户是重点客户，则对其打8.5折，若是一般客户，则不打折。

分析：由于quantity字段和price字段分别来自不同的表中，因此不能在创建表时用公式来完成。为了实现此功能，我们可考虑在Orders中针对Insert操作建立一个INSTEAD OF触发器，在触发器中查询下定单的客户的类别及所定货品的价格，计算出货品的ordersum的值；然后再将记录插入到Orders表中。具体代码如下所示：

CREATE TRIGGER orderinsert ON OrderS

INSTEAD OF INSERT

AS

declare @price money，@quantity int，@type varchar（10）

declare @sum money

select @price=price，@quantity=quantity，@type=type from goods，inserted，customers where goods.name=inserted.goodsnameandcustomers.customerid=inserted.customerid

if @type='一般客户'

set @sum=@price*@quantity

else

set @sum=@price*@quantity*0.85

insert into orders（goodsname，customerid，quantity，ordersum，orderdate）select goodsname，customerid，quantity，@sum，orderdate from inserted.

触发器建成以后，只有向order表中插入记录，就会自动计算ordersum字段的值，从而实现了比默认值及公式都更为复杂的字段默认值的设置。

5 结束语

本文通过使用触发器，实现了通过几个表的数据对某一字段进行自动计算的功能，完成了比CHECK约束更复杂的限制。当然，也可以通过触发器实现数据库的级联更新、级联删除及某些字段的自动更新功能（如增加某一订单时，减少Goods表中相应货品的库存量）。综上所述，触发器的主要特点在于，不管何种原因造成数据变化，它均能自动响应。触发器对数据库开发过程中遇到的问题，往往会有独到的解决方法。因此，在当今数据库系统的建立中，系统开发人员除了使用视图、存储过程等技术外，大多也使用触发器技术，以改进数据库系统建立的质量，使数据库的设计变得简洁和高效。

[1]罗运模，王珊等《SQL Server数据库系统基础》[M].高等教育出版社.

[2][美]James R.Groff Paul N.Weinberg 著.章小莉，宁欣，汪永好.等译《SQL 完全手册》[M].电子工业出版社.

[3]郭勐，张拥军，彭宇行.数据库触发器机制的设计与实现[J].电子技术应用，2005，（02）.

[4]莫裕清，SQL Server 2000中基于触发器的自动更新[J].中国科技信息.2006 Vol.8 P.221-222.

[5]黄晓涛，数据库触发器实现数据库系统的主动功能[J].计算机应用研究.2005 Vol.21 No.2 P.238-240.

[6]周爱华，南理勇.SQL Server 2000数据库触发器的应用[J].商场现代化，2008，（07）.