如何使Trigger2仅响应用户操作，忽略Trigger1触发的执行？

当前Trigger1会在用户更新行时自动更新UpdateDate、UpdatedByHostName、UpdatedByLogin三列，这一操作会二次触发Trigger2。以下是两种可行方案，让Trigger2仅对用户的原始操作做出响应，忽略Trigger1引发的触发。

方案一：使用CONTEXT_INFO标记触发器操作 #

原理 #

在Trigger1执行时设置唯一上下文标记，Trigger2检测到该标记则直接退出，避免重复执行。执行完Trigger1后清除标记，不影响后续正常操作。

修改Trigger1 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trigUpdateStandardTableData_ProductionOrder] 
ON [dbo].[Production Order] 
AFTER UPDATE 
AS 
BEGIN 
    SET NOCOUNT ON 

    -- 设置上下文标记，标记当前为触发器内部操作
    DECLARE @Context VARBINARY(128) = CAST('Trigger1_ProductionOrder' AS VARBINARY(128))
    SET CONTEXT_INFO @Context

    UPDATE [dbo].[Production Order] 
    SET UpdateDate = GETDATE(), 
        UpdatedByHostName = HOST_NAME(), 
        UpdatedByLogin = SUSER_NAME() 
    FROM [dbo].[Production Order] 
    INNER JOIN inserted ii ON [dbo].[Production Order].RowPointer = ii.RowPointer 

    -- 清除上下文标记
    SET CONTEXT_INFO 0x0
END

修改Trigger2 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trigLogChanges_ProductionOrder] 
ON  [dbo].[Production Order]
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN    
    SET NOCOUNT ON
    SET XACT_ABORT ON

    -- 检测到Trigger1的标记则直接退出
    DECLARE @CurrentContext VARBINARY(128) = CONTEXT_INFO()
    IF @CurrentContext = CAST('Trigger1_ProductionOrder' AS VARBINARY(128))
        RETURN

    DECLARE @ChangeBatchID NVARCHAR(36) = NEWID()
    DECLARE @ChangeType NVARCHAR(7) = 'UNKNOWN'
    DECLARE @ChangeDateTime DATETIME2 = GETDATE()

    IF EXISTS(SELECT TOP 1 1 FROM inserted) AND EXISTS(SELECT TOP 1 1 FROM deleted)
    BEGIN    
        SET @ChangeType = 'UPDATE'    
    END

    IF EXISTS(SELECT TOP 1 1 FROM inserted) AND NOT EXISTS(SELECT TOP 1 1 FROM deleted)
    BEGIN    
        SET @ChangeType = 'INSERT'    
    END

    IF NOT EXISTS(SELECT TOP 1 1 FROM inserted) AND EXISTS(SELECT TOP 1 1 FROM deleted)
    BEGIN    
        SET @ChangeType = 'DELETE'    
    END

    BEGIN TRY
        BEGIN TRAN
            IF EXISTS(SELECT TOP 1 1 FROM deleted) -- 处理DELETE或UPDATE操作
            BEGIN               
                -- 写入日志表的逻辑...        
            END

            IF EXISTS(SELECT TOP 1 1 FROM inserted) -- 处理INSERT或UPDATE操作
            BEGIN               
                -- 写入日志表的逻辑...    
            END 
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRAN
        PRINT(ERROR_MESSAGE())
    END CATCH
END

方案二：利用触发器嵌套层级（@@NESTLEVEL） #

原理 #

用户原始操作触发的触发器层级为1，Trigger1内部更新触发的Trigger2属于嵌套层级2。在Trigger2中判断当前层级大于1时直接退出。

修改Trigger2 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trigLogChanges_ProductionOrder] 
ON  [dbo].[Production Order]
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN    
    SET NOCOUNT ON
    SET XACT_ABORT ON

    -- 若为嵌套触发则直接退出
    IF @@NESTLEVEL > 1
        RETURN

    DECLARE @ChangeBatchID NVARCHAR(36) = NEWID()
    DECLARE @ChangeType NVARCHAR(7) = 'UNKNOWN'
    DECLARE @ChangeDateTime DATETIME2 = GETDATE()

    IF EXISTS(SELECT TOP 1 1 FROM inserted) AND EXISTS(SELECT TOP 1 1 FROM deleted)
    BEGIN    
        SET @ChangeType = 'UPDATE'    
    END

    IF EXISTS(SELECT TOP 1 1 FROM inserted) AND NOT EXISTS(SELECT TOP 1 1 FROM deleted)
    BEGIN    
        SET @ChangeType = 'INSERT'    
    END

    IF NOT EXISTS(SELECT TOP 1 1 FROM inserted) AND EXISTS(SELECT TOP 1 1 FROM deleted)
    BEGIN    
        SET @ChangeType = 'DELETE'    
    END

    BEGIN TRY
        BEGIN TRAN
            IF EXISTS(SELECT TOP 1 1 FROM deleted) -- 处理DELETE或UPDATE操作
            BEGIN               
                -- 写入日志表的逻辑...        
            END

            IF EXISTS(SELECT TOP 1 1 FROM inserted) -- 处理INSERT或UPDATE操作
            BEGIN               
                -- 写入日志表的逻辑...    
            END 
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRAN
        PRINT(ERROR_MESSAGE())
    END CATCH
END

注意事项 #

• 该方案仅适用于当前只有两层触发的场景，若后续新增其他嵌套触发器，可能会误阻止正常触发。
• 相比方案一，实现更简单但灵活性稍差。

额外优化：减少Trigger1的不必要触发 #

可以修改Trigger1逻辑，仅当非跟踪列被修改时才执行更新，避免自身循环触发：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trigUpdateStandardTableData_ProductionOrder] 
ON [dbo].[Production Order] 
AFTER UPDATE 
AS 
BEGIN 
    SET NOCOUNT ON 

    -- 仅当非跟踪列被修改时执行更新
    IF NOT UPDATE(UpdateDate) OR NOT UPDATE(UpdatedByHostName) OR NOT UPDATE(UpdatedByLogin)
    BEGIN
        DECLARE @Context VARBINARY(128) = CAST('Trigger1_ProductionOrder' AS VARBINARY(128))
        SET CONTEXT_INFO @Context

        UPDATE [dbo].[Production Order] 
        SET UpdateDate = GETDATE(), 
            UpdatedByHostName = HOST_NAME(), 
            UpdatedByLogin = SUSER_NAME() 
        FROM [dbo].[Production Order] 
        INNER JOIN inserted ii ON [dbo].[Production Order].RowPointer = ii.RowPointer 

        SET CONTEXT_INFO 0x0
    END
END

内容的提问来源于stack exchange，提问作者TheMortiestMorty