SQL Server 觸發器實作資料異動備份：Insert/Update 同步備份表

利用 AFTER INSERT, UPDATE 觸發器，將主表資料異動即時同步至備份表，並以 active 標記區分最新版本與歷史版本。適用於審計追蹤、資料異動記錄、誤刪快速回復場景。

建立測試表與備份表

CREATE TABLE test3 (
    id INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    uname VARCHAR(20),
    uage INT
);

CREATE TABLE test3_bak (
    id INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    bid INT,           -- 對應主表 id
    uname VARCHAR(20),
    uage INT,
    active CHAR(1)     -- '1'=最新版, '0'=歷史版
);

建立觸發器

ALTER TRIGGER test3_bak_insert_update
ON test3
FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @id INT, @uname VARCHAR(20), @uage INT;
    SELECT @id = id, @uname = uname, @uage = uage FROM inserted;

    IF @id <> 0 BEGIN
        -- 將備份表中對應舊記錄標記為歷史版
        UPDATE test3_bak SET active = '0' WHERE bid = @id;
        -- 插入最新版本
        INSERT INTO test3_bak (bid, uname, uage, active)
        VALUES (@id, @uname, @uage, '1');
    END
END;

測試資料與驗證

INSERT INTO test3 (uname, uage) VALUES ('FLB', 20);
INSERT INTO test3 (uname, uage) VALUES ('FLB1', 21);
INSERT INTO test3 (uname, uage) VALUES ('FLB2', 22);
UPDATE test3 SET uage = 100 WHERE id = 2;
DELETE FROM test3 WHERE id = 1;  -- 觸發器不處理 DELETE

SELECT * FROM test3;
SELECT * FROM test3_bak;

結果分析

• test3：僅保留 id=2,3（id=1 已刪除、id=2 已更新）。
• test3_bak：保留完整異動歷程。id=1 為 '0'（歷史）、id=2 有兩筆（舊版 '0'、新版 '1'）、id=3 為 '1'。
• 缺口： 觸發器未處理 DELETE，若需記錄刪除操作，需另建 FOR DELETE 觸發器或改用 FOR INSERT, UPDATE, DELETE 並在 deleted 虛擬表處理。

進階建議

1. 多筆異動處理： 範例假設單筆異動，批次操作時 inserted 含多列，需用 CURSOR 或 SET 運算逐筆處理。
2. 效能考量： 觸發器在交易內執行，大量批次異動會延長交易持有鎖時間，高併發環境建議改用 CDC（Change Data Capture）或 OUTPUT 子句寫入備份表。
3. 刪除記錄： 補充 FOR DELETE 觸發器，從 deleted 取得資料寫入備份表並標記 active='D'。
4. 結構變更同步： 主表 Schema 變更時，需同步修改備份表與觸發器，建議納入資料庫變更管理流程。