觸發器與 PL/pgSQL 函式的互動：OLD、NEW、TG_OP

PostgreSQL 的觸發器不只可以讓你對某些動作自動執行函式，還會把一些超方便的變數傳進去。靠這些變數，你就能知道資料在操作前是什麼、操作後變成什麼，還有到底發生了什麼操作。

• OLD — 包含操作前資料表那一列的舊資料。這個只會在 UPDATE 跟 DELETE 的觸發器用得到，因為 INSERT 根本沒有「舊」的東西。
• NEW — 包含操作後資料表那一列的新資料。這個會在 INSERT 跟 UPDATE 的觸發器用到。
• TG_OP — 這個變數會告訴你現在是哪種操作：INSERT、UPDATE 或 DELETE。

這些變數在跟觸發器綁定的函式裡都會自動出現，直接用就好。

光講理論就像 SQL 沒有 index 一樣，慢又無聊。來，直接看實戰例子。

用 OLD 取得舊資料

假設我們有個 students 資料表。然後有人改了學生的年齡（可能腦袋打結，以為學生還 20 歲，其實已經 25 了）。

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

為了追蹤到底改了什麼，我們來建一個 log 表：

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

接下來我們寫個函式來記錄這些變化。這時 OLD 就派上用場了：

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 記錄年齡變化
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然後來加個觸發器：

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- 只有年齡真的有變才執行
EXECUTE FUNCTION log_student_changes();

來加個學生，然後改一下資料：

INSERT INTO students (name, age) VALUES ('愛麗絲', 20);

UPDATE students
SET age = 25
WHERE name = '愛麗絲';

-- 查一下 log 表：
SELECT * FROM student_changes;

你會看到 log 表裡記錄了這次變化：年齡從 20 變成 25。魔法嗎？不是，是 OLD。

用 NEW 取得新資料

現在假設我們想在新增學生時自動把他的 ID 跟名字記到 log 表（有點小心過頭，但有時候真的有用）：

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- 記錄新學生資料
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- 沒有舊值，因為這是 INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

再來加個新學生，查查 log：

INSERT INTO students (name, age) VALUES ('鮑勃', 22);

-- 查一下 log：
SELECT * FROM student_changes;

你會看到 log 裡多了一個新學生。這就是對資料的超前部署！

用 TG_OP 判斷操作類型

那如果我們想要一個通用的 log 觸發器，可以同時處理 INSERT、UPDATE，甚至 DELETE 呢？這時 TG_OP 就超好用。

來寫個通用函式：

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- AFTER 觸發器在 DELETE 時要回傳 NULL
END;
$$ LANGUAGE plpgsql;

再來加個觸發器，三種操作都能抓：

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

新增、修改、刪除學生都來一遍：

INSERT INTO students (name, age) VALUES ('查理', 30);
UPDATE students SET age = 31 WHERE name = '查理';
DELETE FROM students WHERE name = '查理';

-- 查一下 log：
SELECT * FROM student_changes;

你會看到所有操作的 log — 一個觸發器就能搞定全部！

用 OLD、NEW、TG_OP 常見的雷

用觸發器時常常會遇到幾個經典問題：

「為什麼 OLD 在新增時沒東西？」 這是正常的：INSERT 根本沒有舊資料。要用 NEW。

「如果 NEW 在刪除時沒東西怎麼辦？」 這也是預期行為：DELETE 沒有新資料。要用 OLD。

觸發器邏輯造成無限遞迴。 要小心不要讓觸發器自己又觸發自己。可以在 WHEN 區塊寫明確條件，或是檢查 TG_OP。

2

任務

SQL SELF, 等級 57, 課堂 4

上鎖

學生年齡變更日誌記錄

學生年齡變更日誌記錄

開啟

1

問卷/小測驗

觸發器入門，等級 57，課堂 4

未開放

觸發器入門

觸發器入門

開啟