APP下载

SQLServer触发器应用探析

2016-07-22李虎军

电脑知识与技术 2016年16期
关键词:触发器完整性

李虎军

摘要:该文介绍了触发器类型、特点以及触发器产生的两个临时表,并着重对触发器提高数据完整性的应用及其注意事项进行了阐述,以期对数据库设计者在设计数据库系统时提高数据完整性控制提供有益帮助。

关键词:完整性;触发器;CHECK约束

中图分类号:TP311 文献标识码:A 文章编号:1009-3044(2016)16-0013-02

信息系统开发与运行离不开数据库系统的支持,而对于数据库而言,其存储的数据的正确性至关重要。为确保数据的正确性,各种DBMS都提供了各种方法或机制进行检测、检查。灵活、合理地应用触发器对提高信息系统的数据正确性有很好的帮助。

1 SQL Server触发器

SQL Server触发器是一类特殊存储过程,其由用户定义在数据表上并被事件驱动的由DBMS调用执行的。触发器不能被用户直接调用执行,也不能接收参数。在数据表上或在数据库对象上定义了相应的触发器后,当在数据表中DELETE、INSERT、UPDATE操作或在CREATE、DROP、ALTER数据库对象时将激发并自动执行相应的触发器,使得相应的操作必须满足由触发器所定义的约束规则才能得到成功执行。引起触发器被执行的相应操作和触发器被统一当作一次事务处理。当事务未能整体成功执行,则DBMS会自动回到该事务执行前的状态。触发器可用于各种数据规则、约束和默认值的完整性检查,也可完成更复杂的数据检测和业务逻辑规则。

1.1 触发器分类

按照触发事件的不同,SQL Server触发器可以分为DDL(Data Definition Language)触发器和DML(Data Manipulation Language)触发器两种。

1)DDL触发器用于处理相应的DDL事件。DDL事件有数据库系统用户登录与退出和CREATE、DROP、ALTER语句等操作。DDL触发器可用于审核与规范数据库中对表结构、视图结构上的操作,还可被用于应用系统管理任务。当数据库结构或表结构发生变化时激发触发器,触发器能记录相应的修改过程,并能禁止用户删除指定表、限制用户对数据库修改等操作。

2)DML触发器用于响应用户DML操作事件。DML操作事件是指针对表或视图的INSERT、DELETE和UPDATE操作语句。该类触发器在DML编辑数据时触发执行,在处理表或视图中数据时DML触发器检查操作是否满足相应业务规则,使得数据完整性进一步扩展。

根据触发器是在触发操作语句前还是后执行的不同,触发器又分为INSTEAD OF触发器和AFTER(FOR)触发器两种。

1)INSTEAD OF触发器。顾名思义,该种触发器在数据操作之前执行,代替操作数据的相关语句,实际上是执行触发器中的代码。该类触发器定义在视图或表上。在视图或表上,每个DELETE、UPDATE和INSERT语句只可定义一个INSTEAD OF触发器。

2)AFTER(FOR)触发器。在DML语句操作数据完成以后被触发执行。此种触发器可对被操作的数据进行业务规则检查,如果违反相应的业务规则,则返回该数据操作前的状态。只能在表上而不能在视图上定义AFTER(FOR)触发器,在同一张数据表上可定义多个AFTER(FOR)触发器。

1.2 DBMS生成的两个临时表

在执行DML触发器时,SQL SERVER生成两个只读型临时表:deleted表和inserted表。在触发器执行时这两个表才存在于内存,其表结构与激发DML触发器的视图或基表类似。

1)deleted表

deleted表仅存放进行本次UPDATE和DELETE语句操作的基表的旧数据行。执行UPDATE或DELETE语句时,该数据行从基表中移动到deleted表并从基表中删除。

2)inserted表

inserted表仅存放进行本次UPDATE和INSERT语句操作的新数据行。执行UPDATE语句或INSERT语句时,新数据行被插入基表的同时也被复制到inserted表中。

实际上,UPDATE操作可看做由DELETE操作和INSERT操作组合而成的。UPDATE操作时,DBMS将要修改的数据行从基表中删除并移到deleted表中,然后将修改后的新数据行同时插入到inserted表和基表中。

2 SQL Server触发器应用

触发器机制类似Windows系统的消息响应机制,我们可以灵活利用这一点,不仅可以在数据表上实施比CHECK约束、外键约束等更为复杂的检查和操作,可以引用数据库中其他表中的字段,具有更精细和更强大的数据控制能力,还可以利用DDL触发器对数据库应用系统实施各种应用控制,提高数据库应用系统的安全性。

2.1实现CHECK约束检查功能

在DML触发器中可以灵活、编写代码处理相应的业务逻辑,因此DML触发器可以设置比CHECK约束更复杂、更强大的相应数据检查规则。例如医院所用某种药品已经停止采购,但此药品还需在药品数据表中存在,此时需要阻止门诊医生开列此药,数据库管理人员就可以在药品数据表上建立相应的触发器,来禁止医师修改已停止采购药品的数据,此任务无法使用CHECK约束来完成。当数据库系统设计者想控制错误信息或者给用户/客户应用程序传递错误信息时,无法使用CHECK约束实现,而触发器就能很好地完成任务。当需要发出更详细的错误信息以提示用户时,数据库系统设计者可以在触发器中使用RAISERROR()函数来达到目的。当在业务逻辑需要使用另一张表的数据时,也可以使用触发器来实现。触发器与CHECK约束各有优势。CHECK约束执行快且效率高,但只能引用同一表中的数据,而触发器却可以引用不同表中的数据。触发器可以完全实现CHECK约束的所有功能,但在效率上并不总是比CHECK约束好。触发器是维护引用完整性需要的一种最好选择,虽然它们的速度有点慢,但在维护数据完整性上更灵活。何时选择使用触发器与CHECK约束,需要根据实际情况定,首选CHECK约束,当CHECK约束无能为力时就用触发器。

2.2 数据库设计与批量数据导入

数据库设计时,要在表上设计各种约束以及要设计表与表之间的引用关系等等。在数据库系统调试中难免会对表结构进行改变完善,此时在原表上所建立的各种完整性约束都需要删除,在更改表结构建立表后,再重新建立各种约束,上述工作较难保证没有差错或遗漏,这就给数据库设计者带来较大不便。可以使用触发器来建立相关约束规则,在表结构发生更改时只需禁用触发器即可,随后也可方便地恢复触发器的使用,方便数据库设计和调试。在数据库系统开发完毕后再将某些触发器改为DRI(声明引用完整性)。当需要大批量导入数据时,往往需要关闭各种约束来方便导入数据;当使用触发器实现数据约束时,就能体现出触发器的优点。只需关闭触发器就可减少导入时的大量开销以实现批量数据的快速导入。

2.3 UPDATE()函数和UPDATE_COLUMNS()函数

在UPDATE触发器中,经常通过查看感兴趣的列是否通过发生改变来限制触发器中实际执行的代码数量,要完成这个任务,可以利用UPDATE()函数和UPDATE_COLUMNS()函数。

1)UPDATE()函数

该函数用于测试在指定列上进行的INSERT或UPDATE操作,不能用于DELETE操作,可以指定多列。只适用于触发器范围,其核心目的是对指定列进行是否被更新检测。

UPDATE(FIELD_NAME)值为true,则意味着FIELD_NAME列被更新。

2.4 加强数据库用户管理

可以针对 CREATE、ALTER 和 DROP 开头的语句,建立DDL触发器。为防止对数据库架构进行某些更改时,当希望数据库中发生某种情况以响应数据库架构中的更改时,当需要记录数据库架构中的更改或事件时,使用DDL触发器进行管理。可以对密码表中指定列设定AFTER(FOR)DML触发器,当表中用户名或密码值发生变化时,将操作者、操作时间、操作对象、前后变化的数据等内容进行保存并向数据库管理员发通知,为及时发现不合法操作、加强系统审核、保障数据库的安全提供帮助。

3 SQL Server触发器应用注意事项

3.1 触发器执行是被动的而不是主动的

这里主要指AFTER触发器发生在事件发生之后,在触发器被激活时,整个触发语句已经执行而且事务已经记录在日志中了(但没有提交)。这意味着,如果触发器需要rollback,就不得不撤销已经完成的大量工作,因此促发其对系统性能的影响程度取决于触发语句工作量的大小。而约束是主动的,约束是在实际语句发生之前执行的,这意味着它们在所有主要工作完成前阻止失败的事情发生。也就意味着约束的运行速度要快一些。特别是触发语句越复杂、影响数据量越大,使用触发器所带来的效率影响也越大。

3.2 尽量保持触发器简洁

触发器是与所调用的语句所在事务的一部分。这意味着语句要到触发器结束时才完成。如果在触发器的语句体中书写了运行很长时间的代码,就意味着引起触发器触发的每段代码,运行时间都很长。因此这很可能使得应用程序执行效果较差。

3.3 尽量不要在触发器中回滚

做到这一点较为困难,因为回滚是在触发器中通常希望完成的主要任务。最典型的触发器——AFTER触发器——发生在大部分操作已经完成以后,这也意味着回滚开销很大,这也是为什么建议尽可能使用DRI的原因。如果在触发器中使用多条ROLLBACK TRAN语句,最好在执行激活触发器的语句之前预先找到错误。在错误发生前进行测试,不要等到回滚的时候。

4 结束语

灵活使用触发器可以帮助数据库系统设计者和维护者实现许多复杂的功能,能较好地提高信息系统的数据完整性。但要慎用触发器,如果滥用会造成数据库系统的维护困难。因此在数据库系统设计中,尽量使用DRI来保证数据完整性。合理的触发器设计既可以保护表中的数据,使数据表更加安全,又可以减少数据库设计者的劳动强度,具有事半功倍的作用。

参考文献:

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

[2] 李霞. SQL Server约束在维护数据完整性中的运用[J].晋城职业技术学院学报,2012(5).

猜你喜欢

触发器完整性
稠油热采水泥环完整性研究
石油化工企业设备完整性管理
主从JK触发器逻辑功能分析
莫断音动听 且惜意传情——论音乐作品“完整性欣赏”的意义
使用触发器,强化安全性
精子DNA完整性损伤的发生机制及诊断治疗
ETCS-3列车完整性功能的安全目标
桩身完整性检测中缺陷的综合判别
几种常见触发器工作方式的讨论
对触发器逻辑功能转换的分析