触发器在数据库设计中的应用

陈舒心



触发器在数据库设计中的应用

陈舒心

触发器是一种特殊的存储过程。它的执行不是通过显性的调用，而是通过对相应表格数据实现插入、更新或删除时自动激活执行。在程序开发过程中，可以通过触发器对程序后台数据实施复杂的完整性约束。本文以本校示范校建设专题网站管理后台设计为例，阐述触发器的概念及其在网站功能实现中的定义及应用。

触发器；SQLSERVER；数据库

一、引言

随着网络技术的发展，网站浏览成为我们生活中不可或缺的一部分。一个制作精良的网站，除了要有精美的前台页面，必然也离不开后台数据的处理。在复杂的数据处理过程中，保证网站数据的完整性尤为重要。数据完整性是指存储在数据库中的数据正确无误并且相关数据具有一致性。通常在数据库设计阶段，可以通过创建表格的约束来实现数据完整性。譬如：通过主键约束或唯一键约束可以规范表格中某一字段的值不可重复；通过检查约束可以规范表格中某一字段的取值范围。但这些约束对不符合条件的数据只能做出单一拒绝执行的动作。如果需要实现较为复杂反馈或操作时，这些约束还是存在一定的局限性的。如果要求处理数据过程中，系统能自动根据用户对表格的操作做出不同的响应执行某些操作，那么，就可以使用触发器去实现。

二、触发器

触发器是一种特殊的存储过程，但它不像存储过程能直接通过函数名被调用。当受触发器保护的数据受到某些操作影响时，触发器会被数据库系统激活执行。因此，从激活操作的角度，触发器可分以下三种类型：插入触发器、更新触发器和删除触发器。无论是哪种操作，由于触发执行的时机不同，SQLServer提供了两种前缀类型不同的触发器定义：After触发器和Instead of触发器。

After触发器要求只执行插入、更新、删除中任一操作之后激活触发器执行。而Instead of触发器的激活时机刚好相反，它是在执行插入、更新、删除中任一操作之前就被激活。因此，在执行Instead of触发器时，系统所做的相关操作都是未被执行的。在SQLServer中，After触发器是默认触发器。

在每一个触发器被激活时，数据库系统都会自动创建两张数据表：Inserted表和Delete表。它们只保存在内存中，并不真实存在于数据库表对象中。两张表的结构与触发器作用的表格的结构相同。Instead表中存放的是系统执行INSERT 和UPDATE 语句时被影响的数据行的副本。在一个插入或更新事务处理中, 新添加的行会同时被添加到Inserted 表和触发器作用的数据表中。Deleted 表用于存储系统执行DELETE 和UPDATE 语句时被影响的数据行的副本。在执行DELETE或UPDATE 语句时，相关数据行从数据表中删除，同时被传送到Deleted 表中。Deleted 表和触发器作用的数据表一般不会出现内容完全相同的数据行。当触发器执行成功，数据操作语句执行完成后，整个事务过程才算完成。否则，这个事务要回滚。因此，触发器定义的条件不能与表格中已经定义的完整性约束相矛盾。当触发器完成后，Inserted表和Delete表会自动删除。

三、应用实例——基于T-SQL语言的触发器的设计

本校在示范校建设过程中，需要搭建示范校建设专题网站用以发布示范校项目建设过程的动态信息、通知公告及工作简报。同时展示项目建设的方案、资源、成果等。网站后台的主体功能是进行新闻信息与用户信息的管理。数据后台分别设计了数据表Admin(用户信息表)、News_operation(新闻操作日志)、News(新闻发布信息表)、News_Class(新闻类别表)、News_Picture(新闻图片信息表)等表格，用以存储网站上相关数据。

由于文章篇幅问题，不能将整个网站数据后台所做设计一一阐述，因此，在下文中以其中的三张有关联的数据表Admin、News、News_operation为例,阐述选择触发器实现完整性约束的原因及设计过程。具体表格结构如表1、表2、表3所示。

表1 Admin表结构

表2 News表结构

表3 News_operation表结构

三张数据表间的关联如下：

1.Admin表与News表

在网站上的新闻公告，必须是通过注册账号登录成功后才能发布，发布者的名称要与登录的账号一致。因此，对于Admin表的UserName字段的值和News表的Author字段的值必须一致，这种一致性可以通过定义两张表格UserName字段和Author字段主外键约束实现。其中，UserName字段为主键，Admin表为主键表；Author字段为外键，News表为外键表。主外键约束条件下，通常可以通过定义相关数据级联更新和级联删除来达到数据完整性。所谓级联更新和删除，就是主键表中的数据被执行更新或者删除操作时，与其相关的外键表中的对应数据也自动进行更新或者删除。显然，在本站中设置Admin表的UserName字段和News表的Author字段级联更新时合适的。但是如果设置级联删除，则会导致某些新闻公告在发布用户注销后也被删除，造成信息的丢失。因此，在主键表Admin表执行数据删除时为保证外键表News表中对应数据不丢失的前提下，又可以达到数据的一致性，笔者选择通过为Admin表设计一个删除触发器，在数据执行删除操作时系统激活，将News表中的Author字段设置为空值。

2.News表与News_operation表

考虑到每个注册用户登录网站后，都可以对数据实行增删改的操作。为保障数据安全，网站需要跟踪记录执行的操作的用户、操作类型及执行操作的时间。因此，当用户对News表数据执行操作时，相关执行的信息将会记录至News_operation表中。通过在表格中设置单纯的完整性约束，很难实现此功能。当然也可以考虑借助开发工具，编写前台代码实现。但若通过前台编码实现此功能，不仅会增加程序员的工作量，而且每次在执行到编码时都必须经过编译，将指令从前台通过网络传输到后台，才能达到目的。在这个过程中必然会耗费一定是时间和网络带宽。若果通过触发器去实现，由于触发器是直接存储在数据后台，可以在后台数据设计阶段就设计定义好，因此在此设计触发器去实现此需求更为合适。

以Admin、News、News_operation为基本表，需要满足以下要求：①Admin表中用户信息删除时，该用户发布的所有新闻公告的发布人Author字段置为空值(Null)；②用户对News表数据进行删除或者修改或者添加时，将用户对表格操作记录到Admin_log表中。触发器实现如下：

1.发布人Author字段置空

create trigger News_delete

on Adimin for delete

as

update News

set Author=Null

FROM News,Deleted

whereNews.ID=Delete.ID

2.记录用户对新闻表的操作

create trigger News_operation

on News for insert,update,delete

as

if not exists(select1 from inserted) and exists(select 1 from deleted)

begin /*inserted表无记录，是删除操作*/

DECLARE @author NVARCHAR(50)

select @author=f_editer from deleted

insert into admin_log values(@author,'删除新闻',getdate())

end

else if not exists(select 1 from deleted) and exists(select1from inserted)begin /*是数据添加操作*/

DECLARE @author1 NVARCHAR(50)

select @author1=f_editer from inserted

insert into admin_log values(@author1,'添加新闻',getdate())

end

else if exists(select 1 from deleted) and exists(select 1 from inserted)

begin/*是数据更新操作*/

DECLARE @author2 NVARCHAR(50)

select @author2=f_editer from deleted

insert into admin_log values(@author2,'更新新闻',getdate())

end

以上触发器的功能均已通过系统测试。

四、结束语

在系统开发过程中，触发器设计可以保障后台数据的完整性。当然，上述的功能也可以借助开发工具的前台代码实现，但是触发器所带来的系统开销会更加小。触发器是存储在本地服务器上的，在执行时可以节省网络传输过程中消耗的时间。而且，触发器执行过程中如果出现任何情况的错误导致操作失败，之前所做的一切都会恢复，因此它能最大程度的保证数据的完整性。

由此可见，合理地设计触发器，既可以使数据更安全，又可以减少程序开发人员的劳动量。若能在程序开发中熟练地将触发器和其他数据对象有机集合，将能大大提高程序执行的效率。

[1]张峰，张莉莉.触发器在数据处理过程中的应用研究[J].计算机工程与科学，2008(5)：156-158.

[2]胡鹤年.SQL Server触发器在数据库设计中的应用[J].数据库与信息管理，2012(8)：37-38.

[3]刘春蕾.触发器在数据库编程中的应用[J].电脑开发与应用，2014(4)：56-58.

[4]彭娇.触发器在Oracle数据库中的应用研究[J].软件导刊，2015(6):151-152.

责任编辑 何丽华

2016-03-10

陈舒心(1978-)，广东省轻工职业技术学校讲师，硕士。研究方向：现代教育技术。(广东 广州/510300)

G712

A

1005-1422(2016)04-0110-03