SQL 如何防止数据库触发器递归？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1529412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I prevent a database trigger from recursing?
提问by Pure.Krome
I've got the following trigger on a table for a SQL Server 2008 database. It's recursing, so I need to stop it.
我在 SQL Server 2008 数据库的表上有以下触发器。它是递归的，所以我需要停止它。
After I insert or update a record, I'm trying to simply update a single field on that table.
插入或更新记录后，我试图简单地更新该表上的单个字段。
Here's the trigger :
这是触发器：
ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate]
ON [dbo].[tblMedia]
BEFORE INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @IdMedia INTEGER,
@NewSubject NVARCHAR(200)
SELECT @IdMedia = IdMedia, @NewSubject = Title
FROM INSERTED
-- Now update the unique subject field.
-- NOTE: dbo.CreateUniqueSubject is my own function.
-- It just does some string manipulation.
UPDATE tblMedia
SET UniqueTitle = dbo.CreateUniqueSubject(@NewSubject) +
CAST((IdMedia) AS VARCHAR(10))
WHERE tblMedia.IdMedia = @IdMedia
END
Can anyone tell me how I can prevent the trigger's insert from kicking off another trigger again?
谁能告诉我如何防止触发器的插入物再次触发另一个触发器？
回答by Anssssss
Not sure if it is pertinent to the OP's question anymore, but in case you came here to find out how to prevent recursion or mutual recursion from happening in a trigger, you can test for this like so:
不确定它是否与 OP 的问题有关，但如果您来这里是为了了解如何防止在触发器中发生递归或相互递归，您可以像这样测试：
IF TRIGGER_NESTLEVEL() <= 1/*this update is not coming from some other trigger*/
MSDN link
MSDN链接
回答by Rodrigo
I see three possibilities:
我看到了三种可能性：
Disable trigger recursion:
This will prevent a trigger fired to call another trigger or calling itself again. To do this, execute this command:
ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF GOUse a trigger INSTEAD OF UPDATE, INSERT
Using a
INSTEAD OFtrigger you can control any column being updated/inserted, and even replacing before calling the command.Control the trigger by preventing using IF UPDATE
Testing the column will tell you with a reasonable accuracy if you trigger is calling itself. To do this use the
IF UPDATE()clause like:ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate] ON [dbo].[tblMedia] FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON DECLARE @IdMedia INTEGER, @NewSubject NVARCHAR(200) IF UPDATE(UniqueTitle) RETURN; -- What is the new subject being inserted? SELECT @IdMedia = IdMedia, @NewSubject = Title FROM INSERTED -- Now update the unique subject field. -- NOTE: dbo.CreateUniqueSubject is my own function. -- It just does some string manipulation. UPDATE tblMedia SET UniqueTitle = dbo.CreateUniqueSubject(@NewSubject) + CAST((IdMedia) AS VARCHAR(10)) WHERE tblMedia.IdMedia = @IdMedia END
禁用触发器递归：
这将防止触发触发器以调用另一个触发器或再次调用自身。为此，请执行以下命令：
ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF GO使用触发器 INSTEAD OF UPDATE, INSERT
使用
INSTEAD OF触发器，您可以控制正在更新/插入的任何列，甚至可以在调用命令之前进行替换。通过阻止使用 IF UPDATE 来控制触发器
如果您的触发器调用自身，则测试该列会以合理的准确度告诉您。为此，请使用以下
IF UPDATE()子句：ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate] ON [dbo].[tblMedia] FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON DECLARE @IdMedia INTEGER, @NewSubject NVARCHAR(200) IF UPDATE(UniqueTitle) RETURN; -- What is the new subject being inserted? SELECT @IdMedia = IdMedia, @NewSubject = Title FROM INSERTED -- Now update the unique subject field. -- NOTE: dbo.CreateUniqueSubject is my own function. -- It just does some string manipulation. UPDATE tblMedia SET UniqueTitle = dbo.CreateUniqueSubject(@NewSubject) + CAST((IdMedia) AS VARCHAR(10)) WHERE tblMedia.IdMedia = @IdMedia END
回答by Remus Rusanu
ALTER DATABASE <dbname> SET RECURSIVE_TRIGGERS OFF
RECURSIVE_TRIGGERS { ON | OFF }
ONRecursive firing of AFTER triggers is allowed.
OFFOnly direct recursive firing of AFTER triggers is not allowed. To also disable indirect recursion of AFTER triggers, set the nested triggers server option to 0 by using sp_configure.
Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. To disable indirect recursion, you must also set the nested triggers server option to 0.
The status of this option can be determined by examining the is_recursive_triggers_on column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.
RECURSIVE_TRIGGERS { ON | 离开 }
ON允许递归触发 AFTER 触发器。
OFF只允许直接递归触发 AFTER 触发器。要同时禁用 AFTER 触发器的间接递归，请使用 sp_configure 将嵌套触发器服务器选项设置为 0。
当 RECURSIVE_TRIGGERS 设置为 OFF 时，仅防止直接递归。要禁用间接递归，您还必须将嵌套触发器服务器选项设置为 0。
此选项的状态可以通过检查 sys.databases 目录视图中的 is_recursive_triggers_on 列或 DATABASEPROPERTYEX 函数的 IsRecursiveTriggersEnabled 属性来确定。
回答by Colin
TRIGGER_NESTLEVELcan be used to prevent recursion of a specific trigger, but it is important to pass the object id of the trigger into the function. Otherwise you will also prevent the trigger from firing when an insert or update is made by another trigger:
TRIGGER_NESTLEVEL可用于防止特定触发器的递归，但重要的是将触发器的对象 id 传递到函数中。否则，当另一个触发器进行插入或更新时，您还将阻止触发器触发：
IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.mytrigger')) > 1
BEGIN
PRINT 'mytrigger exiting because TRIGGER_NESTLEVEL > 1 ';
RETURN;
END;
From MSDN:
从MSDN：
When no parameters are specified, TRIGGER_NESTLEVEL returns the total number of triggers on the call stack. This includes itself.
当没有指定参数时，TRIGGER_NESTLEVEL 返回调用堆栈上的触发器总数。这包括它自己。
Reference: Avoiding recursive triggers
参考： 避免递归触发器
回答by Pure.Krome
I think i got it :)
我想我明白了:)
When the title is getting 'updated' (read: inserted or updated), then update the unique subject. When the trigger gets ran a second time, the uniquesubject field is getting updated, so it stop and leaves the trigger.
当标题“更新”（阅读：插入或更新）时，然后更新唯一主题。当触发器第二次运行时，uniquesubject 字段正在更新，因此它会停止并离开触发器。
Also, i've made it handle MULTIPLE rows that get changed -> I always forget about this with triggers.
另外，我已经让它处理了被改变的多行 - >我总是用触发器忘记这一点。
ALTER TRIGGER [dbo].[tblMediaAfterInsert]
ON [dbo].[tblMedia]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- If the Title is getting inserted OR updated then update the unique subject.
IF UPDATE(Title) BEGIN
-- Now update all the unique subject fields that have been inserted or updated.
UPDATE tblMedia
SET UniqueTitle = dbo.CreateUniqueSubject(b.Title) +
CAST((b.IdMedia) AS VARCHAR(10))
FROM tblMedia a
INNER JOIN INSERTED b on a.IdMedia = b.IdMedia
END
END
回答by DVK
You can have a separate NULLABLE column indicating whether the UniqueTitle was set.
您可以有一个单独的 NULLABLE 列来指示是否设置了 UniqueTitle。
Set it to true value in a trigger, and have the trigger do nothing if it's value is true in "INSERTED"
在触发器中将其设置为 true 值，如果“INSERTED”中的值为 true，则触发器不执行任何操作
回答by HLGEM
For completeness sake, I will add a few things. If you have a particular after trigger that you only want to run once, you can set it up to run last using sp_settriggerorder.
为了完整起见，我将添加一些内容。如果您有一个只想运行一次的特定后触发器，您可以使用 sp_settriggerorder 将其设置为最后运行。
I would also consider if it might not be best to combine the triggers that are doing the recursion into one trigger.
我还会考虑是否最好将执行递归的触发器组合成一个触发器。
