SQL Server触发器在保持数据库完整性中的应用

前言:此论文为我已经发表的一篇论文,说的好听点所谓"学术论文",何呵....发表在<电脑开发与应用>2007年第三期上,其实也没有什么特别的地方,只是一个触发器的小小应用而已,如果对大家有用,我将会感到非常的高兴.这篇论文也是当时在首次尝试自己用asp.net做一个论坛时用到的一些东西,当然这个技巧不算是我的独创了,只是我在应用中利用它解决了我的实际问题而已..呵呵..大家将就着看一下吧

 

[ 摘要 ] 触发器在保持数据库的完整性与一致性方面起着非常重要的作用，它是一种保证数据库中数据完整性的方法。本文讲述了DML 触发器的类型、工作机理及触发机制。以BBS论坛数据库为例，介绍了触发器在保持数据库完整性中的具体应用。

[ 关键词 ] 触发器 数据库 SQL Server 数据完整性

中图分类号：TP13                           文献标识码：A

The application of SQL Server Trigger in maintaining the integrity of database

 Hui Zhao Lengfei Lu

(Modem Education Technology Center, Central South University, ChangShang City, 410083)

Abstract: The trigger plays a vital role in maintaining the integrity and consistency of database, it is one kind of methods which guarantee the data integrity of database. This paper narrates the DML trigger work mechanism and types and how the triggers work. Take the BBS forum database for example, the writer introduced the application of trigger in maintaining the integrity of database

Keywords:  Trigger Database SQL Server Data Integrity

 

1.         触发器概述

触发器是SQL Server数据库应用中一个重要工具，是一种特殊类型的存储过程，应用非常广泛。一般存储过程主要通过存储过程名而被直接调用，触发器则是通过事件触发执行。触发器基于一个表来创建并和一个或多个数据修改操作（插入、更新或删除）相关联，可视作表的一部分。触发器与数据库中的表紧密相关，比如当对表执行INSERT、UPDATE或DELETE操作时，触发器就会自动执行。

SQL Server 包括两大类触发器：DML 触发器和 DDL 触发器。其中DDL 触发器是 SQL Server 2005 的新增功能，当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用该触发器；DML 触发器是指当数据库中发生数据操作语言 (DML) 事件时被调用。DML 事件包括在指定表或视图中修改数据的 INSERT、UPDATE或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。DML 触发器用于在数据被修改时强制执行业务规则，以及扩展 Microsoft SQL Server 2005 约束、默认值和规则的完整性检查逻辑。本文中所讲述的触发器主要是指DML 触发器。

DML触发器包括三种类型：AFTER 触发器、INSTEAD OF 触发器、CLR 触发器。在执行了 INSERT、UPDATE 或 DELETE 语句操作之后将会执行 AFTER 触发器，本文将以AFTER触发器为例来讲述触发器在保持数据完整性中的应用。其创建语法如下：

CREATE   TRIGGER  trigger_name
ON  table_name
AFTER { [ INSERT ][ , ][ UPDATE ][ , ][ DELETE ] }}
AS
    Sql_statements
[ RETURN ]

  

在触发器的应用中，我们通常会用到两个特殊的表：inserted表和deleted表。它们都是针对当前触发器的局部表。这两个表与触发器所在表的结构完全相同，而且总是存储在高速缓存中。当触发DELETE触发器后，从受影响的表中删除的行的副本将被放置到deleted表中。同理当触发INSERT触发器后，inserted表中保存的是刚被插入的数据行的一个副本。

当一个触发器执行激发另一个触发器的操作，而另一个触发器又激发第三个触发器，如此等等，这时就发生了触发器的嵌套。也就是下文中用到的嵌套触发器。DML 触发器和 DDL 触发器最多可以嵌套 32 层。

 

2. 1.   触发器的应用

我们以BBS论坛数据库中多个关联表的操作为例，阐述触发器在保持数据完整性、一致性中的应用。

在BBS的程序设计中，我们经常会碰到对一个数据表操作的同时，还要自动对另外几个相关联的数据表进行操作，以保证各数据表之间数据的完整性与一致性。BBS论坛中常用的数据表有：

BBS_User表（存储用户信息）：用于存储用户信息。字段有用户名、密码、积分、发帖数、等级ID、最后一次发帖、qq、Email、头像、注册时间等；

BBS_Type表：用于存储大版块信息。字段有版块ID、版块名称等；

BBS_LanMu表：存储分论坛信息。字段有分论坛ID、名称、所属大版块ID、主题总数、回复总数、版主等；

BBS_Topic表：存储帖子信息。字段有帖子ID、标题、内容、发帖人、所属分论坛ID、回复总数、点击总数、最后一次回贴时间、回帖人等；

BBS_Reply表：存储回复信息。字段有回复内容、回复人、回复的帖子ID、回复时间等。

在BBS论坛中，触发器主要应用于以下几种情况：

当用户在分论坛里发表帖子时，对BBS_Topic表进行操作，但同时要自动对分论坛表BBS_LanMu里面的论坛主题总数增1，还要更新BBS_User表给该用户增加相应的积分，当用户积分达到一定分数时，自动更新该用户的等级ID，表示该用户已经升了一个等级。

当用户回复帖子时，对BBS_Reply表操作的同时，也需要对分论坛表BBS_LanMu里的回复总数增1、对BBS_Topic表的回复总数增1并更新该表里的最后回帖标题和时间，还要将BBS_User表里的该用户的发帖数增1、自动增加相应积分、更新最后发帖标题和时间等；

在论坛的后台管理中，管理员有时需要添加或者删除一个大版块。当我们要删除BBS_Type表一个大版块时，为了保证数据库各表中数据的完整性与一致性，要同时对BBS_LanMu表、BBS_Topic表、BBS_Reply表中相关联的数据记录一并删除。

下面将以后台管理中对论坛大版块进行删除操作时应用触发器为例来进行具体介绍。

1)        需求分析

在一个BBS中常见的论坛结构如图Pic-1：
 
 

在一个论坛中有许多个大版块，每个版块又对应多个分论坛。每个论坛又对应多个帖子，每个帖子又对应多个回复信息。因此需要4个相关联的表来存储相应的信息：BBS_Type表（存储大版块信息）、BBS_LanMu表（存储分论坛信息）、BBS_Topic表（存储帖子信息）、BBS_Reply表（存储回复信息）。BBS_Type与BBS_LanMu、BBS_LanMu与BBS_Topic、BBS_Topic与BBS_Reply之间都是一对多的关系。

当我们要删除BBS_Type表一个大版块时，为了保证数据库各表中数据的完整性与一致性，需要同时对BBS_LanMu表、BBS_Topic表、BBS_Reply表中相关联的数据记录一并删除。因为这里面存在3对一对多的关系，如果在程序中或者存储过程实现，显然是很困难也是不合理的。根据触发器的作用以及这4个表之间的关系，采用嵌套触发器来实现这个删除功能。为BBS_Type表、BBS_LanMu表、BBS_Topic表分别建立一个AFTER触发器，该触发器由DELETE事件触发。采用嵌套触发器可以在数据库里自动完成这多个表中相关记录的删除，大大简化了业务逻辑。这样即保证了数据的完整性与一致性，又保证程序设计的合理性与方便性。

2) 1)  创建触发器

根据需求分析，为BBS_Type表、BBS_LanMu表、BBS_Topic表分别建立AFTER触发器，该触发器由DELETE事件触发。建立在这3个表之上的触发器之间是嵌套触发的关系，即BBS_Type表上的触发器触发BBS_LanMu表上的触发器，BBS_LanMu表上的触发器再触发BBS_Topic表上的触发器。本文中使用的数据库为Microsoft SQL Server 2005。要让触发器能嵌套触发必须在数据库“属性”中，将“递归触发器已启用”设置为TRUE。

⑴ 为BBS_Type表（存储大版块信息）建立触发器DelType。该触发器功能是删除BBS_LanMu表中属于刚删除的大版块的所有分论坛信息。

CREATE   trigger   [ DelType ]   on   [ dbo ] . [ BBS_Type ]
after  delete
as
begin
declare   @typeid   int
select   @typeid = TypeID  from  deleted    -- 获得要被删除的版块ID
delete   from  dbo.BBS_LanMu  where  Typeid = @typeid  
end

 

⑵ 为BBS_LanMu表（存储分论坛信息）建立触发器DelLanmu。该触发器功能是删除BBS_Topic表中属于刚删除分论坛的所有帖子信息。

CREATE   trigger   [ DelLanmu ]   on   [ dbo ] . [ BBS_LanMu ]
AFTER  DELETE
as
BEGIN
     declare   @lmid   int
     select   @lmid = LMID  from  deleted   -- 获得要被删除的分论坛ID
     delete   from  dbo.BBS_Topic  where  LMID = @lmid
END

 

⑶ 为BBS_Topic表（存储帖子信息）建立触发器DelTopic。该触发器功能是删除BBS_Reply表中属于刚删除帖子的所有回复信息。

CREATE   trigger   [ DelTopic ]   on   [ dbo ] . [ BBS_Topic ]
after  delete
as
BEGIN
declare   @tid   int
select   @tid = TID  from  deleted   -- 获得要被删除的帖子ID
delete   from  dbo.BBS_Reply  where  TID = @tid
END

 

1)        执行过程

触发器执行过程如图Pic-2。

 

当数据操作层对数据表BBS_Type发出DELETE一条记录的时候，触发器DelType被触发，此触发器将删除BBS_LanMu表中属于刚删除的大版块的所有分论坛信息。

当DelType触发器对数据表BBS_LanMu删除一条记录时，又触发BBS_LanMu表上的触发器DelLanmu，此触发器将删除BBS_Topic表中属于刚删除分论坛的所有帖子信息。

当DelLanmu触发器对数据表BBS_Topic删除一条记录时，又触发触发器DelTopic，此触发器将删除BBS_Reply表中属于刚删除帖子的所有回复信息。

至此数据库中与BBS_Type中删除记录相关联的所有记录全部删除，保证了数据库各表数据的完整性与一致性。这个过程是在数据库中自动进行的，因此速度非常快，用户只需要对BBS_Type表发出删除一条记录的命令，其他表中相关的记录会自动删除。

 

3. 1.   总结：

触发器能保持数据的完整性与一致性，它可以方便地基于一个表的修改，自动更新其他相关表的记录，以保证数据的完整性。在数据库的应用中，触发器扮演着很重要的角色。无论是作为提供高级参照完整性功能的途径，还是执行自动维护非正规化数据的任务，触发器都能帮助用户实现满足实际需要的规则，简化业务逻辑，并使系统更方便更有效。

 

参考文献：

[1] Dongwon Lee. Designing Triggers with Trigger-By-Example[J]. Knowledge and Information Systems，Jan. 2005：10-11

[2] 徐国华，白凤娥.数据库中的触发器技术[J].太原大学学报，2006，3：64-65

[3] 黄景文，周舟.SQL Server触发器的应用[J].电脑开发与应用，2005，11：4-5.

[4] 张益星.SQL Server 2000触发器技术及应用[J].信息技术，2005，10：69-70.

附：