利用觸發器實現 SQL Server 資料異動備份

建立測試環境

-- 來源資料表
CREATE TABLE test3 (
    id INT PRIMARY KEY IDENTITY(1,1),
    uname VARCHAR(20),
    uage INT
);

-- 備份資料表，含異動標記
CREATE TABLE test3_bak (
    id INT PRIMARY KEY IDENTITY(1,1),
    bid INT,            -- 對應來源 id
    uname VARCHAR(20),
    uage INT,
    active CHAR(1)      -- '1' 為最新版本，'0' 為歷史版本
);

建立 INSERT/UPDATE 觸發器

CREATE TRIGGER trg_test3_bak_iu
ON test3
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    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;  -- 完整異動歷程

擴充建議

• 支援多列異動：直接使用 inserted 資料表聯結 UPDATE/INSERT，避免逐列宣告變數。
• 加入 DELETED 處理：若需記錄刪除操作，擴充 AFTER DELETE 觸發器。
• 加入時間戳記欄位 mod_dt DATETIME DEFAULT GETDATE() 於備份表，利於稽核。
• 大量資料異動時考慮使用 OUTPUT 子句或 CDC (Change Data Capture) 取代觸發器，效能更佳。