SQL Server 觸發器實現多表數據同步：架構限制與最佳實踐方案

問題場景

需求：A 表、B 表數據變化時，自動在 C 表中體現。提問者希望單個觸發器監聽兩表變化。

核心限制：觸發器綁定單表

SQL Server 觸發器本質：

• 定義在特定表上（ON TableName）
• 僅響應該表的 DML 操作（INSERT/UPDATE/DELETE）
• 無法跨表觸發：A 表觸發器無法感知 B 表變化，反之亦然

正確架構：分表建立觸發器

-- A 表觸發器
CREATE TRIGGER tr_A_SyncC ON A
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- 邏輯：根據 inserted/deleted 更新 C 表
    MERGE C AS target
    USING (SELECT key, value FROM inserted) AS source
    ON target.key = source.key
    WHEN MATCHED THEN UPDATE SET target.value = source.value
    WHEN NOT MATCHED THEN INSERT (key, value) VALUES (source.key, source.value);
    -- DELETE 需額外處理 deleted 表
END;

-- B 表觸發器（邏輯類似）
CREATE TRIGGER tr_B_SyncC ON B
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- 同步邏輯
END;

進階同步邏輯：MERGE 統一處理

利用 MERGE 簡化增刪改邏輯：

CREATE TRIGGER tr_A_SyncC ON A
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 同步 INSERT/UPDATE
    MERGE C WITH (HOLDLOCK) AS target
    USING (
        SELECT id, col1, col2, 'A' AS src FROM inserted
        UNION ALL
        SELECT id, col1, col2, 'A' FROM deleted WHERE NOT EXISTS (SELECT 1 FROM inserted WHERE inserted.id = deleted.id)
    ) AS source ON target.id = source.id AND target.src = source.src
    WHEN MATCHED AND EXISTS (SELECT 1 FROM inserted WHERE inserted.id = source.id) 
        THEN UPDATE SET target.col1 = source.col1, target.col2 = source.col2
    WHEN NOT MATCHED BY TARGET AND EXISTS (SELECT 1 FROM inserted WHERE inserted.id = source.id)
        THEN INSERT (id, col1, col2, src) VALUES (source.id, source.col1, source.col2, source.src)
    WHEN MATCHED AND NOT EXISTS (SELECT 1 FROM inserted WHERE inserted.id = source.id)
        THEN DELETE;
END;

替代方案對比

觸發器開發鐵律

1. SET NOCOUNT ON：減少網絡往返
2. 處理多行：inserted/deleted 可能包含多行
3. 避免事務：觸發器隱式在觸發語句事務中，顯式事務易死鎖
4. 防遞歸：IF TRIGGER_NESTLEVEL() > 1 RETURN;
5. 錯誤處理：TRY...CATCH 記錄日誌，不拋出中斷主操作
6. 性能考慮：避免游標、複雜查詢；必要時用 AFTER 而非 INSTEAD OF

總結

SQL Server 觸發器天然綁定單表，無法實現「單觸發器監聽多表」。正確做法是在 A、B 表上各建一個觸發器，內部邏輯更新 C 表。若同步邏輯複雜或涉及高併發，建議評估 CDC、Service Broker 或應用層方案。觸發器雖強大，但屬於「隱式邏輯」，濫用會導致維護噩夢，務必遵循「簡單、單一職責、可觀測」原則。