當資料被修改時，自動更新 last_modified 欄位的 trigger

想像一下，你正在開發一個管理學生跟課程的 app，你有一個 students 資料表。這個表有個 last_modified 欄位，每次只要資料有被改（像是學生名字或年齡被改），這個欄位就要自動更新。

與其每次 SQL 查詢都手動寫 last_modified 的更新，我們可以寫一個 trigger 幫我們自動做這件事。

students 資料表結構

先來建立一個 students 資料表，這會是我們的範例。這個表存放學生的基本資料：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- 學生唯一識別碼
    name VARCHAR(100) NOT NULL,   -- 學生名字
    age INT,                      -- 學生年齡
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- 最後修改時間
);

• last_modified 欄位在新增資料時會自動填入現在時間（NOW()）。
• 這個欄位會在學生資料被改動時自動更新。

來塞一些測試資料進去：

INSERT INTO students (name, age)
VALUES 
    ('奧托 林', 20),
    ('瑪麗亞 奇', 22),
    ('亞歷克斯 松', 19);

現在資料表內容長這樣：

建立更新 last_modified 的 function

PL/pgSQL 的 function 會被 trigger 用來更新 last_modified 欄位。這個 function 會在資料被改動前自動被呼叫。

來寫一個 update_last_modified function：

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- 把 last_modified 欄位設成現在時間
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW 是一個特別的變數，裡面放著資料被改過後的新內容。
• 我們把 NEW.last_modified 設成 NOW()（現在的日期時間）。
• function 要回傳更新過的 NEW，這樣 trigger 才會正常運作。

建立 trigger

現在來建立 trigger，讓每次 students 表有資料被更新時，自動呼叫 update_last_modified function。

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

這裡發生了什麼事：

• BEFORE UPDATE 表示 trigger 會在 資料被更新前 先執行。
• FOR EACH ROW 代表每一筆被改的資料都會觸發一次。
• EXECUTE FUNCTION update_last_modified() 就是要呼叫我們剛剛寫的 function。

測試 trigger

來看看 trigger 怎麼運作。先查一下 students 表的資料：

SELECT * FROM students;

查詢結果：

現在把 student_id = 1 的學生年齡改一下：

UPDATE students
SET age = 21
WHERE student_id = 1;

再查一次資料：

SELECT * FROM students;

預期結果：

注意看：student_id = 1 的 last_modified 已經變成現在時間，其他資料沒變。

擴充 trigger 的邏輯

假設我們現在只想在 特定欄位被改動時 才更新 last_modified。比如只有學生名字或年齡有變才要觸發，其他欄位變動就不用。

這時可以在 trigger 裡加 WHEN 條件。

來寫一個有條件的 trigger：

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

這裡：

• WHEN 條件會檢查舊資料（OLD）跟新資料（NEW）的 name 跟 age 有沒有不一樣。
• 如果這兩個欄位都沒變，trigger 就不會動作。

再來可以試著改資料，測試一下新邏輯。

使用 trigger 的建議

1. 不要濫用 trigger。 它很方便，但會讓資料庫邏輯變複雜，debug 也比較難。
2. 一定要寫清楚 trigger 的用途跟用在哪些情境。
3. 用 WHEN 條件，減少不必要的 trigger 執行。
4. 記得 trigger 可能會影響資料庫效能，特別是資料很多的時候。

用 trigger 常見的錯誤

資料改錯了。 比如忘了給 NEW 設值，結果回傳的資料沒變。

條件寫錯。 比如忘了加 WHEN，trigger 什麼時候都會動，根本不需要。

遞迴。 如果 trigger 呼叫的 function 又觸發 trigger，可能會無限循環。PostgreSQL 有遞迴保護，但還是盡量避免這種情況。

這個例子可以看到，trigger 真的可以讓自動更新資料變得很簡單。在實際專案裡，這種技巧常常用來做資料異動紀錄、維護資料一致性、還有自動化一些重複的工作。