本文详细介绍了SQL Server中的触发器，包括触发器的概念、作用、分类以及如何创建DML和DDL触发器。触发器主要用于确保数据的完整性，如级联更改和复杂的数据验证。文章还展示了创建INSERT、UPDATE、DELETE触发器的示例，并讨论了替代触发器、嵌套触发器和递归触发器的概念。此外，还讲解了如何管理触发器，包括查看、修改、删除和启用/禁用触发器的步骤。
触发器
一、概述
触发器是一种特殊类型的存储过程，主要是通过事件触发来执行的，而存储过程可以通过存储过程名来直接调用。当往某一个数据表中插入、修改或者删除记录时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器和引起触发器执行的SQL语句被当作一次事务处理，如果这次事务未获得成功，SQL Server会自动返回该事务执行前后状态。
触发器是一个在修改指定表值的数据时执行的存储过程，不同的是执行存储过程要使用EXEC语句来调用，而触发器的执行不需要使用EXEC语句来调用，通过创建触发器可以保证不同数据表中的相关数据的引用完整性或一致性。
触发器的优点：
- 触发器是自动的。当对数据表中的数据做了任何修改（比如手工输入或者应用程序采取的操作）之后触发器会立即被激活。
- 触发器可以通过数据库中的相关数据表进行层叠更改。
- 触发器可以强制一些限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他数据表中的列（即数据表中的字段）。
1.1、触发器的作用
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的引用完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，能提供比CHECK约束更复杂的数据完整性，并自定义错误信息。触发器的主要作用：
- 强制数据库间的引用 完整性。
- 级联修改数据库中所有相关的数据表，自动触发其他与之相关的操作。
- 跟踪变化，撤销或回滚违法操作，防止非法修改数据。
- 返回自定义的错误信息，约束无法返回信息，而触发器可以。
- 触发器可以调用更多的存储过程。
触发器与存储过程的主要区别在于触发器的运行方式，存储过程需要用户、应用程序或者触发器来显式地调用并执行，而触发器是当特定事件（INSERT、UPDATE、DELETE）出现的时候，自动执行。
1.2、触发器的分类
1.2.1、数据操作语言触发器
数据操作语言（DML）触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。SQL Server中DML触发器有3种：INSERT触发器、UPDATE触发器和DELETE触发器。当遇到下面的情形时，考虑使用DML触发器：
- 通过数据库中的相关表实现级联更改。
- 防止恶意或者错误的INSERT、UPDATE和DELETE操作，并强制执行比CHECK约束定义的限制更为复杂的其他限制。
- 评估数据修改前后表的状态，并根据该差异采取措施。
在SQL Server中，针对每个DML触发器定义了两个特殊的表：DELETED表和INSERTED表，这两个逻辑表在内存中存放，由系统来创建和维护，用户不能对它们进行修改。触发器执行完成之后与该触发器相关的这两个表也会被删除。
- DELETED表存放执行DELETE或者UPDATE语句时要从表中删除的行。在执行DELETE或UPDATE时，被删除的行从触发触发器的表中被移动到DELETED表，即DELETED表和触发触发器的表有公共的行。
- INSERTED表存放执行INSERT或UPDATE语句时要向表中插入的行，在执行INSERT或UPDATE事务中，新行同时添加到触发触发器的表和INSERTED表。INSERTED表的内容是触发触发器的表中新行的副本，即INSERTED表中的行总是与触发触发器的表中的新行相同。
1.2.1、数据定义语言触发器
数据定义语言（DDL）触发器是当服务器或者数据库中发生数据定义语言事件时被激活而调用，使用DDL触发器可以防止对数据库架构进行的某些未授权更改。
二、创建DML触发器
DML触发器是指当数据库服务器中发生数据库操作语言事件时要执行的操作，DML事件包括对数据表或视图发生的INSERT、DELETE、UPDATE语句。
2.1、INSERT触发器
因为触发器是一种特殊类型的存储过程，所以创建触发器的语法格式与创建存储过程的语法格式相似。
当用户向表中插入新的记录行时，被标记为FOR INSERT的触发器的代码就会执行，同时SQL Server会创建一个新行的副本，将副本插入到一个特殊表中。该表只在触发器的作用域内存在。下面来创建当用户执行INSERT操作时触发的触发器。
在stu_info表上创建一个名为Insert_Student的触发器，在用户向stu_info表中插入数据时触发：
| USE SQLDB; GO CREATE TRIGGER Insert_Student ON stu_info AFTER INSERT AS BEGIN IF OBJECT_ID(N'stu_Sum',N'U') IS NULL ---判断stu_Sum表是否存在 CREATE TABLE stu_Sum(number INT DEFAULT 0); ---创建存储学生人数的stu_Sum表 DECLARE @stuNumber INT; SELECT @stuNumber = COUNT(*) FROM stu_info; IF NOT EXISTS (SELECT * FROM stu_Sum) ---判断表中是否有记录 INSERT INTO stu_Sum VALUES(0); UPDATE stu_Sum SET number = @stuNumber ----把更新后总的学生人数插入到stu_Sum表中 END GO |
上述程序语句的执行过程分析如下：
| IF OBJECT_ID(N'stu_Sum',N'U') IS NULL ---判断stu_Sum表是否存在 CREATE TABLE stu_Sum(number INT DEFAULT 0); ---创建存储学生人数的stu_Sum表 |
IF语句判断是否存在名为stu_Sum的表，如果不存在则创建该表。
| DECLARE @stuNumber INT; SELECT @stuNumber = COUNT(*) FROM stu_info; |
这两行语句声明一个整数类型的变量@stuNumber，其中存储了SELECT语句查询stu_info表中所有学生的人数。
| IF NOT EXISTS (SELECT * FROM stu_Sum) ---判断表中是否有记录 INSERT INTO stu_Sum VALUES(0); |
如果是第一次操作stu_Sum表，需要向该表中插入一条记录，否则下面的UPDATE语句将不能执行。
- AFTER:用于指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才触发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。注意该类型触发器仅能在表上创建，而不能在视图上定义。
- AS:触发器要执行的操作。
当创建完触发器之后，向stu_info表中插入记录，触发触发器的执行。执行下面的语句：
| SELECT COUNT(*) stu_info表中总人数 FROM stu_info; INSERT INTO stu_info(s_id,s_name,s_score,s_sex) VALUES(20,'白雪',87,'女'); SELECT COUNT(*) stu_info表中总人数 FROM stu_info; SELECT number AS stu_Sum表中总人数 FROM stu_Sum; |
从触发器的触发过程可以看到，查询语句中的第2行执行了一条INSERT语句，向stu_info表中插入了一条记录，结果显示插入前后stu_info表中总的记录数；第4行语句查看触发器执行之后stu_Sum表中的结果，可以看到，这里成功地将stu_info表中总的学生人数计算之后插入到stu_Sum表，实现了表的级联操作。
在某些情况下，根据数据库设计的需要，可能会禁止用户对某些表的操作，可以在表上指定拒绝执行插入操作。例如前面创建的stu_Sum表，其中插入的数据是根据stu_info表中计算得到的，用户不能随便插入数据。
创建触发器，当用户向stu_Sum表中插入数据时，禁止操作，输入如下语句：
| CREATE TRIGGER Insert_forbidden ON stu_Sum AFTER INSERT AS BEGIN RAISERROR('不允许直接向该表插入记录，操作被禁止',1,1) ROLLBACK TRANSACTION END |
输入下面的语句调用触发器：
| INSERT INTO stu_Sum VALUES(5); |
2.2、DELETE触发器
用户执行DELETE操作时，就会激活DELETE触发器，从而控制用户能够从数据库中删除的数据记录。触发DELETE触发器之后，用户删除的记录会被添加到DELETED表中，原来表中的相应记录被删除，所以可以在DELETED表中查看被删除的记录。
创建DELETE触发器，用户对stu_info表执行删除操作后触发，并返回删除的记录信息：
| USE SQLDB; GO CREATE TRIGGER Delete_Student ON stu_info AFTER DELETE AS BEGIN SELECT s_id AS 已删除的学生编号,s_name,s_score,s_sex,s_age FROM DELETED END GO |
与创建INSERT触发器过程相同，这里AFTER后面指定DELETE关键字，表明这是一个用户执行DELETE删除操作触发的触发器。
创建完成，执行一条DELETE语句触发该触发器：
| DELETE FROM stu_info WHERE s_id=20 |
这里返回的结果记录是从DELETED表中查询到的。
2.3、UPDATE触发器
UPDATE触发器是当用户在指定表上执行UPDATE语句时被触发而调用的。这种类型的触发器用来约束用户对现有数据的修改。
UPDATE触发器可以执行两种操作：更新前的记录存储到DELETED表；更新后的记录存储到INSERTED表。
创建UPDATE触发器，用户对stu_info表执行更新操作后触发，并返回记录信息：
| CREATE TRIGGER Update_Student ON stu_info AFTER UPDATE AS BEGIN DECLARE @stuCount INT; SELECT @stuCount = COUNT(*) FROM stu_info; UPDATE stu_Sum SET number = @stuCount; SELECT s_id AS 更新前学生编号,s_name AS 更新前学生姓名 FROM deleted SELECT s_id AS 更新后学生编号,s_name AS 更新后学生姓名 FROM inserted END GO |
创建完成，执行一条UPDATE语句触发该触发器：
| UPDATE stu_info SET s_name = '张毅' WHERE s_id=1; |
从执行过程可以看到，UPDATE语句触发触发器之后，DELETED和INSERTED两个表中保存的数据分别为为执行更新前后的数据。该触发器同时也更新了保存所有学生人数的stu_Sum表，该表中的number字段的值也同时被更新。
2.4、替代触发器
与前面的3种AFTER触发器不同，SQL Server服务器在执行触发AFTER触发器的SQL代码后，先建立临时的INSERTED和DELETED表，然后执行SQL代码中对数据的操作，最后才激活触发器的代码。而对于替代INSTEAD OF触发器的代码时，先建立临时的INSERTED和DELETED表，然后直接触发INSTEAD OF触发器，而拒绝执行用户输入的DML操作语句。
- INSTEAD OF：用于规定执行的是触发器而不是执行触发SQL语句，从而用触发器替代触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。然而，可以在每个具有INSTEAD OF触发器的视图上定义视图。INSTEAD OF触发器不能在WITH CHECK OPTION的可更新视图上定义。如果向指定的WITH CHECK OPTION选项的可更新视图添加INSTEAD OF触发器，系统将产生错误。用户必须用ALTER VIEW删除该选项后才能定义INSTEAD OF触发器。
基于多个基本表的视图必须使用INSTEAD OF触发器来对多个表中的数据进行插入、更新和删除操作。
创建INSTEAD OF触发器，当用户插入到stu_info表中的学生记录中的成绩大于100分时，拒绝插入，同时提示“插入成绩错误”：
| USE SQLDB; GO CREATE TRIGGER InsteadOfInsert_Student ON stu_info INSTEAD OF INSERT AS BEGIN DECLARE @stuScore INT; SELECT @stuScore = (SELECT s_score FROM inserted) If @stuScore > 100 SELECT '插入成绩错误' AS 失败原因 END GO |
创建完成，执行一条INSERT语句触发该触发器：
| INSERT INTO stu_info(s_id,s_name,s_score,s_sex) VALUES(22,'周鸿',110,'男') SELECT * FROM stu_info |
从返回结果可以看到，插入的记录的s_score字段值大于100，将无法插入到基本表，基本表中没有新增记录。
2.5、允许使用嵌套触发器
如果一个触发器在执行操作时调用了另一个触发器，而这个触发器又接着调用了下一个触发器，那么就形成了嵌套触发器。嵌套触发器在安装时就被启用，但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。
触发器最多可以嵌套32层，如果嵌套的次数超过限制，那么该触发器将被终止，并回滚整个事务。使用嵌套触发器需要考虑以下注意事项：
- 默认情况下，嵌套触发器配置选项是开启的。
- 在同一个触发器事务中，一个嵌套触发器不能被触发两次。
- 由于触发器一个事务，如果在一系列嵌套触发器的任意层中发生错误，则整个事务都将取消，而且所有数据将回滚。
嵌套是用来保持整个数据库的完整性的重要功能，但有时可能需要禁用嵌套，如果禁用了嵌套，那么修改一个触发器的实现不会再触发该表上的任何触发器。
- 嵌套触发要求复杂而有条理的设计，级联修改可能会修改用户不想涉及的数据。
- 在一系列嵌套触发器中的任意点的时间修改操作都会触发一些触发器，尽管这时数据库提供很强的保护，但如果要以特定的顺序更新表，就会产生问题。
使用如下语句禁用嵌套：
| EXEC sp_configure 'nested triggers',0 |
如果要再次启用嵌套可以使用如下语句：
| EXEC sp_configure 'nested triggers',1 |
如果不想对触发器进行嵌套，还可以通过【允许触发器激发其他触发器】的服务器配置选项来控制。但不管此设置是什么，都可以嵌套INSTEAD OF触发器。
2.5.1、设置触发器嵌套选项更改步骤
- 在对象资源管理器窗口中，右击服务器名，并在弹出的快捷菜单中选择【属性】。
- 打开服务器属性对话框，选择【高级】选项。设置高级选项卡【杂项】里【允许触发器激活其他触发器】为True或False，分别代表激活或不激活。
2.6、递归触发器
递归触发器是指一个触发器从其内部再一次激活该触发器，例如UPDATE操作激活的触发器内部还有一条对数据表的更新语句，那么这条更新语句就有可能再次激活这个触发器本身，当然，这种递归的触发器内部还会有判断语句，只有在一定情况下才会执行那条T-SQL语句，否则就成了无限调用的死循环了。
SQL Server中的递归触发器包括两种：
- 直接递归：触发器被触发并执行一个操作，而该操作又使同一个触发器再次被触发。
- 间接递归：触发器被触发并执行一个操作，而该操作又使另一个个表中的某个触发器被触发，第二个触发器使原始表得到更新，从而再次触发第一个触发器。
默认情况下，递归触发器选项是禁用的，但可以通过管理平台来设置启用递归触发器：
- 选择需要修改的数据库右击，在弹出的快捷菜单中选择【属性】菜单命令。
- 打开数据库属性对话框，选择【选项】，在选项卡的【杂项】选项组中，在【递归触发器已启用】后的下拉列表中选择True，单击【确定】。
提示：递归触发器最多只能递归16层，如果递归中的第16个触发器激活了第17个触发器，则结果与发布ROLLBACK命令一样，所有数据将回滚。
三、创建DDL触发器
与DML触发器相同，DDL触发器可以通过用户的操作而激活，是当用户创建、修改和删除数据库对象时触发。对于DDL触发器而言，其创建和管理过程与DML触发器类似。
3.1、创建服务器作用域的DDL触发器
创建服务器作用域的DDL触发器，需要指定ALL SERVER参数。
- ALL SERVER：表示将DDL或登录触发器的作用域应用于当前服务器。
创建数据库作用域的的DDL触发器，拒绝用户对数据库中数据表的删除和修改操作：
| USE SQLDB; GO CREATE TRIGGER DenyDelete_test ON DATABASE FOR DROP_TABLE,ALTER_TABLE AS BEGIN PRINT '用户没有权限执行删除操作！' ROLLBACK TRANSACTION END GO |
ON关键字后面的指定触发器作用域；“DROP_TABLE，ALTER_TABLE”语句指定DDL触发器的触发事件，即删除和修改表；最后定义BEGIN END语句块，输出提示信息。
- DATABASE：表示将DDL触发器的作用域应用于当前数据库。
创建完成后，执行一条DROP语句触发该触发器：
| DROP TABLE [dbo].[MyTable]; |
创建服务器作用域的DDL触发器，拒绝用户对数据库中数据表的删除和修改操作：
| CREATE TRIGGER DenyCreate_AllServer ON ALL SERVER FOR CREATE_DATABASE,ALTER_DATABASE AS BEGIN PRINT '用户没有权限创建或修改服务器上的数据库！' ROLLBACK TRANSACTION END GO |
创建成功后，依次打开服务器的【服务器对象】下的【触发器】节点，可以看到创建的服务器作用域的DenyCreate_AllServer触发器。
上述程序代码成功创建了整个服务器作为作用域的触发器，当用户创建或修改数据库时触发触发器，禁止用户的操作，并显示信息。执行下面的语句来测试触发器的执行过程。
| CREATE DATABASE test01; |
四、管理触发器
4.1、查看触发器
4.1.1、使用对象资源管理器查看触发器信息
- 首先登录到SQL Server图形用户界面的管理平台，在【对象资源管理器】窗口中打开需要查看的触发器所在的数据表节点。在【触发器】列表中选择要查看的触发器，右击并弹出的快捷菜单中选择【修改】菜单命令，或者双击该触发器。
- 在查询编辑窗口中将显示创建该触发器的代码内容。
4.1.2、使用系统存储过程查看触发器
因为触发器是一种特殊的存储过程，所以也可以使用查看存储过程的方法来查看触发器的内容：
| USE SQLDB; GO sp_helptext Insert_student; |
从结果可以看到到，使用系统存储过程sp_helptext查看的触发器的定义信息与用户输入的代码是相同的。
4.2、修改触发器
当触发器不满足需求时，可以修改触发器的定义和属性，在SQL Server中可以通过两种方式进行修改：先删除原来的触发器，再重新创建与之名称相同的触发器；直接修改现有触发器的定义。修改触发器定义可以使用ALTER TRIGGER语句。
修改Insert_Student触发器，将INSERT触发器修改为DELETE触发器。
| ALTER TRIGGER Insert_Student ON stu_info AFTER DELETE AS BEGIN IF OBJECT_ID(N'stu_Sum',N'U') IS NULL ---判断stu_Sum表是否存在 CREATE TABLE stu_Sum(number INT DEFAULT 0); ---创建存储学生人数的stu_Sum表 DECLARE @stuNumber INT; SELECT @stuNumber = COUNT(*) FROM stu_info; IF NOT EXISTS (SELECT * FROM stu_Sum) ---判断表中是否有记录 INSERT INTO stu_Sum VALUES(0); UPDATE stu_Sum SET number = @stuNumber ----把更新后总的学生人数插入到stu_Sum表中 END |
这里将INSERT关键字替换为DELETE，其他内容不变。
4.3、删除触发器
当触发器不再使用时，可以将其删除，删除触发器不会影响其操作的数据表，而当某个表被删除时，该表上的触发器也同时被删除。
删除触发器有两种方式：在对象资源管理器中删除和使用DROP TRIGGER语句删除。
- 在对象资源管理器中删除触发器
在对象资源管理器中选择要删除的触发器，在弹出的菜单中选择【删除】命令或者按键盘上的Delete键进行删除，在弹出的【删除对象】对话框中单击【确定】按钮。
- 使用DROP TRIGGER语句删除触发器
DROP TRIGGER语句可以删除一个或多个触发器：
| USE SQLDB; GO DROP TRIGGER [Insert_Student]; |
- 删除服务器作用域的触发器DenyCreate_AllServer，执行完命令刷新一下就看不到此触发器了：
| DROP TRIGGER [DenyCreate_AllServer] ON ALL Server; |
4.4、启用和禁用触发器
触发器创建之后便启用了，如果暂时不需要使用某个触发器，可以将其禁用。触发器被禁用后并没有删除，它仍然作为对象存储在当前当前数据库中。但是当用户执行触发操作（INSERT、DELETE、UPDATE）时，触发器不会被调用。禁用触发器可以使用ALTER TABLE语句或者DISABLE TRIGGER语句。
- 禁用触发器
| USE SQLDB; GO ALTER TABLE [dbo].[stu_info] DISABLE TRIGGER [Update_Student]; |
| DISABLE TRIGGER Update_Student ON stu_info ---也可以输入此语句，和上面执行结果相同 |
如果出现以下报错，说明有触发器阻止，需先禁用或删除此触发器：
- 禁用数据库作用域的DenyDelete_test触发器
| DISABLE TRIGGER DenyDelete_test ON DATABASE; |
ON关键字后面指定触发器作用域。
- 启用触发器
被禁用的触发器可以通过ALTER TABLE 语句或ENABLE TRIGGER语句重新启用。
| USE SQLDB; GO ALTER TABLE [dbo].[stu_info] ENABLE TRIGGER [Update_Student]; |
| ENABLE TRIGGER [Update_Student] ON [dbo].[stu_info]; ---也可以输入此语句，和上面执行结果相同 |
- 启用数据库作用域的DenyDelete_test触发器
| ENABLE TRIGGER DenyDelete_test ON DATABASE; |
五、注意点
- 在使用触发器的时候需要注意的是，对相同的数据表、相同的的事件只能创建一个触发器，比如对表account创建了一个AFTER INSERT触发器，那么如果对表account再次创建一个个AFTER INSERT触发器，SQL Server将会报错，此时只可以在表account上创建INSTEAD OF UPDATE类型的触发器。
- 触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。如果需求发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响新的数据的完整性。因此，要将不再使用的触发器用时删除。
扫一扫
617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
