SQL Server触发器如何高效实现跨库同步及操作日志记录？

问题背景 #

需要实现跨库数据同步触发器，核心逻辑为：对源库的每条变更记录，判断目标库是否存在对应记录——存在则执行UPDATE，不存在则执行INSERT并记录插入日志；同时需捕获操作错误并写入日志。

此前尝试的方案均存在缺陷：

• 游标实现：逐条处理能满足逻辑，但性能极差
• 动态SQL拼接：将每条记录的TRY/CATCH逻辑拼串执行，效率更低
• MERGE语句：考虑过但OUTPUT子句无法获取@@IDENTITY等值，也无法追踪单条记录的错误

最优实现方案：MERGE + 批量处理 + 错误捕获 #

1. 基础版：MERGE批量同步+OUTPUT记录操作日志 #

利用MERGE完成批量INSERT/UPDATE，通过OUTPUT子句记录操作详情，再批量写入插入日志。这种方式兼顾性能和日志需求，适合不需要单条错误捕获的场景。

SET XACT_ABORT ON; -- 确保事务出错时自动回滚
BEGIN TRANSACTION;

-- 创建临时表存储MERGE操作结果
CREATE TABLE #MergeResults (
    ActionType NVARCHAR(10),
    SourceID INT, -- 源表的主键ID
    TargetID INT -- 目标表自增ID（若有）
);

-- 执行跨库MERGE同步
MERGE INTO db2.dbo.TargetTable AS T
USING INSERTED AS S
ON T.MatchKey = S.MatchKey -- 替换为实际的匹配字段（如唯一键）
WHEN MATCHED THEN
    UPDATE SET T.fld = S.fld -- 替换为实际需要更新的字段
WHEN NOT MATCHED THEN
    INSERT (fld) VALUES (S.fld) -- 替换为实际需要插入的字段
OUTPUT 
    $action AS ActionType,
    S.ID AS SourceID,
    INSERTED.ID AS TargetID -- 目标表自增ID通过INSERTED获取
INTO #MergeResults;

-- 批量写入插入日志：仅记录INSERT操作的记录
INSERT INTO dbo.log (text)
SELECT 'row inserted blabla ' + CAST(SourceID AS VARCHAR(20))
FROM #MergeResults
WHERE ActionType = 'INSERT';

COMMIT TRANSACTION;

2. 进阶版：分批MERGE+TRY/CATCH实现部分错误捕获 #

如果需要在部分记录出错时，保证其他记录正常同步并捕获错误，可将INSERTED记录分批处理，每批单独执行MERGE并包裹TRY/CATCH，既保留批量性能，又能捕获批次级错误。

DECLARE @BatchSize INT = 1000; -- 可根据实际性能调整批次大小
DECLARE @StartID INT, @EndID INT;

-- 获取INSERTED记录的主键范围（假设源表ID为自增主键）
SELECT @StartID = MIN(ID), @EndID = MAX(ID) FROM INSERTED;

WHILE @StartID <= @EndID
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        CREATE TABLE #BatchMergeResults (
            ActionType NVARCHAR(10),
            SourceID INT,
            TargetID INT
        );

        -- 针对当前批次执行MERGE
        MERGE INTO db2.dbo.TargetTable AS T
        USING (
            SELECT * FROM INSERTED 
            WHERE ID BETWEEN @StartID AND @StartID + @BatchSize - 1
        ) AS S
        ON T.MatchKey = S.MatchKey
        WHEN MATCHED THEN
            UPDATE SET T.fld = S.fld
        WHEN NOT MATCHED THEN
            INSERT (fld) VALUES (S.fld)
        OUTPUT $action, S.ID, INSERTED.ID INTO #BatchMergeResults;

        -- 写入当前批次的插入日志
        INSERT INTO dbo.log (text)
        SELECT 'row inserted blabla ' + CAST(SourceID AS VARCHAR(20))
        FROM #BatchMergeResults
        WHERE ActionType = 'INSERT';

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- 记录当前批次的错误日志
        INSERT INTO dbo.log (text)
        VALUES ('Batch failed blabla: ' + ERROR_MESSAGE() + ' (ID range: ' + CAST(@StartID AS VARCHAR(20)) + ' to ' + CAST(@StartID + @BatchSize - 1 AS VARCHAR(20)) + ')');
    END CATCH

    SET @StartID = @StartID + @BatchSize;
END

3. 极致版：表值参数+存储过程实现单条错误捕获 #

如果必须捕获单条记录的错误，可将INSERTED记录通过表值参数传入存储过程，在存储过程中用轻量游标逐行处理并单独TRY/CATCH。这种方式性能略低于MERGE，但能满足细粒度错误追踪需求。

-- 1. 创建表值类型
CREATE TYPE SyncTableType AS TABLE (ID INT, fld VARCHAR(50)); -- 匹配源表字段

-- 2. 创建同步存储过程
CREATE PROCEDURE dbo.SyncToDB2
    @SyncData SyncTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ID INT, @fld VARCHAR(50);

    -- 声明轻量游标
    DECLARE sync_cursor CURSOR FAST_FORWARD FOR
        SELECT ID, fld FROM @SyncData;

    OPEN sync_cursor;
    FETCH NEXT FROM sync_cursor INTO @ID, @fld;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            IF EXISTS(SELECT 1 FROM db2.dbo.TargetTable WHERE MatchKey = @ID)
            BEGIN
                UPDATE db2.dbo.TargetTable SET fld = @fld WHERE MatchKey = @ID;
            END
            ELSE
            BEGIN
                INSERT INTO db2.dbo.TargetTable(fld) VALUES (@fld);
                INSERT INTO dbo.log(text) VALUES ('row inserted blabla ' + CAST(@ID AS VARCHAR(20)));
            END

            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION;
            INSERT INTO dbo.log(text) VALUES ('insert/update failed blabla ' + CAST(@ID AS VARCHAR(20)) + ': ' + ERROR_MESSAGE());
        END CATCH

        FETCH NEXT FROM sync_cursor INTO @ID, @fld;
    END

    CLOSE sync_cursor;
    DEALLOCATE sync_cursor;
END

-- 3. 触发器中调用存储过程
CREATE TRIGGER dbo.SourceTable_AfterInsertUpdate
ON dbo.SourceTable
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SyncData SyncTableType;
    INSERT INTO @SyncData(ID, fld) SELECT ID, fld FROM INSERTED;
    EXEC dbo.SyncToDB2 @SyncData;
END

方案选型建议 #

• 优先选基础版MERGE：性能最优，适合对错误容忍度较高、只需记录操作日志的场景
• 需要部分错误捕获选进阶版分批MERGE：平衡性能和错误追踪能力
• 必须单条错误追踪选极致版存储过程+游标：牺牲部分性能满足细粒度需求

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