如何防止数据库触发器递归？ [英] How do I prevent a database trigger from recursing?

问题描述

在SQL Server 2008数据库的表上有以下触发器。它是递归的，所以我需要停止它。

I've got the following trigger on a table for a SQL Server 2008 database. It's recursing, so I need to stop it.

在插入或更新记录后，我试图仅更新该表上的单个字段。

After I insert or update a record, I'm trying to simply update a single field on that table.

以下是触发条件：

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

有人可以告诉我如何防止触发器的插入再次触发另一个触发器吗？ / p>

Can anyone tell me how I can prevent the trigger's insert from kicking off another trigger again?

推荐答案

不知道它是否与OP的问题有关，但是如果您来这里了解如何防止递归或在触发器中发生相互递归，您可以像这样进行测试：

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:

IF TRIGGER_NESTLEVEL() <= 1/*this update is not coming from some other trigger*/

MSDN 链接

这篇关于如何防止数据库触发器递归？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！