如何避免SQL Server中表批量删除时触发器并发执行导致的重复数据问题

Hey there, I’ve run into this exact duplicate insertion issue with SQL Server triggers before—let’s fix this properly. The root of your problem is twofold: first, your trigger is processing every row in the Deleted table (even 10 identical TId=1 rows) instead of unique TIds, and second, the separate IF EXISTS check plus insert creates a race condition where multiple trigger executions can all try to insert the same TId before any insert finishes.

The best fix here is to use SQL Server's MERGE statement, which handles update/insert operations in a single atomic step, and adds deduplication for repeated TIds in the Deleted table.

Modified Trigger Code #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg1] ON [dbo].[TABLE2]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Get only unique TIds from Deleted to avoid redundant processing
    WITH UniqueDeletedTIds AS (
        SELECT DISTINCT TId 
        FROM Deleted 
        WHERE TId IS NOT NULL
    )
    -- Atomically update existing records or insert new ones
    MERGE INTO [dbo].[Table3] AS TargetTable
    USING UniqueDeletedTIds AS SourceTable
        ON TargetTable.TId = SourceTable.TId
    WHEN MATCHED THEN
        UPDATE SET C32 = 1  -- Update if TId already exists in Table3
    WHEN NOT MATCHED THEN
        INSERT (TId, C32, C33)
        VALUES (SourceTable.TId, 3, GETUTCDATE());  -- Insert only if TId is missing

    SET NOCOUNT OFF;
END
GO
PRINT '';
PRINT 'End of script';
PRINT ' --- // ---';

Key Improvements Explained #

• Atomic MERGE Operation: Unlike your original nested IF checks, MERGE runs as a single atomic statement. This locks the necessary resources during execution, eliminating the race condition where multiple trigger instances could all pass the "TId doesn't exist" check before any insert completes.
• Deduplicate TIds: The CTE UniqueDeletedTIds filters out duplicate TIds from the Deleted table. Even if you delete 10 rows with the same TId, we only process that TId once—no more duplicate inserts.
• Simplified, Maintainable Logic: The nested conditionals are gone, making the trigger easier to read, debug, and modify later.

Quick Notes to Keep in Mind #

• Primary Key Check: Since Table3 uses TId as its primary key, the MERGE statement will enforce uniqueness automatically (you won’t get duplicate rows even if something slips past the deduplication step).
• Statement-Level Trigger: Remember SQL Server triggers run once per statement, not per row. This is why deduplicating TIds is critical—your original trigger would have tried to insert 10 rows for the same TId in one go.
• Performance: SET NOCOUNT ON suppresses unnecessary row-count messages, which helps keep the trigger running efficiently.

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