逐行插入引发AFTER INSERT触发器重复执行，性能低下求优化方案

问题背景 #

外部系统向test_target_2025_nguyennn表逐行插入数据，导致关联的AFTER INSERT触发器每次插入都执行一次删除并重建test_nguyennn表的操作，反复执行大量IO操作，造成严重性能问题。

原触发器代码 #

USE DRAFT
GO

CREATE OR ALTER TRIGGER trg_recreate_table_trigger_kpi_secured_loan
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    -- Capture event data
    DECLARE @eventData XML = EVENTDATA();
    DECLARE @objectName NVARCHAR(255) = @eventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)');

    -- Check if the created table is 'test_target_2025_nguyennn'
    IF @objectName = 'test_target_2025_nguyennn'
    BEGIN
        PRINT 'Table test_target_2025_nguyennn has been created.';

        -- Dynamically create an AFTER INSERT trigger on the new table
        DECLARE @createTriggerSQL NVARCHAR(MAX) = '
            CREATE OR ALTER TRIGGER trg_after_insert_test_target_2025_nguyennn
            ON test_target_2025_nguyennn
            AFTER INSERT
            AS
            BEGIN
                SET NOCOUNT ON;

                -- Drop and recreate draft..test_nguyennn with inserted data
                BEGIN 
                    IF OBJECT_ID(''draft..test_nguyennn'', ''U'') IS NOT NULL
                    BEGIN
                        DROP TABLE draft..test_nguyennn;
                    END

                    SELECT *
                    INTO draft..test_nguyennn
                    FROM test_target_2025_nguyennn;
                END

            END;'

    END;
END;

解决方案 #

1. 重构触发器逻辑：放弃全表重建，改为增量同步 #

原触发器每次插入都删除重建目标表是性能瓶颈核心，改为只同步新增数据，避免全表操作：

-- 修改后的AFTER INSERT触发器逻辑
CREATE OR ALTER TRIGGER trg_after_insert_test_target_2025_nguyennn
ON test_target_2025_nguyennn
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 初始化目标表（仅第一次执行）
    IF OBJECT_ID('draft..test_nguyennn', 'U') IS NULL
    BEGIN
        SELECT * INTO draft..test_nguyennn FROM test_target_2025_nguyennn;
    END
    ELSE
    BEGIN
        -- 仅插入本次新增数据（替换为实际主键列避免重复）
        INSERT INTO draft..test_nguyennn
        SELECT * FROM inserted
        WHERE NOT EXISTS (
            SELECT 1 FROM draft..test_nguyennn t
            WHERE t.主键列 = inserted.主键列
        );
    END
END;

2. 改用定时同步，替换触发器 #

如果不需要实时同步，用SQL Server代理作业定时执行同步任务，减少执行频率：

-- 定时同步脚本
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('draft..test_nguyennn', 'U') IS NOT NULL
        DROP TABLE draft..test_nguyennn;

    SELECT * INTO draft..test_nguyennn FROM test_target_2025_nguyennn;
END;

通过SQL Server代理创建定时作业，按业务需求设置执行频率即可。

3. 使用视图替代物理复制表 #

如果test_nguyennn仅用于查询源表全量数据，直接创建视图，无需复制或触发器：

CREATE VIEW draft..test_nguyennn
AS
SELECT * FROM test_target_2025_nguyennn;

查询时直接读取源表数据，兼顾性能与实时性。

4. 配置外部系统为批量插入（若可操作） #

如果能修改外部系统逻辑，改为批量插入（如一次插入1000行），即使保留原触发器逻辑，执行次数也会大幅减少，性能显著提升。

内容的提问来源于stack exchange，提问作者Ngọc Nguyễn