用 trigger 自動化任務

想像一下，你是個大型資料庫的管理員。突然有人把某個重要資料從 table 刪掉，大家都在喊：「是誰幹的？！」為了避免這種情況，資料庫都會提供記錄變更、追蹤資料發生什麼事的功能。這些都是靠 log 跟 audit 來搞定的。

• 變更紀錄（log） 讓你可以保存發生過什麼事的歷史：哪一筆資料被改了、怎麼改的、什麼時候改的。
• 資料稽核（audit） 用來做更深入的檢查，不只記錄變更，還會記下是誰（user）發起的。

現在你知道「為什麼」要這樣做了，接下來我們來學「怎麼做」。

建立 log table

在我們開始設定 trigger 之前，先要有個 table 來存變更紀錄。範例如下：

-- 建立變更紀錄的 table
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- 每筆 log 的唯一識別碼
    table_name TEXT NOT NULL,        -- 發生變更的 table 名稱
    operation TEXT NOT NULL,         -- 操作類型：INSERT、UPDATE、DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- 變更時間
    old_data JSONB,                  -- 變更前的資料（for UPDATE/DELETE）
    new_data JSONB                   -- 變更後的資料（for INSERT/UPDATE）
);

這裡發生了什麼？

1. log_id — 每筆 log 的唯一識別碼。
2. table_name — 我們會記下是哪個 table 被改了。
3. operation — 操作類型：INSERT、UPDATE 或 DELETE。
4. change_time — 記錄精確的變更時間。
5. old_data 跟 new_data — 變更前後的資料，用 JSON 格式存。

用 trigger 做變更紀錄

現在我們有 log table 了，來幫某個 table（例如 students）加個 trigger，讓它能記錄所有變更：新增學生、更新或刪除。 我們要做的事：

1. 寫一個 PL/pgSQL function，會把資料寫進 log table。
2. 在 students table 上加 trigger。

這個 function 會拿到操作類型（INSERT、UPDATE、DELETE），還有變更的資料（OLD 跟 NEW）。

-- 寫入變更紀錄的 function
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- INSERT 操作的 log
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- DELETE 操作的 log
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- UPDATE 操作的 log
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- 回傳 NULL，因為這是 AFTER trigger
END;
$$ LANGUAGE plpgsql;

這裡：

• TG_OP — 這是個特別的變數，會存目前的操作類型：INSERT、UPDATE、DELETE。
• row_to_json(OLD) 跟 row_to_json(NEW) — 把資料 row 轉成 JSON，方便存起來。
• RETURN NULL — 因為這是 AFTER trigger，不需要回傳變更後的資料。

現在把我們的 function 跟 students table 綁在一起。

-- 建立變更紀錄 trigger
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

這裡發生了什麼？

• AFTER INSERT OR UPDATE OR DELETE — trigger 會在 students table 做完這些操作後執行。
• FOR EACH ROW — 每一筆被改的資料都會執行一次 trigger。
• EXECUTE FUNCTION log_student_changes() — 呼叫我們剛剛寫的 log function。

測試 trigger

來測試一下 trigger 有沒有正常運作。

1. 插入新資料

INSERT INTO students (name, age, grade)
VALUES ('奧托 林', 20, 'A');

來看看 log table 記了什麼：

SELECT * FROM change_logs;

範例結果：

2. 更新資料

UPDATE students
SET grade = 'B'
WHERE name = '奧托 林';

再查一次 log table：

SELECT * FROM change_logs ORDER BY change_time DESC;

結果：

3. 刪除資料

DELETE FROM students
WHERE name = '奧托 林';

再查一次 log：

實際應用範例

1. 在關鍵 table 上做操作紀錄：像銀行帳戶這種 table，一定要記錄所有變更，防止詐騙。
2. 系統稽核：你可以存紀錄來符合法規，或分析 user 行為。
3. 資料還原：如果有人不小心刪了資料，可以從 log table 還原回來。

注意事項與陷阱

用 trigger 做 log 時，效能很重要。如果 trigger 太常被觸發，會讓資料庫變慢。所以：

• 只在關鍵 table 上做 log。
• 如果 log 太多，要設計歸檔（archive）策略。

trigger 就像吉他的弦一樣：要調得剛剛好，才能彈出好聲音，讓你自動化日常工作又能掌控資料。