SQL Server 如何防止数据库触发器递归?

mzaanser  于 2022-11-21  发布在  其他
关注(0)|答案(7)|浏览(234)

我在SQL Server 2008数据库的数据表上有下列触发程序。它是递归的,所以我必须停止它。
在插入或更新记录后,我尝试只更新该表中的单个字段。
这是触发器:

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

谁能告诉我如何防止扳机的插入物再次触发另一个扳机?

qojgxg4l

qojgxg4l1#

不确定它是否与OP的问题相关,但是如果你来这里是为了找出如何防止递归或相互递归在触发器中发生,你可以这样测试:

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

MSDN第一版

ryhaxcpt

ryhaxcpt2#

我看到三种可能性:
1.禁用触发器递归
这将防止触发器调用另一个触发器或再次调用自身。为此,请执行以下命令:

ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF
GO

1.使用触发器代替UPDATE、INSERT
使用INSTEAD OF触发器,您可以控制任何正在更新/插入的列,甚至可以在调用命令之前进行替换。
1.通过防止使用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
sdnqo3pr

sdnqo3pr3#

TRIGGER_NESTLEVEL可用于防止特定触发器的递归,但将触发器的对象ID传递到函数中很重要。否则,当另一个触发器执行插入或更新操作时,您也会阻止触发器触发:

IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.mytrigger')) > 1
         BEGIN
             PRINT 'mytrigger exiting because TRIGGER_NESTLEVEL > 1 ';
             RETURN;
     END;

MSDN开始:
未指定参数时,TRIGGER_NESTLEVEL会传回堆栈回退上的触发程序总数。这包括它本身。
参考:Avoiding recursive triggers

ekqde3dh

ekqde3dh4#

ALTER DATABASE <dbname> SET RECURSIVE_TRIGGERS OFF

RECURSIVE_TRIGGERS { ON | OFF }

ON允许递归引发AFTER触发程序。
OFF只允许直接递归引发AFTER触发程序。若要同时停用AFTER触发程序的间接递归,请使用sp_configure将巢状触发程序服务器选项设定为0。

当RECURSIVE_TRIGGERS设置为OFF时,只禁止直接递归。若要禁用间接递归,还必须将嵌套触发器服务器选项设置为0。
这个选项的状态可以借由检查sys.databases目录检视中的is_recursive_triggers_on数据行或ASEPROPERTYEX函数的IsRecursiveTriggersEnabled属性来判断。

cnwbcb6i

cnwbcb6i5#

我 想 我 明白 了 : )
当 标题 被 " 更新 " 时 ( 阅读 :当 触发 器 第 二 次 运行 时 , uniquessubject 字段 被 更新 , 因此 它 停止 并 离开 触发 器 。
此外 , 我 已经 使 它 处理 多 行 被 更改 - 〉 我 总是 忘记 这 与 触发 器 。

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

中 的 每 一 个

eit6fx6z

eit6fx6z6#

您可以使用单独的NULLABLE列来指示是否设置了UniqueTitle。
在触发器中将其设置为true值,如果在“INSERTED”中其值为true,则触发器不执行任何操作

bqjvbblv

bqjvbblv7#

如果您有一个特定的after触发器,并且只想运行一次,那么可以使用sp_settriggerorder将其设置为最后运行。
我还会考虑将执行递归的触发器合并为一个触发器是否是最好的做法。

相关问题