5.SQL Server DML触发器--嵌套和递归触发器



本文摘自《锋利的SQL》：http://item.jd.com/10380652.html

1．嵌套触发器

无论是DML触发器还是DDL触发器，如果出现了一个触发器执行启动另一个触发器的操作，都属于嵌套触发器。DML触发器和DDL触发器最多可以嵌套32层，SQL触发器中对托管代码的任何引用均计为32层嵌套限制中的一层，从托管代码内部调用的方法不根据此限制进行计数。

可以通过nestedtriggers服务器配置选项来控制是否可以嵌套AFTER触发器。INSTEAD OF触发器嵌套不受此选项影响。参考下面的语句：

sp_configure 'nested triggers',1; -- 设置nested triggers选项为1，允许AFTER触发器嵌套

GO

RECONFIGURE; --使用新环境值

GO

EXEC sp_configure 'nested triggers'; -- 查看nested triggers选项设置

GO

如果嵌套触发器中的一个触发器启动了一个无限循环，则将超出嵌套层限制，触发器将被终止执行。

在下面的示例中，当从PriTable表中删除订单信息时，PriDelTrigger触发器将从DetailTable表中删除该笔订单的产品信息，在删除这些信息的同时，DetailDelTrigger触发器将保存被删除行到DetailTableBak表中。这种由一个触发器启动另一个触发器的操作，就属于嵌套触发器。

USE AdventureWorks;

Go

--创建主表，存放销售订单编号和金额

CREATE TABLE PriTable

(OrderID int IDENTITY(1,1), OrderTotal money);

 

--创建明细表，存放每笔订单中的产品信息

CREATE TABLE DetailTable

(OrderID int, ProductID int, ProductCount int NOTNULL, Price money);

 

--创建备份表，存储DetailTable中被删除的数据

CREATE TABLE DetailTableBak

(OrderID int, ProductID int, ProductCount int NOTNULL, Price money);

GO

 

--向主表中插入订单信息

INSERT INTO PriTable VALUES (2100.00);

INSERT INTO PriTable VALUES (1000.00);

--向明细表中插入订单的产品信息

INSERT INTO DetailTable VALUES (1,1,10,110.00);

INSERT INTO DetailTable VALUES (1,2,10,100.00);

INSERT INTO DetailTable VALUES (2,2,10,100.00);

GO

 

--为PriTrigger表创建触发器

CREATE TRIGGER PriDelTrigger

ON PriTable

AFTER DELETE

AS

   DELETEFROM DetailTable

   WHEREOrderID IN (SELECT OrderID

                    FROM Deleted);

GO

 

--为DetailTable表创建触发器，保存被删除的数据到DetailTableBak表中

CREATE TRIGGER DetailDelTrigger

ON DetailTable

AFTER DELETE

AS

   INSERTINTO DetailTableBak

       SELECT * FROM Deleted;

GO

 

--测试触发器

DELETE FROM PriTable

WHERE OrderID = 1;

SELECT * FROM DetailTableBak; -- 查看被删除的行

2．递归触发器

（1）直接递归

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

（2）间接递归

触发器触发并执行另一个触发器的操作时，该触发器却再次触发了第一个触发器，这就发生了间接递归。例如，应用程序更新了表T1，从而触发了触发器Trig1。Trig1更新了表T2，从而触发了触发器Trig2。Trig2转而更新了表T1，从而再次触发了Trig1。

只有在设置RECURSIVE_TRIGGERS数据库选项为ON的情况下，才允许以递归方式调用AFTER触发器。如：

ALTER DATABASE AdventureWorks

   SETRECURSIVE_TRIGGERS ON;

在将RECURSIVE_TRIGGERS数据库选项设置为OFF时，仅防止直接递归。如果要禁用间接递归，还应将nestedtriggers服务器选项设置为0。

下面是一个使用递归触发器的示例，在使用个递归触发器时，应当能够有一个明确的条件来结束递归，防止出现死循环。表emp_mgr中包含3列，其中emp用于存储公司雇员的名称，mgr用于存储每个雇员的经理，mgcount存储每个经理管理的雇员总数。

用于UPDATE的emp_mgrupd触发器是一个递归触发器。当改变一个雇员的经理时，将执行触发器中的UPDATE语句，调整相应经理的管理人数。触发器中的这个UPDATE语句会再次触发emp_mgrupd触发器，形成递归。但是，由于触发器中包含有IF UPDATE (mgr)语句，所以在被递归触发时，不会再执行其中的UPDATE语句，递归结束。

USE AdventureWorks;

GO

--允许递归.

ALTER DATABASE AdventureWorks

  SETRECURSIVE_TRIGGERS ON;

GO

 

--创建表

CREATE TABLE emp_mgr (

  empchar(30) PRIMARY KEY,

   mgrchar(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),

   mgcountint DEFAULT 0

);

GO

 

--创建INSERT触发器，当插入新行时，增加相应经理的管理人数

CREATE TRIGGER emp_mgrins

ON emp_mgr

AFTER INSERT

AS

   UPDATEemp_mgr

   SETemp_mgr.mgcount = emp_mgr.mgcount + 1

   FROM Inserted                           

   WHEREemp_mgr.emp = Inserted.mgr;

 

GO

 

--创建UPDATE触发器，当调整雇员的经理时，则相应调整经理的管理人数

--该触发器要求每次仅更新一名雇员的经理

CREATE TRIGGER emp_mgrupd

ON emp_mgr

AFTER UPDATE

AS

   IF UPDATE(mgr)

   BEGIN

       UPDATE emp_mgr

       SETemp_mgr.mgcount = emp_mgr.mgcount + 1

       FROM Inserted                           

       WHEREemp_mgr.emp = Inserted.mgr;

 

       UPDATEemp_mgr

       SETemp_mgr.mgcount = emp_mgr.mgcount - 1

       FROM Deleted       

       WHEREemp_mgr.emp = Deleted.mgr;

   END

GO

 

--插入测试数据

INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL);

INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry');

INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice');

INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice');

INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe');

执行下面的语句，查看emp_mgr表中的数据，以及将Dave的经理由Joe改变为Harry后的数据变化情况，如图14-4所示。

SELECT * FROM emp_mgr;

GO

--将Dave的经理由Joe改变为Harry

UPDATE emp_mgr

SET mgr = 'Harry'

WHERE emp = 'Dave';

--查看改变经理后的结果

SELECT * FROM emp_mgr;