[求助]关于SQL触发器 - SQL Server论坛

得分:0

触发器的几种应用

　　摘　要　列举了触发器的几种代表性应用：数据分散—集中式模型的设计，历史数据的导出，应用系统间的数据接口。并对如何设计这些触发器进行了探讨。
　　关键词　触发器，数据分散—集中模型，历史数据导出，数据接口

1　引言
　　在大型数据库设计中，会经常用到触发器。它的特点是：一旦被定义，就存在于后台数据库系统（server，服务器方）中，并会在相应条件下自动地隐式执行，从而使得它的设计既与前台（client，客户机方）的平台无关，又免除了前台相关的数据操作设计。
　　在文献［1］中，列举了触发器的几种应用：审计;复杂的完整性约束;复杂的安全性授权;事件登录;列值导出;分布式数据库中表复制。

2　触发器的另外几种应用
2.1　数据分散——集中式模型设计
　　在实际开发过程中，经常遇到这样的数据维护要求：单位由多个部门组成，要求各部门只能维护本部门的数据，但另一方面，又需要将分散到各部门的数据集中起来进行汇总，得到本单位的汇总数据。如一个学校有多个系，学校需要各系的成绩汇总；一个工厂有多个生产车间，工厂需要各车间的产量汇总；一个公司有多个销售部门，公司需要各部门的销量汇总等等。
　　在这种情况下，如果不使用触发器的话，数据库设计就存在困难：
　　. 如果为每个部门都建立一个表，显然难以得到汇总的数据（在这种情况下，无法利用视图机制）；
　　. 如果所有的部门都共享一个表的话（这时，这张表中的数据实际就是汇总的数据），因为每个部门需要维护数据，所以都对这个表有修改权，因此在数据安全上难以控制。
　　使用触发器的话，上述问题便可迎刃而解：为每个部门建立一个表（该部门的所有权限只限于对此表有修改权），再为汇总数据也建立一个表，然后在每个部门表上建立触发器，使得部门表上有数据更新时，便会对应地更改汇总表中的相关数据（见图1）。

2.使用数据库触发器
应用程序经常需要实施复杂的业务规则,这些规则无法用完整性规则表示,所以最好不要按照常规思路在应用程序中实施完整性规则;而是用数据触发子(triggers)来实施业务规则。其优点是容易创建,可集中进行规则实施,避免不必要的网络I/O。利用数据库触发子可以使其他一些应用程序集中化和自动化。比如,计算item 表中的total 列的值是所订零件的数量乘以零件的单价,而零件单价存放在stock表中,当插入一个新的行项目时,应用程序计算total列的值有两种方法。
方法一:让应用程序通过SQL命令执行这个操作
DECLARE total REAL;
BEGIN
SELECT unitprice*quantity INTO total
FROM stock, item
WHERE id=4;
INSERT INTO item VALUES(...);
END
应用程序通过网络发出请求,取得某一些零件单价,然后插入这个含有该行计算值(tota l)的行。修改item表中某行数量值,应用程序需要包含相似的逻辑来计算。此外,多个用户还可能同一时刻插入和修改订单。总之,用这种方法来计算total 列时会在客户机/服务器系统中产生大量的网络传输。
方法二:用数据库触发子, 从一个行项目自动导出total 的值,
当用户在item表中插入新行或修改quantity时,无需任何网络访问。
CREATE TRIGGER Linetotal
BEFORE INSERT OR UPDATE OF quantity,stockid
ON item
FOR EACH ROW
DELARE
itemprice REAL;
BEGIN
SELECT unitprice
INTO itemprice
FROM stock
WHERE id=:new.stockid;
:new.tolal:=new.quantity*itemprice;
END linetotal;
当创建触发器linetotal后, 应用开发人员在编写应用程序时就不需考虑保持total列为最新值的问题,而且网络数据库上所有应用都会因此受益。

基于SQL SERVER触发器技术的实现

收稿日期：2002-02-25
作者简介：沈晨鸣(1963-)，男，江苏南京人，南京工程学院计算机工程系讲师。?
沈晨鸣?
（南京工程学院，江苏　南京　210013）
摘要：SQL SERVER环境下触发器技术可保证数据库中数据的完整性。实现该项技术可以解决相关技术问题，如单行和多行数据插入。
关键词：数据完整性；触发器；存储过程
中图分类号：TP315　　　　　文献标识码：A　　　　　　　
??
　　在数据库管理系统中，如何保证数据库中的数据完整性是一项重要的课题。数据完整性是指存储在数据库的数据的一致性。主要体现在以下几个方面：实体完整性（Entity Integrity）、域完整性（Domain Integrity）、参照完整性（Referential Integrity）和用户的自定义完整性（User define Integrity）。目前，已有多种方法来解决这个问题。从最基本的数据类型，到多种形式的约束条件，虽然都提出了数据完整性的解决方案，但由于这些方法较为简单，不能解决比较复杂的数据完整性问题。而触发器（Trigger）作为一种高级的技术，可以轻松地解决任何有关保证数据完整性的问题。
一、在SQL SERVER环境中使用触发器
　　1?触发器的工作原理
触发器是一种特殊类型的存储过程，它与表紧密联系在一起，在对表进行插入、删除和更新时，如该表（也称触发器表）有相应操作类型的触发器，则触发器便会自动触发执行。触发器分为INSERT触发器、DELETE触发器和UPDATE触发器3类。当向触发器表中插入数据时，INSERT触发器将触发执行，新的记录会增加到触发器表和inseted表中；当删除触发器表中的数据时，DELETE触发器将触发执行，被删除的记录会存放到deleted表中；当更新触发器表中的数据时，相当于插入一条新记录和删除一条旧记录，此时UPDATE触发器将触发执行，表中原有的记录存放到deleted表中，修改后的记录插入到inserted表中。其中inserted表和deleted表是两个逻辑表，由系统来维护，不允许用户直接对这两个表进行修改。它们存放于内存中，不存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。deleted表用于存储SQL语言中DELETE和UPDATE语句所影响的行的复本。在执行DELETE或 UPDATE语句时，行从触发器表中删除，并传输到 deleted 表中。deleted 表和触发器表通常没有相同的行；inserted 表用于存储 SQL语言中INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。inserted 表中的行是触发器表中新行的副本。
　　2?触发器的实现步骤
在笔者开发的教材管理系统中，建立了一个教材数据库JCSJK，其中有教材表JCB和教材进出明细表MXB，需要在对MXB进行插入、删除和修改时，动态地修改JCB中对应教材的库存数量。下面举例说明触发器的实现步骤。为减少篇幅，对表结构作了简化处理。以下操作在Microsoft SQL SERVER环境中完成。
　　(1)建立教材表JCB，并定义主键
CREATE TABLE ［dbo］.［JCB］ (?
［教材代码］［char］ (10) NOT NULL ,?
［教材名］［char］ (30) NOT NULL ,?
［价格］［decimal］(18, 2) NOT NULL ,?
［库存量］［int］ NULL ,?
［出版社］［char］ (20) NULL ?
) ON ［PRIMARY］?ALTER TABLE ［dbo］.［JCB］ WITH NOCHECK ADD
CONSTRAINT ［PK－JCB］ PRIMARY KEY CLUSTERED
(［教材代码］
) ON ［PRIMARY］
　　(2)建立教材进出明细表MXB，并定义主键和外?br> CREATE TABLE ［dbo］.［MXB］ (?
［教材代码］［char］ (10) NOT NULL ,?
［日期］［datetime］ NOT NULL ,?
［教材进］［int］ NULL ,?
［教材出］［int］ NULL ,?
［备注］［char］ (40) NULL ?
) ON ［PRIMARY］?
ALTER TABLE ［dbo］.［MXB］ WITH NOCHECK ADD
CONSTRAINT ［PK－MXB］ PRIMARY KEY CLUSTERED ?
(［教材代码］,?
［日期］?
) ON ［PRIMARY］
ALTER TABLE ［dbo］.［MXB］ ADD
CONSTRAINT ［FK－MXB－JCB］ FOREIGN KEY
(［教材代码］?) REFERENCES ［dbo］.［JCB］ (?
［教材代码］?) ON DELETE CASCADE ON UPDATE CASCADE
?图1显示了JCB和MXB两个表的关系。

　　(3)在MXB上建立INSERT触发器
CREATE TRIGGER MXB－INSE ON ［dbo］.［MXB］
FOR INSERT
AS?UPDATE JCB SET 库存量=库存量+
(SELECT 教材进-教材出 FROM INSERTED)
FROM JCB,INSERTED?WHERE JCB.教材代码=INSERTED.教材代码
(4)在MXB上建立DELETE触发器
CREATE TRIGGER MXB－DELE ON ［dbo］.［MXB］
FOR DELETE
AS
UPDATE JCB SET 库存量=库存量-
(SELECT 教材进-教材出 FROM DELETED)
FROM JCB,DELETED?WHERE JCB.教材代码=DELETED.教材代码〖HT〗
　　(5)在MXB上建立UPDATE触发器
CREATE TRIGGER MXB－UPDA ON ［dbo］.［MXB］
FOR UPDATE
AS
BEGIN?UPDATE JCB SET 库存量=库存量-
(SELECT 教材进-教材出 FROM DELETED)
FROM JCB,DELETED
WHERE JCB.教材代码=DELETED.教材代码
UPDATE JCB SET 库存量=库存量+
(SELECT 教材进-教材出 FROM INSERTED)
FROM JCB,INSERTED?WHERE JCB.教材代码=INSERTED.教材代码
END
　　通过以上步骤，设置了MXB表的3类触发器，当用户对MXB表进行插入、删除和修改时，将根据MXB中教材进出的情况动态地修改JCB中对应教材的库存量。由于在触发器中，涉及到的inserted表和deleted表均存放在内存中，因此，触发器的执行速度较快。
　　3?设计触发器的考虑
　　在写触发器代码时需要考虑的一个重要问题就是，引发触发器的语句可以是一个影响单行的语句，也可以是一个影响多行的语句。这在 UPDATE 和DELETE 触发器中很常见，因为这些语句经常作用于多行。而这在 INSERT 触发器中就比较少见，因为基本的 INSERT 语句只添加一行。然而，由于 INSERT 触发器可由 INSERT INTO (table_name) SELECT 语句激发，所以，插入许多行可能导致单个的触发器调用。上面讨论的涉及MXB的3类触发器都是针对影响单行的语句。因此，有必要考虑影响多行的语句，这里对MXB的INSERT触发器进行讨论。
　　(1)可处理多行的MXB上的INSERT触发器
　　如果要进行多行插入，上面示例中的触发器可能就不能正确处理，因为 UPDATE 语句赋值表达式右边的表达式只能是一个值，而不能是一个值列表。因此，该触发器的作用就是获取 inserted 表中任意一行的值，并将其添加到JCB表中特定教材代码值的已有库存量值上。如果某个教材代码值在inserted 表中出现了多次，则可能无法得到预期的结果。为了正确地更新JCB表，触发器就必须适应inserted表中出现多行的可能性。这可以通过 SUM 函数实现，它为 inserted 表中每个教材代码计算教材进出的总计。SUM 函数存放于相关子查询中（SELECT 语句在括号内）。该子查询为 inserted 表中与JCB表的教材代码匹配或相关的每个教材代码返回一个单一值。
CREATE TRIGGER MXB－INSE ON ［dbo］.［MXB］
FOR INSERT
AS
UPDATE JCB SET 库存量=库存量+
(SELECT SUM(教材进-教材出) FROM INSERTED
WHERE JCB.教材代码=INSERTED.教材代码)
WHERE JCB.教材代码 IN
(SELECT 教材代码 FROM INSERTED)
　　该触发器对单行插入同样适用，不过，使用该触发器时，WHERE 子句中所使用的相关子查询和 IN 运算符需要额外处理，而这对于单行插入来说是不必要的。
　　(2)可区分单行和多行插入的MXB上的INSERT触发器?可以通过系统函数@@ROWCOUNT以区分单行插入和多行插入，以使触发器针对不同行数使用最优方法。
CREATE TRIGGER MXB_INSE ON ［dbo］.［MXB］
FOR INSERT
AS
IF @@ROWCOUNT=1
BEGIN
UPDATE JCB SET 库存量=库存量+
(SELECT 教材进-教材出 FROM INSERTED)
FROM JCB,INSERTED
WHERE JCB.教材代码=INSERTED.教材代码
END
ELSE
BEGIN
UPDATE JCB SET 库存量=库存量+
(SELECT SUM(教材进-教材出) FROM INSERTED
WHERE JCB.教材代码=INSERTED.教材代码)
WHERE JCB.教材代码 IN
(SELECT 教材代码 FROM INSERTED)
END
　　二、结论
　　触发器应用于支持企业级商业解决方案时，是一个功能十分强大的工具。它可以用于实现业务规则，可以检查事务，可以在同一表上创建多个触发器来分离代码的功能。另外，通过使用触发器收集的信息，可以提高数据库的性能，可以用来维护那些使用外键所不能实现的复杂参数完整性。因此，对于维护数据表之间一致性，保持数据的相关完整性的情况，触发器应作为首先考虑的技术。