如何控制触发器递归

 

背景

A 表 UPDATE 后，取 B 表某列再次 UPDATE A 表，这样又触发了 A 表的 UPDATE   触发器，我的目的是只触发一次，是否设置 nested triggers 选项关闭递归触发器即可？

分析

首先，必须清楚触发器递归的定义，触发器有两种递归方式：

1．     直接递归

A表上的触发器更改（插入/删除/更新）A表数据，导致A表的触发器再次触发，这种状况称之为直接递归；

2．     间接递归

A表上的触发器更新B表数据，导致触发B表触发器；而B表触发器又更改A表数据，导致A表触发器再次触发，这种状况称之为间接递归

解决方法 — 选项配置（影响所有范围的触发器）

SQL Server提供了数据库级和服务器级配置来确定递归触发器是否被允许:

1.       服务器级（使用存储过程 sp_configure 进行配置）

server trigger recursion 选项 (SQL Server 2005) 决定是否允许服务器级触发器直接递归激发；当此选项设置为 1 (ON ，默认值 ) 时，将允许服务器级触发器递归激发；当设置为 0 (OFF) 时，服务器级触发器不能递归激发。

nested triggers 选项决定是否允许触发器间接递归激发；当此选项设置为 1 (ON ，默认值 ) 时，将允许触发器递归激发；当设置为 0 (OFF) 时，触发器不能递归激发。

2.       数据库级

RECURSIVE_TRIGGERS 数据库选项设置决定是否允许数据库中的触发器直接递归触发；默认值为 OFF ，不允许直接递归触发。

该选项可以通过存储过程 sp_dboption设置；对于SQL Server 2005，还可以使用类似下面的T-SQL设置：

ALTER DATABASE [DbName]

    SET RECURSIVE_TRIGGERS ON

使用选项决定递归触发器的行为时，需要注意的是选项设置的有效范围：

nested triggers 选项决定所有的触发器是否间接递归激发，这意味着这是一个 SQL Server 实例级的选项，设置将影响所有的触发器。

server trigger recursion 选项是 SQL Server 2005 中才有的（ SQL Server 2005 才有服务器级触发器）。

RECURSIVE_TRIGGERS 选项影响配置它的数据库中的所有触发器。

其他解决方法（针对特定的触发器）

如果只希望特定的触发器允许或者禁止触发器，则SQL Server没有选项可以做到；如果确实需要这样的功能，可以在触发器代码中实现控制：

1．       使用 update ( 列名 ) 函数

此函数适用于对 UPDATE 的控制。对于 "A 表 UPDATE 后，取 B 表某列再次 UPDATE A 表 " ，如果仅更新 A 表的某些列才触发 UPDATE B, 并且 B 表再次 UPDATE A 表不会包含 A 表触发 UPDATE B 的那些列 , 则在 A 表的触发器中 , 使用 IF UPDATE( 列 ) 来确定是否应该 UPDATE B 即可。

2．       使用 @@NESTLEVEL

该变量值确定嵌套层数。

对于 "A 表 update 后，取 B 表某列再次 UPDATE A 表 " ，如果触发者不是一个存储过程 , 则 UPDATE A 的 A 表触发器 @@NESTLEVEL = 1, 到 UPDATE B 时 , B 表触发器 @@NESTLEVEL = 2, B 表触发器再 UPDATE A 时 , @@NESTLEVEL = 3 。

所以如果 @@NESTLEVEL >=3 时 , 一般表示递归了 ( 当然 , 前提是 UPDATE A 的触发器本身没有两层的递归 , 即不能是存储过程再调用存储过程去 UPDATE A 。

3．       使用 @@PROCID

该全局变量返回调用者的 object_id 。 如果需要 A 表触发 B 表触发器，而 B 表触发器再触发 A 表触发器时， A 表触发器不响应；则在 A 表触发器中使用它来判断触发者是谁 , 如果是 B 表触发器 , 则不处理就行了 , 类似下面这样：

IF OBJECT_ID(N'B 表触发器名称 ' ) = @@PROCID

BEGIN

    PRINT 'B 表触发器 , 不处理 '

    RETURN

END