背景
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