同表递归触发器仅首次触发，后续更新无响应问题求助

递归更新过程中，触发器仅首次触发后便停止工作。现有一张具备父子关系的表，创建了INSTEAD OF触发器（用于写入数据前处理值）和AFTER触发器（用于递归更新被修改记录的所有子记录）。更新顶层记录时，首次触发器正常触发，但子记录更新时触发器不再触发，不过子记录确实被修改了。已确认nested triggers和server trigger recursion配置均已开启。

表结构 #

DROP TABLE IF EXISTS [dbo].[TRIGGERTEST];
GO

CREATE TABLE [dbo].[TRIGGERTEST]
(
    [CODE] varchar(30) NOT Null,
    [PARENT] varchar(30) Null,
    [COLUMN1] varchar(30) NOT Null,
    [COLUMN2] varchar(30) NOT Null,
    [UPDATECOUNT] int DEFAULT 0 NOT Null,
    [SQLIDENTITY] uniqueidentifier 
        CONSTRAINT [DF_TRIGGERTEST_SQLIDENTITY] DEFAULT NEWSEQUENTIALID() NOT NULL

    CONSTRAINT [PRIK_TRIGGERTEST] PRIMARY KEY CLUSTERED
    (
        [CODE] ASC
    )
    WITH ( IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) 
);
GO

INSTEAD OF触发器（写入前处理值） #

CREATE OR ALTER TRIGGER [dbo].[TR0_TRIGGERTEST]
ON [dbo].[TRIGGERTEST]
INSTEAD OF INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
    IF ROWCOUNT_BIG() = 0
        RETURN;

    SET XACT_ABORT, NOCOUNT ON;

    DECLARE @NewCODE varchar(30);
    DECLARE @NewPARENT varchar(30);
    DECLARE @NewCOLUMN1 varchar(30);
    DECLARE @NewCOLUMN2 varchar(30);
    DECLARE @NewUPDATECOUNT int;
    DECLARE @NewSQLIDENTITY uniqueidentifier;
    DECLARE @OldCODE varchar(30);
    DECLARE @OldPARENT varchar(30);
    DECLARE @OldCOLUMN1 varchar(30);
    DECLARE @OldCOLUMN2 varchar(30);
    DECLARE @OldUPDATECOUNT int;
    DECLARE @OldSQLIDENTITY uniqueidentifier;

    IF EXISTS ( SELECT Null FROM inserted ) AND NOT EXISTS ( SELECT Null FROM deleted )
    BEGIN
        DECLARE c_primary CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR 
            SELECT i.[CODE], i.[PARENT], i.[COLUMN1], i.[COLUMN2], i.[UPDATECOUNT], i.[SQLIDENTITY]
            FROM inserted i;

        OPEN c_primary;

        FETCH NEXT FROM c_primary INTO @NewCODE, @NewPARENT, @NewCOLUMN1, @NewCOLUMN2, @NewUPDATECOUNT, @NewSQLIDENTITY;
    END
    ELSE IF EXISTS ( SELECT Null FROM inserted ) AND EXISTS ( SELECT Null FROM deleted )
    BEGIN
        DECLARE c_primary CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR 
            SELECT i.[CODE], d.[CODE], i.[PARENT], d.[PARENT], i.[COLUMN1], d.[COLUMN1], i.[COLUMN2], d.[COLUMN2], i.[UPDATECOUNT], d.[UPDATECOUNT], i.[SQLIDENTITY], d.[SQLIDENTITY]
            FROM inserted i, deleted d
            WHERE i.[SQLIDENTITY] = d.[SQLIDENTITY];

        OPEN c_primary;

        FETCH NEXT FROM c_primary INTO @NewCODE, @OldCODE, @NewPARENT, @OldPARENT, @NewCOLUMN1, @OldCOLUMN1, @NewCOLUMN2, @OldCOLUMN2, @NewUPDATECOUNT, @OldUPDATECOUNT, @NewSQLIDENTITY, @OldSQLIDENTITY;
    END
    ELSE RETURN;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF EXISTS ( SELECT Null FROM inserted ) AND NOT EXISTS ( SELECT Null FROM deleted )
        BEGIN
            SELECT [Source] = N'PreInsert.1 inserted', * FROM inserted WHERE [COLUMN1] = @NewCOLUMN1;
            SELECT [Source] = N'PreInsert.1 values', [@NewCODE] = @NewCODE, [@NewPARENT] = @NewPARENT, [@NewCOLUMN1] = @NewCOLUMN1, [@NewCOLUMN2] = @NewCOLUMN2, [@NewUPDATECOUNT] = @NewUPDATECOUNT, [@NewSQLIDENTITY] = @NewSQLIDENTITY;

            SET @NewCOLUMN2 = CONCAT( @NewCOLUMN2, N'x' );

            INSERT INTO [dbo].[TRIGGERTEST]
            ( [CODE], [PARENT], [COLUMN1], [COLUMN2], [SQLIDENTITY] )
            VALUES
            ( @NewCODE, @NewPARENT, @NewCOLUMN1, @NewCOLUMN2, @NewSQLIDENTITY );

            SELECT [Source] = N'PreInsert.2 inserted', * FROM inserted WHERE [COLUMN1] = @NewCOLUMN1;
            SELECT [Source] = N'PreInsert.2 table', * FROM [dbo].[TRIGGERTEST] WHERE [COLUMN1] = @NewCOLUMN1;

            FETCH NEXT FROM c_primary INTO @NewCODE, @NewPARENT, @NewCOLUMN1, @NewCOLUMN2, @NewUPDATECOUNT, @NewSQLIDENTITY;
        END
        ELSE IF EXISTS ( SELECT Null FROM inserted ) AND EXISTS ( SELECT Null FROM deleted )
        BEGIN
            SELECT [Source] = N'PreUpdate.1 inserted', * FROM inserted WHERE [COLUMN1] = @NewCOLUMN1;
            SELECT [Source] = N'PreUpdate.1 values', [@NewCODE] = @NewCODE, [@NewPARENT] = @NewPARENT, [@NewCOLUMN1] = @NewCOLUMN1, [@NewCOLUMN2] = @NewCOLUMN2, [@NewUPDATECOUNT] = @NewUPDATECOUNT, [@NewSQLIDENTITY] = @NewSQLIDENTITY;

            UPDATE [dbo].[TRIGGERTEST]
            SET [PARENT] = @NewPARENT
            , [COLUMN1] = @NewCOLUMN1
            , [COLUMN2] = @NewCOLUMN2
            , [UPDATECOUNT] += 1
            WHERE [SQLIDENTITY] = @NewSQLIDENTITY;

            SELECT [Source] = N'PreUpdate.2 inserted', * FROM inserted WHERE [COLUMN1] = @NewCOLUMN1;
            SELECT [Source] = N'PreUpdate.2 table', * FROM [dbo].[TRIGGERTEST] WHERE [COLUMN1] = @NewCOLUMN1;
        
            FETCH NEXT FROM c_primary INTO @NewCODE, @OldCODE, @NewPARENT, @OldPARENT, @NewCOLUMN1, @OldCOLUMN1, @NewCOLUMN2, @OldCOLUMN2, @NewUPDATECOUNT, @OldUPDATECOUNT, @NewSQLIDENTITY, @OldSQLIDENTITY;
        END
    END

    CLOSE c_primary;
    DEALLOCATE c_primary;
END
GO

AFTER触发器（递归更新子记录） #

CREATE OR ALTER TRIGGER [dbo].[TR1_TRIGGERTEST]
ON [dbo].[TRIGGERTEST]
AFTER INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
    IF ROWCOUNT_BIG() = 0
        RETURN;

    SET XACT_ABORT, NOCOUNT ON;

    DECLARE @NewCODE varchar(30);
    DECLARE @NewPARENT varchar(30);
    DECLARE @NewCOLUMN1 varchar(30);
    DECLARE @NewCOLUMN2 varchar(30);
    DECLARE @NewUPDATECOUNT int;
    DECLARE @NewSQLIDENTITY uniqueidentifier;
    DECLARE @OldCODE varchar(30);
    DECLARE @OldPARENT varchar(30);
    DECLARE @OldCOLUMN1 varchar(30);
    DECLARE @OldCOLUMN2 varchar(30);
    DECLARE @OldUPDATECOUNT int;
    DECLARE @OldSQLIDENTITY uniqueidentifier;

    IF EXISTS ( SELECT Null FROM inserted ) AND NOT EXISTS ( SELECT Null FROM deleted )
    BEGIN
        DECLARE c_primary CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR 
            SELECT i.[CODE], i.[PARENT], i.[COLUMN1], i.[COLUMN2], i.[UPDATECOUNT], i.[SQLIDENTITY]
            FROM inserted i;

        OPEN c_primary;

        FETCH NEXT FROM c_primary INTO @NewCODE, @NewPARENT, @NewCOLUMN1, @NewCOLUMN2, @NewUPDATECOUNT, @NewSQLIDENTITY;
    END
    ELSE IF EXISTS ( SELECT Null FROM inserted ) AND EXISTS ( SELECT Null FROM deleted )
    BEGIN
        DECLARE c_primary CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR 
            SELECT i.[CODE], d.[CODE], i.[PARENT], d.[PARENT], i.[COLUMN1], d.[COLUMN1], i.[COLUMN2], d.[COLUMN2], i.[UPDATECOUNT], d.[UPDATECOUNT], i.[SQLIDENTITY], d.[SQLIDENTITY]
            FROM inserted i, deleted d
            WHERE i.[SQLIDENTITY] = d.[SQLIDENTITY];

        OPEN c_primary;

        FETCH NEXT FROM c_primary INTO @NewCODE, @OldCODE, @NewPARENT, @OldPARENT, @NewCOLUMN1, @OldCOLUMN1, @NewCOLUMN2, @OldCOLUMN2, @NewUPDATECOUNT, @OldUPDATECOUNT, @NewSQLIDENTITY, @OldSQLIDENTITY;
    END
    ELSE RETURN;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF EXISTS ( SELECT Null FROM inserted ) AND NOT EXISTS ( SELECT Null FROM deleted )
        BEGIN
            SELECT [Source] = N'PostInsert inserted', * FROM inserted WHERE [COLUMN1] = @NewCOLUMN1;
            SELECT [Source] = N'PostInsert table', * FROM [dbo].[TRIGGERTEST] WHERE [COLUMN1] = @NewCOLUMN1;

            FETCH NEXT FROM c_primary INTO @NewCODE, @NewPARENT, @NewCOLUMN1, @NewCOLUMN2, @NewUPDATECOUNT, @NewSQLIDENTITY;
        END
        ELSE IF EXISTS ( SELECT Null FROM inserted ) AND EXISTS ( SELECT Null FROM deleted )
        BEGIN
            SELECT [Source] = N'PostUpdate inserted', * FROM inserted WHERE [COLUMN1] = @NewCOLUMN1;
            SELECT [Source] = N'PostUpdate deleted', * FROM deleted WHERE [COLUMN1] = @NewCOLUMN1;
            SELECT [Source] = N'PostUpdate table', * FROM [dbo].[TRIGGERTEST] WHERE [COLUMN1] = @NewCOLUMN1;

            UPDATE [dbo].[TRIGGERTEST]
            SET [COLUMN2] = CONCAT( [COLUMN2], N'y' )
            WHERE [PARENT] = @NewCODE;

            FETCH NEXT FROM c_primary INTO @NewCODE, @OldCODE, @NewPARENT, @OldPARENT, @NewCOLUMN1, @OldCOLUMN1, @NewCOLUMN2, @OldCOLUMN2, @NewUPDATECOUNT, @OldUPDATECOUNT, @NewSQLIDENTITY, @OldSQLIDENTITY;
        END
    END

    CLOSE c_primary;
    DEALLOCATE c_primary;
END
GO

测试数据插入 #

SELECT N'Insert statement against TABLE';
GO

DELETE FROM [dbo].[TRIGGERTEST];
INSERT INTO [dbo].[TRIGGERTEST]
( [CODE], [PARENT], [COLUMN1], [COLUMN2] )
SELECT N'Level 1', Null, N'Value 1', N'Value 1' UNION ALL
SELECT N'Level 2', N'Level 1', N'Value 2', N'Value 2' UNION ALL
SELECT N'Level 3', N'Level 2', N'Value 3', N'Value 3' UNION ALL
SELECT N'Level 4', N'Level 3', N'Value 4', N'Value 4' UNION ALL
SELECT N'Level 5', N'Level 4', N'Value 5', N'Value 5';

SELECT [Source] = N'Table SELECT', * FROM [dbo].[TRIGGERTEST];
GO

更新操作 #

SELECT N'Update statement against TABLE';
GO

UPDATE [dbo].[TRIGGERTEST]
SET [COLUMN2] = CONCAT( [COLUMN2], N'y' )
WHERE [CODE] = N'Level 1';

SELECT [Source] = N'Table SELECT', * FROM [dbo].[TRIGGERTEST];
GO

配置检查 #

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'nested triggers', 1;  
GO  
RECONFIGURE;
GO
EXEC sp_configure 'server trigger recursion', 1;  
GO  
RECONFIGURE;
GO

-- 配置结果
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'nested triggers' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'server trigger recursion' changed from 1 to 1. Run the RECONFIGURE statement to install.

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