善用觸發器實作 SQL Server 資料異動備份

前言：資料備份的重要性

歷經論壇被駭、資料遺失的教訓後，深刻體認到資料備份的關鍵性。雖然市面上有許多備份工具，但透過資料庫內建的 觸發器 實作即時異動備份，是一種低成本、高即時性的補充方案。

設計概念

採用 冗餘備份表 架構：主表負責日常操作，備份表記錄每一次 INSERT 與 UPDATE 的歷史快照。雖會增加儲存空間，卻能確保資料可追溯、可回溯。

實作範例

1. 建立測試資料表

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

-- 備份表：新增 bid 關聯主表 id、active 標記目前有效版本
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' 表歷史版本
);

2. 建立異動觸發器

CREATE TRIGGER trg_test3_bak_ins_upd
ON test3
FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @id INT, @uname VARCHAR(20), @uage INT;

    -- 從 inserted 虛擬表取得異動資料
    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;

3. 測試資料操作

-- 新增測試資料
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;

4. 驗證備份結果

SELECT * FROM test3;        -- 目前主表資料
SELECT * FROM test3_bak;    -- 完整異動歷程

進階考量

• 批次異動處理：上述範例僅處理單筆異動，實務上應使用 INSERTED 資料表搭配 JOIN 或 CURSOR 處理多筆資料。
• 刪除操作：若需記錄刪除，請加入 FOR DELETE 並從 DELETED 虛擬表取值。
• 空間管理：定期封存或清理過舊的備份資料，避免備份表無限增長。

結語

觸發器備份雖非完整災難復原方案的替代品，但能提供細緻的資料異動審計軌跡，是資料安全防禦體系中不可或缺的一環。