创建嵌套触发器

2017-06-13

当触发器执行启动另一个触发器的作时，DML 和 DDL 触发器都是嵌套的。这些动作可以启动其他触发器，以此类推。 DML 和 DDL 触发器最多可嵌套 32 个级别。可以控制 AFTER 触发器是否可以通过 嵌套触发器 服务器配置选项嵌套。 INSTEAD OF 触发器（只有 DML 触发器可以是 INSTEAD OF 触发器）可以嵌套，而不管此设置如何。

注释

对 Transact-SQL 触发器中托管代码的任何引用都计入 32 级嵌套限制的一个级别。从托管代码中调用的方法不计入此限制。

如果允许嵌套触发器，并且链中的触发器启动无限循环，则会超过嵌套级别，并且触发器将终止。

可以使用嵌套触发器执行有用的管家功能，例如存储受上一触发器影响的行的备份副本。例如，可以在 PurchaseOrderDetail 创建一个触发器，用于保存 PurchaseOrderDetail 行在 delcascadetrig 触发器删除时的备份副本。当 delcascadetrig 触发器生效时，从 PurchaseOrderHeader 中删除 PurchaseOrderID 1965 会从 PurchaseOrderDetail 中删除相应的行。若要保存数据，可以针对该触发器创建 DELETE 触发器 PurchaseOrderDetail ，以便将已删除的数据保存到另一个单独创建的表中 del_save。例如：

CREATE TRIGGER Purchasing.savedel  
   ON Purchasing.PurchaseOrderDetail  
FOR DELETE  
AS  
   INSERT del_save;  
   SELECT * FROM deleted;

不建议在依赖于顺序的序列中使用嵌套触发器。使用独立的触发器来级联数据修改。

注释

由于触发器在事务中执行，因此在一组嵌套触发器的任何级别发生故障都会取消整个事务，并且所有数据修改都会回滚。在触发器中包含 PRINT 语句，以便可以确定发生故障的位置。

递归触发器

除非设置了RECURSIVE_TRIGGERS数据库选项，否则 AFTER 触发器不会以递归方式调用自身。

有两种类型的递归：

直接递归

当触发器被触发并执行了一项导致相同触发器再次触发的操作时，会发生这种递归。例如，应用程序更新表 T3;这会导致触发 Trig3 。 Trig3 再次更新表 T3 ;这会导致触发器 Trig3 再次触发。

当再次调用同一触发器时，直接递归也可能发生，不过这是在调用了一种不同类型的触发器（AFTER 或 INSTEAD OF）之后。换句话说，当第二次调用同一个 INSTEAD OF 触发器时，即使在这之间调用了一个或多个 AFTER 触发器，也可能发生 INSTEAD OF 触发器的直接递归。同样，AFTER 触发器的直接递归可能发生在第二次调用同一个 AFTER 触发器时，即使在此期间调用了一个或多个 INSTEAD OF 触发器。例如，应用程序更新表 T4。此更新会导致 INSTEAD OF 触发器 Trig4 被激活。 Trig4 更新表 T5。此更新会导致 AFTER 触发器 Trig5 触发。 Trig5 更新表 T4，此更新使得 INSTEAD OF 触发器 Trig4 再次触发。对于 Trig4，此事件链被视为直接递归。
间接递归

当触发器被触发并执行了导致同一类型（AFTER 或 INSTEAD OF）的另一个触发器被触发的动作时，这种递归就会发生。第二个触发器执行一个动作，使原始触发器再次触发。换句话说，当第二次调用 INSTEAD OF 触发器时，可能发生间接递归，但只有在中间调用另一个 INSTEAD OF 触发器之后才会出现。同样，当第二次调用 AFTER 触发器时，可能会发生间接递归，但直到在两者之间调用另一个 AFTER 触发器之前才会发生。例如，应用程序更新表 T1。此更新会导致 AFTER 触发器 Trig1 触发。 Trig1 更新表 T2，此更新会导致 AFTER 触发器 Trig2 触发。 Trig2 又更新表 T1 ，导致 AFTER 触发器 Trig1 再次触发。

仅当将 RECURSIVE_TRIGGERS 数据库选项设置为 OFF 时，才会阻止 AFTER 触发器的直接递归过程。若要禁用 AFTER 触发器的间接递归，还请将嵌套触发器服务器选项设置为 0。

例子

以下示例演示如何使用递归触发器来解决自我引用关系（也称为传递闭包）。例如，该表 emp_mgr 定义以下内容：

公司的员工（emp）。
每个员工的经理（mgr）。
向每位员工报告的组织树中的员工总数（NoOfReports）。

递归 UPDATE 触发器可用于在插入新员工记录时保留 NoOfReports 列 up-to日期。 INSERT 触发器更新 NoOfReports 管理器记录的列，该列以递归方式更新 NoOfReports 管理层次结构上其他记录的列。

USE AdventureWorks2012;  
GO  
-- Turn recursive triggers ON in the database.  
ALTER DATABASE AdventureWorks2012  
   SET RECURSIVE_TRIGGERS ON;  
GO  
CREATE TABLE dbo.emp_mgr (  
   emp char(30) PRIMARY KEY,  
    mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),  
    NoOfReports int DEFAULT 0  
);  
GO  
CREATE TRIGGER dbo.emp_mgrins ON dbo.emp_mgr  
FOR INSERT  
AS  
DECLARE @e char(30), @m char(30);  
DECLARE c1 CURSOR FOR  
   SELECT emp_mgr.emp  
   FROM   emp_mgr, inserted  
   WHERE emp_mgr.emp = inserted.mgr;  
  
OPEN c1;  
FETCH NEXT FROM c1 INTO @e;  
WHILE @@fetch_status = 0  
BEGIN  
   UPDATE dbo.emp_mgr  
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly  
   WHERE emp_mgr.emp = @e ;                           -- added employee.  
  
   FETCH NEXT FROM c1 INTO @e;  
END  
CLOSE c1;  
DEALLOCATE c1;  
GO  
-- This recursive UPDATE trigger works assuming:  
--   1. Only singleton updates on emp_mgr.  
--   2. No inserts in the middle of the org tree.  
CREATE TRIGGER dbo.emp_mgrupd ON dbo.emp_mgr FOR UPDATE  
AS  
IF UPDATE (mgr)  
BEGIN  
   UPDATE dbo.emp_mgr  
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's  
   FROM inserted                            -- (no. of reports) by  
   WHERE emp_mgr.emp = inserted.mgr;         -- 1 for the new report.  
  
   UPDATE dbo.emp_mgr  
   SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's  
   FROM deleted                             -- (no. of reports) by 1  
   WHERE emp_mgr.emp = deleted.mgr;          -- for the new report.  
END  
GO  
-- Insert some test data rows.  
INSERT dbo.emp_mgr(emp, mgr) VALUES  
    ('Harry', NULL)  
    ,('Alice', 'Harry')  
    ,('Paul', 'Alice')  
    ,('Joe', 'Alice')  
    ,('Dave', 'Joe');  
GO  
SELECT emp,mgr,NoOfReports  
FROM dbo.emp_mgr;  
GO  
-- Change Dave's manager from Joe to Harry  
UPDATE dbo.emp_mgr SET mgr = 'Harry'  
WHERE emp = 'Dave';  
GO  
SELECT emp,mgr,NoOfReports FROM emp_mgr;  
  
GO

下面是更新前的结果。

emp                            mgr                           NoOfReports  
------------------------------ ----------------------------- -----------  
Alice                          Harry                          2  
Dave                           Joe                            0  
Harry                          NULL                           1  
Joe                            Alice                          1  
Paul                           Alice                          0

下面是更新后的结果。

emp                            mgr                           NoOfReports  
------------------------------ ----------------------------- -----------  
Alice                          Harry                          2  
Dave                           Harry                          0  
Harry                          NULL                           2  
Joe                            Alice                          0  
Paul                           Alice                          0

设置嵌套触发器选项

sp_configure（Transact-SQL）

设置RECURSIVE_TRIGGERS数据库选项

ALTER DATABASE SET 选项 (Transact-SQL)

另请参阅

CREATE TRIGGER （Transact-SQL）
配置嵌套触发器服务器配置选项

通过

创建嵌套触发器

递归触发器

例子

另请参阅

其他资源