触发器与数据库完整性控制.doc

触发器与数据库完整性控制【摘要】触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。触发器类似于约束，但是比约束更加灵活，可以实施比FOREIGN KEY约束、CHECK约束更为复杂的检查和操作，具有更精细和更强大的数据控制能力。【关键词】SQL触发器，数据库完整性一、绪论数据库的完整性是为了保证数据库中存储的数据是正确的。所谓正确是指符合现实世界的语义。实体完整性总应在最低级别上通过索引进行强制，这些索引或是 PRIMARY KEY 和 UNIQUE 约束的一部分，或是在约束之外独立创建的。假设功能可以满足应用程序的功能需求，域完整性应通过 CHECK 约束进行强制，引用完整性 (RI) 则应通过 FOREIGN KEY 约束进行强制。而当约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。例如：CHECK 约束只能根据逻辑表达式或同一表中的另一列来验证列值。如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器；约束只能通过标准的系统错误信息传递错误信息。如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用触发器；触发器也可以评估数据修改前后的表状态，并根据其差异采取对策；一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。二、SQL触发器语法1定义触发器CREATE TRIGGER （创建表的用户才可以创建触发器，名称唯一且与在同一模式下）BEFORE|AFTER ON （当这个表的数据发生变化时激活触发事件）FOR EACH ROW|STATEMENT（即定义行级触发器或语句级触发器）WHEN （触发器激活时，只有当触发条件为真时触发动作体才执行）（既可以是一个匿名PL/SQL过程块，也可以是对已创建的存储过程的调用）2.删除触发器DROP TRIGGER ON (触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除)三、SQL触发器在保持数据库完整性中的实际应用在触发器的应用中，我们通常会用到两个特殊的表：inserted表和deleted表。它们都是针对当前触发器的局部表。这两个表与触发器所在表的结构完全相同，而且总是存储在高速缓存中。当触发DELETE触发器后，从受影响的表中删除的行的副本将被放置到deleted表中。同理当触发INSERT触发器后，inserted表中保存的是刚被插入的数据行的一个副本。当一个触发器执行激发另一个触发器的操作，而另一个触发器又激发第三个触发器，如此等等，这时就发生了触发器的嵌套。也就是下文中用到的嵌套触发器。DML 触发器和 DDL 触发器最多可以嵌套 32 层。下面以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中常见的论坛结构如图：在一个论坛中有许多个大版块，每个版块又对应多个分论坛。每个论坛又对应多个帖子，每个帖子又对应多个回复信息。因此需要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)创建触发器根据需求分析，为BBS_Type表、BBS_LanMu表、BBS_Topic表分别建立AFTER触发器，该触发器由DELETE事件触发。建立在这3个表之上的触发器之间是嵌套触发的关系，即BBS_Type表上的触发器触发BBS_LanMu表上的触发器，BBS_LanMu表上的触发器再触发BBS_Topic表上的触发器。本文中使用的数据库为Microsoft SQL Server 2005。要让触发器能嵌套触发必须在数据库“属性”中，将“递归触发器已启用”设置为TRUE。1.为BBS_Type表（存储大版块信息）建立触发器DelType。该触发器功能是删除BBS_LanMu表中属于刚删除的大版块的所有分论坛信息。CREATE trigger DelType on dbo.BBS_Typeafter deleteasbegindeclare typeid intselect typeid=TypeID from deleted -获得要被删除的版块IDdelete from dbo.BBS_LanMu where Typeid=typeidend2.为BBS_LanMu表（存储分论坛信息）建立触发器DelLanmu。该触发器功能是删除BBS_Topic表中属于刚删除分论坛的所有帖子信息。CREATE trigger DelLanmu on dbo.BBS_LanMuAFTER DELETEasBEGINdeclare lmid intselect lmid=LMID from deleted -获得要被删除的分论坛IDdelete from dbo.BBS_Topic where LMID=lmidEND3.为BBS_Topic表（存储帖子信息）建立触发器DelTopic。该触发器功能是删除BBS_Reply表中属于刚删除帖子的所有回复信息。CREATE trigger DelTopic on dbo.BBS_Topicafter deleteasBEGINdeclare tid intselect tid=TID from deleted -获得要被删除的帖子IDdelete from dbo.BBS_Reply where TID=tidEND执行过程触发器执行过程如图：当数据操作层对数据表BBS_Type发出DELETE一条记录的时候，触发器DelType被触发，此触发器将删除BBS_LanMu表中属于刚删除的大版块的所有分论坛信息。当DelType触发器对数据表BBS_LanMu删除一条记录时，又触发BBS_LanMu表上的触发器DelLanmu，此触发器将删除BBS_Topic表中属于刚删除分论坛的所有帖子信息。当DelLanmu触发器对数据表BBS_Topic删除一条记录时，又触发触发器DelTopic，此触发器将删除BBS_Reply表中属于刚删除帖子的所有回复信息。至此数据库中与BBS_Type中删除记录相关联的所有记录全部删除，保证了数据库各表数据的完整性与一致性。这个过程是在数据库中自动进行的，因此速度非常快，用户只需要对BBS_Type表发出删除一条记录的命令，其他表中相关的记录会自动删除。四、结束语触发器的功能非常强，不仅局限于数据库的完整性检查，也可以用来实现数据库系统的其他功能，包括数据库安全，以及更加广泛的应用系统的一些业务流程和控制流程，基于规则的数据和业务控制功能。参考文献：1. 王珊 萨师煊 数据库系统概论（第四版），20062. Hammer M，McLeod D. Semantic