创建嵌套触发器

适用于: SQL Server Azure SQL 数据库 Azure SQL 托管实例

当触发器执行启动其他触发器的操作时,DML 和 DDL 触发器都是嵌套触发器。 这些操作都可以启动其他触发器等。 DML 触发器和 DDL 触发器最多可以嵌套 32 层。 可以通过 nested triggers 服务器配置选项来控制是否可以嵌套 AFTER 触发器。 但不管此设置是什么,都可以嵌套 INSTEAD OF 触发器(只有 DML 触发器可以为 INSTEAD OF 触发器)。

注意

Transact-SQL 触发器中对托管代码的任何引用均计为 32 层嵌套限制中的一层。 从托管代码内部调用的方法不根据此限制进行计数。

如果允许使用嵌套触发器,且链中的一个触发器启动了一个无限循环,则将超出嵌套层限制,且触发器将终止。

可使用嵌套触发器执行一些有用的日常工作,如保存前一个触发器所影响行的一个备份副本。 例如,可以在 PurchaseOrderDetail 上创建一个触发器,以保存由 PurchaseOrderDetail 触发器所删除的 delcascadetrig 行的备份副本。 delcascadetrig 触发器有效时,从 PurchaseOrderID 中删除 PurchaseOrderHeader 1965 将删除 PurchaseOrderDetail中对应的行。 为了保存数据,可在 PurchaseOrderDetail 上创建一个 DELETE 触发器,该触发器可将删除的数据保存到另一个单独创建的表 ( del_save) 中。 例如:

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

建议不要按与顺序相关的序列使用嵌套触发器。 应使用单独的触发器级联数据修改。

注意

由于触发器在事务中执行,如果在一组嵌套触发器的任意层中发生错误,则整个事务都将取消,且所有的数据修改都将回滚。 在触发器中包含 PRINT 语句可以确定错误的发生位置。

递归触发器

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

有两种不同的递归方式:

  • 直接递归

    在触发器触发并执行一个导致同一个触发器再次触发的操作时,将发生此递归。 例如,应用程序更新了表 T3,从而触发了触发器 Trig3Trig3 再次更新表 T3 ,从而再次触发了触发器 Trig3

    调用其他类型的触发器(AFTER 或 INSTEAD OF)之后再次调用同一个触发器时,也会发生直接递归。 换言之,当同一个 INSTEAD OF 触发器被第二次调用时,即使在这两次调用之间调用了一个或多个 AFTER 触发器,也会发生 INSTEAD OF 触发器的直接递归。 同样,当同一个 AFTER 触发器被第二次调用时,即使在这两次调用之间调用了一个或多个 INSTEAD OF 触发器,也会发生 AFTER 触发器的直接递归。 例如,一个应用程序对表 T4 进行更新。 此更新将导致触发 INSTEAD OF 触发器 Trig4Trig4 对表 T5 进行更新。 此更新将导致触发 AFTER 触发器 Trig5Trig5 更新表 T4,此更新将导致再次触发 INSTEAD OF 触发器 Trig4 。 此事件链即被认为是 Trig4 的直接递归。

  • 间接递归

    当触发器触发并执行导致触发相同类型的其他触发器(AFTER 或 INSTEAD OF)的操作时,会发生此类递归。 第二个触发器执行一个再次触发第一个触发器的操作。 换言之,当在这两次调用之间调用其他 INSTEAD OF 触发器之前第二次调用 INSTEAD OF 触发器,便会发生间接递归。 同样,当在这两次调用之间调用其他 AFTER 触发器之前第二次调用 AFTER 触发器,也会发生间接递归。 例如,一个应用程序对表 T1 进行更新。 此更新将导致触发 AFTER 触发器 Trig1Trig1 更新表 T2,此更新将导致触发 AFTER 触发器 Trig2Trig2 反过来更新表 T1 ,从而导致再次触发 AFTER 触发器 Trig1

当 RECURSIVE_TRIGGERS 数据库选项设置为 OFF 时,仅阻止 AFTER 触发器的直接递归。 若要禁用 AFTER 触发器的间接递归,还必须将 nested triggers 服务器选项设置为 0

示例

下面的示例中说明使用递归触发器来解决自引用关系(也称为传递闭包)。 例如,表 emp_mgr 定义了以下内容:

  • 一个公司中的雇员 (emp)。

  • 每个雇员的经理 (mgr)。

  • 组织树中向每个经理汇报的雇员总数 (NoOfReports)。

递归 UPDATE 触发器可以用于在插入新雇员记录时让 NoOfReports 列保持最新。 INSERT 触发器更新经理记录的 NoOfReports 列,而该操作递归更新管理层向上的其他记录的 NoOfReports 列。

USE AdventureWorks2022;  
GO  
-- Turn recursive triggers ON in the database.  
ALTER DATABASE AdventureWorks2022  
   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  

设置 nested triggers 选项

设置 RECURSIVE_TRIGGERS 数据库选项

另请参阅

CREATE TRIGGER (Transact-SQL)
配置 nested triggers 服务器配置选项