SQL Server触发器在教务管理系统中的应用研究

摘 要：触发器是保证数据完整性的重要机制之一，本文首先介绍了触发器的分类、功能实现原理，以及在SQL Server中的常见使用方法，并结合教务管理系统中的实例给出了DML触发器的创建方法。结果表明，触发器的合理应用可以较好地提高系统性能，维护数据库的完整性。

关键词：触发器；SQL Server；数据完整性；教务管理系统

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

1 引言（Introduction）

在SQL Server数据库中，有主键、检查、唯一、默认、外键等约束能保证数据的完整性，但它们也有一定的局限性，大多只能在某张数据表中起作用，不能跨越其他数据表发挥作用，而触发器正好就可以解决这样的问题。触发器是SQL Server数据库中一类重要的数据库对象，它与存储过程不同，不需要进行显式的调用，它因事件触发而自动执行无须人工干预，它可以完成比较复杂的完整性操作，如数据表的级联更新、删除等，从而确保数据的完整性。

2 触发器的简介（Introduction to the trigger）

2.1 触发器的分类

SQL Server的触发器通常分为两类，分别是DML触发器和DDL触发器。DML触发器主要当数据库中的数据发生增加、更新和删除时被触发，也即当数据库执行INSERT、UPDATE和DELETE语句时被触发。DDL触发器主要用于审核与规范数据库中的数据表、视图的数据结构，如当服务器或数据库中发生新增数据表或修改某列数据类型时被触发。而根据触发器的触发时机不同，触发器还可以分为AFTER触发器和INSTEAD OF触发器。AFTER触发器又称为后触发器，它是在引起触发器执行的语句成功完成后执行的。而INSTEAD OF触发器又称为替代触发器，它代替引起触发器执行的语句而执行。一个表或视图的每个修改动作（INSERT、UPDATE和DELETE）都可以有一个INSTEAD OF触发器，但可以有多个AFTER触发器[1]。

2.2 触发器的功能

触发器的功能主要有：（1）跟踪数据的变化，当数据库中的数据发生变化时，禁止一些非法操作，从而保证数据的安全。（2）增强数据的参照完整性，可以监测到数据表中数据的变化，并自动地级联更新整个数据库中的各项内容，从而保证数据的一致性[2]。（3）当数据库出现安全问题时用于审计，如可以记录发生插入、修改和删除操作时的操作类型、操作时间及相关的用户等信息。（4）实现数据库定义本身所不能实现的较为复杂的商业规则，如对更新数据操作时间的限制、更新数据幅度的限制等。

3 触发器的工作原理（The working principle of the

trigger）

触发器是一种依赖于数据表而存在的数据库对象，当它被激活的时候，系统会自动的创建两张临时表：分别是INSERTED表和DELETED表。这两张表是逻辑表，并且这两张表是由系统管理的，存储在内存中，不存储在数据库中，因此不允许用户直接对其修改[3]。这两张表的结构和触发器所在的表结构完全相同，在触发器的代码被执行后由系统自动撤销。

系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到严重错误，则整个事务即自动回滚，恢复到原来的状态[4]。

在执行数据增加的操作时，相关联的INSERT触发器会被激活，系统会自动建立一张INSERTED表，要插入的数据会被临时存放在该表中；当执行数据删除操作时，相关联的DELETE触发器会被激活，系统则会自动生成一张DELETED表，删除的数据会临时存放在该表中；而当执行数据更新操作时，相当于先在数据表中删除了需要更新的数据，后又插入了需要替代的新的数据，因此系统会自动生成DELETED表和INSERTED表，需更新的原始数据被存入到DELETED表中，而替代的新的数据则被存入到了INSERTED表中。具体如表1所示。

4 触发器的常见操作（Common operation of the

trigger）

4.1 触发器的创建

创建触发器不仅需要指定触发器的名称，还需说明与之相关的数据表、触发器的触发类型、触发事件等信息，具体语法格式如下：

CREATE TRIGGER trigger_name

ON table/view

[WITH ENCRYPTION]

FOR /AFTER/INSTEAD OF[INSERT][，UPDATE][，DELETE]

AS

T-SQL语句

其中trigger_name表示触发器的名称，它必须遵循标识符规则，不能以#或##开头（以下出现不再赘述）。由于触发器可以应用在数据表或视图上，table/view此处表示对其执行的数据库对象的名称。需要注意的是，视图只能被INSTEAD OF触发器引用。WITH ENCRYPTION是可选项，用来对触发器的代码进行加密。FOR/AFTER指定 DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发，AFTER可以省略。INSTEAD OF设置触发器激活的时机为前触发，需要注意的是不能为DDL触发器指定INSTEAD OF。[INSERT][，UPDATE][，DELETE]表示激活触发器的DML语句的类型，允许使用上述选项的任意顺序组。

4.2 触发器的禁用和启用

触发器可以根据情况进行禁用和启用。在数据维护或初始化过程中，特别是在大批量数据库导入时，需要暂停触发器语句体的执行，等数据维护或初始化过程完成后，继续使触发器生效[5]。

禁用和启用触发器的语句格式如下：

ALTER TABLE table_name DISABLE/ ENABLE TRIGGER trigger_name/ALL

其中table_name表示禁用或启用的某个触发器所在的数据表的名称，ALL表示禁用数据表上的所有触发器。此外，还可以调用系统存储过程sp_msforeachtable来禁止或启用所有表上的所有触发器，具体格式如下所示：

exec sp_msforeachtable'ALTER TABLE ？ DISABLE/ENABLE TRIGGER ALL'

其中sp_msforeachtable是系统存储过程，存放在SQL Server的系统数据库master中。"？"的作用相当于DOS命令中，以及我们在Windows下搜索文件时的通配符的作用。

4.3 触发器的删除

当触发器不再需要时，可以使用DROP命令进行删除，其语法格式如下：

DROP TRIGGER trigger_name

5 触发器的应用（Application of the trigger）

在一个教务管理系统中的教学管理模块中有学生表、班级表、选课表和课程表等数据表，分别记录了学生、班级、选课和课程的具体信息，关系模式如下所示：

学生（学号，姓名，性别，出生日期，电子邮件，地址，班级编号）

班级（班级编号，班级名称，人数，专业编号）

选课（学号，课程编号，成绩，学年，学期）

课程（课程编号，课程名称，学分，课程性质）

5.1 处理新转入的学生

当学生信息增加到学生表中后，与之相关联的班级信息也会发生相应的变化，即班级人数会增加。如果每次都使用手工的方式去更改班级表中的信息，既麻烦也容易出错，不利于数据的一致性。因此可以设计一个INSERT触发器来解决这类问题，当系统发现学生表中增加记录时，自动的实现班级表中班级人数字段的更新。具体代码如下：

CREATE TRIGGER tri_stuInsert

ON student --创建在学生表上

FOR INSERT --触发事件是增加数据

AS

BEGIN

UPDATE class --根据INSERTED表中的班级编号更新班级表中的人数

SET num=num+1--每增加一条记录，班级人数增加1

WHERE Classno=（SELECT Classno FROM INSERTED）

END

5.2 使用级联删除处理学生退学

当学生办理退学时，需要在删除学生个人信息的同时，将其选修课程的信息删除。否则留在选课表中的学生成绩就会因找不到学生的相关信息，而造成整个数据库中数据的异常。这时可以设计一个DELETE触发器来解决这类问题，当系统发现学生表中删除的学生在选课表里有相关记录时，自动实现选课表中数据的删除。具体代码如下：

CREATE TRIGGER tri_stuDelete

ON student --创建在学生表上

FOR DELETE --触发事件是删除数据

AS

BEGIN

DELETE result --根据DELETED表中的学号删除选课表中的信息

WHERE Sno IN （SELECT Sno

FROM DELETED）

END

5.3 使用级联更新修改课程编号

如果课程编号发生变化时，与之相关联的选课表中的课程编号也需要同时更新，不然的话就会破坏数据的完整性。因此可以设计一个UPDATE触发器来解决这类问题，当系统发现课程表中的课程编号发生变化时，自动地对选课表中的课程编号进行更新。具体代码如下：

CREATE TRIGGER tri_courseUpdate

ON course--创建在选课表上

FOR UPDATE--触发事件是更新数据

AS

BEGIN

--分别定义局部变量存放原课程编号和新课程编号

DECLARE @oldcno CHAR（7），@newcno CHAR（7）

--从DELETED、INSERTED表中查询出原课程编号和新课程编号并存放在局部变量中

SELECT@oldcno=deleted.cno，@newcno

=inserted.cno

FROM DELETED，INSERTED

UPDATE Result --对选课表的课程编号进行更新

SET Cno=@newcno

WHERE Cno=@oldcno

END

6 结论（Conclusion）

通过实践证明，应用触发器不仅可以处理表与表之间复杂的逻辑关系，优化数据库的设计，更好地维护数据库中数据的完整性[6]，同时也提高了教务管理系统实现的运行效率。但是同时我们也必须清醒地认识到，如果过多的使用触发器不仅增加了数据库维护的成本，有时还将降低数据库的整体性能。因此，我们要在合适的时候恰当地使用触发器。

参考文献（References）

[1] 吴西燕.SQL触发器在旅行社管理信息系统中的应用[J].电脑

编程技巧与维护，2012（22）：50-51.

[2] 耿涛，黄磊，刘儒香.SQL Server2005触发器在图书管理系统中

的应用研究[J].西昌学院学报（自然科学版），2012，26（3）：69-71.

[3] 吴伶琳，杨正校.SQL Server数据库技术及应用[M].第二版.大

连：大连理工大学出版社，2014：170-172.

[4] 符策锐.触发器保持评教系统数据完整性的应用研究[J].微计

算机信息，2012，28（8）：173-175.

[5] 朱亚兴.Oracle数据库系统应用开发实用教程[M].北京：高等

教育出版社，2012：248-249.

[6] 褚龙现.DML触发器保持数据库完整性应用研究[J].计算机

与现代化，2013（4）：57-59.

作者简介：

吴伶琳（1977-），女，硕士，副教授，工程师.研究领域：数据

库技术与应用.