如何改写SQL触发器以循环处理多记录增删改并调用带参存储过程

The original trigger only processes a single record (TOP 1) during bulk operations, which means most audit entries get lost. Additionally, there's a bug in the INSERT logic where it tries to fetch the primary key from the deleted table (which is empty for insert operations). Here's the corrected version that loops through every record in deleted/inserted and calls SP_Audit for each one:

IF EXISTS (SELECT * FROM MyDB.sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[MyTable_DEL_UPD_INS]')) 
    DROP TRIGGER [dbo].[MyTable_DEL_UPD_INS] 
GO 

CREATE TRIGGER [dbo].[MyTable_DEL_UPD_INS] 
ON [MyDB].[dbo].[MyTable] 
AFTER DELETE, UPDATE, INSERT 
NOT FOR REPLICATION 
AS 
BEGIN 
    SET NOCOUNT ON; -- Prevent extra result sets from interfering with SELECT statements

    DECLARE @PKId INT, @Code VARCHAR(5), @AuditType VARCHAR(10)
    SET @Code = 'TEST'

    -- Handle DELETE operations (only deleted records exist)
    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        DECLARE delete_cursor CURSOR FOR
            SELECT [MyTable_PK] FROM deleted WITH (NOLOCK)
        
        OPEN delete_cursor
        FETCH NEXT FROM delete_cursor INTO @PKId

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @PKId IS NOT NULL AND @Code IS NOT NULL
                EXEC MyDB.[dbo].[SP_Audit] @PKId, @Code, 'DELETE'
            
            FETCH NEXT FROM delete_cursor INTO @PKId
        END

        CLOSE delete_cursor
        DEALLOCATE delete_cursor
    END

    -- Handle UPDATE operations (both deleted and inserted records exist)
    IF EXISTS (SELECT * FROM deleted) AND EXISTS (SELECT * FROM inserted)
    BEGIN
        DECLARE update_cursor CURSOR FOR
            SELECT d.[MyTable_PK] FROM deleted d WITH (NOLOCK)
        
        OPEN update_cursor
        FETCH NEXT FROM update_cursor INTO @PKId

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @PKId IS NOT NULL AND @Code IS NOT NULL
                EXEC MyDB.[dbo].[SP_Audit] @PKId, @Code, 'UPDATE'
            
            FETCH NEXT FROM update_cursor INTO @PKId
        END

        CLOSE update_cursor
        DEALLOCATE update_cursor
    END

    -- Handle INSERT operations (only inserted records exist)
    IF NOT EXISTS (SELECT * FROM deleted) AND EXISTS (SELECT * FROM inserted)
    BEGIN
        DECLARE insert_cursor CURSOR FOR
            SELECT [MyTable_PK] FROM inserted WITH (NOLOCK) -- Fixed: pull PK from inserted, not deleted
        
        OPEN insert_cursor
        FETCH NEXT FROM insert_cursor INTO @PKId

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @PKId IS NOT NULL AND @Code IS NOT NULL
                EXEC MyDB.[dbo].[SP_Audit] @PKId, @Code, 'INSERT'
            
            FETCH NEXT FROM insert_cursor INTO @PKId
        END

        CLOSE insert_cursor
        DEALLOCATE insert_cursor
    END
END 
GO 

ALTER TABLE [MyDB].[dbo].[MyTable] ENABLE TRIGGER [MyTable_DEL_UPD_INS]

Key Improvements: #

• Cursor-based looping: Ensures every record in deleted/inserted is processed, even during bulk operations.
• Fixed INSERT logic: Now correctly pulls the primary key from the inserted table instead of deleted.
• SET NOCOUNT ON: Prevents unnecessary result sets from being returned, which can cause issues with applications calling the trigger.
• Clean cursor management: Each cursor is closed and deallocated after use to avoid resource leaks.

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