SQL SERVER触发器实现

基于SQL SERVER触发器技术的实现   出处：http://www.jstvu.edu.cn/xuebao/2002-3/pages/jiaoyujishu/sqlserver.htm

收稿日期：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 　　二、结论 　　触发器应用于支持企业级商业解决方案时，是一个功能十分强大的工具。它可以用于实现业务规则，可以检查事务，可以在同一表上创建多个触发器来分离代码的功能。另外，通过使用触发器收集的信息，可以提高数据库的性能，可以用来维护那些使用外键所不能实现的复杂参数完整性。因此，对于维护数据表之间一致性，保持数据的相关完整性的情况，触发器应作为首先考虑的技术。 参考文献： ［1］袁莉，张其海译. SQL SERVER7.0开发宝典［Ｍ］. 北京：人民邮电出版社，1999.198-230. ［2］罗晓沛. 数据库技术（中级）［Ｍ］. 北京：清华大学出版社，1999.78-100. The Implementation of Trigger Technique Based on SQL SERVER  SHEN Chenming (Nanjing Institute of Technology,Nanjing 210013,Jiangsu,China) Abstract: The article explains the principle of trigger and shows how to guarantee the data integrity in SQL SERVER .In addition, a settlement plan on inserting single-or multi-line data is also proposed. Keyword：data integrity; trigger；stored procedure 