觸發器實戰

觸發器讓資料庫在特定事件發生時自動執行邏輯。本篇將介紹觸發器的各種應用場景。

一、觸發器基礎

觸發器由兩部分組成：

觸發器函數：定義要執行的邏輯
觸發器：定義何時執行函數

語法結構

sql

-- 1. 建立觸發器函數
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- 邏輯
    RETURN NEW;  -- 或 RETURN OLD; 或 RETURN NULL;
END;
$$;

-- 2. 建立觸發器
CREATE TRIGGER trigger_name
    {BEFORE | AFTER | INSTEAD OF}
    {INSERT | UPDATE | DELETE | TRUNCATE}
    ON table_name
    FOR EACH {ROW | STATEMENT}
    EXECUTE FUNCTION trigger_function_name();

二、觸發時機

BEFORE vs AFTER

時機	特點	用途
BEFORE	可以修改 NEW、可以取消操作	資料驗證、自動填值
AFTER	資料已寫入	審計日誌、同步其他表

FOR EACH ROW vs FOR EACH STATEMENT

層級	執行次數	可存取
ROW	每列一次	OLD, NEW
STATEMENT	每語句一次	無

三、常見應用

自動更新 updated_at

最常見的觸發器應用：

sql

-- 觸發器函數
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 套用到表
CREATE TRIGGER update_users_modtime
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

-- 現在每次 UPDATE users 時，updated_at 會自動更新

批量套用到多張表

sql

-- 找出所有有 updated_at 欄位的表，自動加觸發器
DO $$
DECLARE
    t RECORD;
BEGIN
    FOR t IN
        SELECT table_name FROM information_schema.columns
        WHERE column_name = 'updated_at' AND table_schema = 'public'
    LOOP
        EXECUTE format('
            DROP TRIGGER IF EXISTS update_%I_modtime ON %I;
            CREATE TRIGGER update_%I_modtime
                BEFORE UPDATE ON %I
                FOR EACH ROW
                EXECUTE FUNCTION update_modified_column();
        ', t.table_name, t.table_name, t.table_name, t.table_name);
    END LOOP;
END;
$$;

四、資料驗證

阻止不合法的操作

sql

CREATE OR REPLACE FUNCTION prevent_price_decrease()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < OLD.price THEN
        RAISE EXCEPTION 'Price cannot be decreased from % to %', OLD.price, NEW.price;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_price_decrease
    BEFORE UPDATE OF price ON products
    FOR EACH ROW
    EXECUTE FUNCTION prevent_price_decrease();

只在特定欄位變更時觸發

sql

-- 只有 status 欄位變更時才觸發
CREATE TRIGGER order_status_change
    BEFORE UPDATE OF status ON orders
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status)
    EXECUTE FUNCTION handle_status_change();

五、審計日誌

完整審計表設計

sql

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id TEXT,
    action TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_table ON audit_logs(table_name);
CREATE INDEX idx_audit_time ON audit_logs(changed_at);

觸發器函數

sql

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    record_id TEXT;
    old_data JSONB;
    new_data JSONB;
BEGIN
    -- 取得主鍵值
    IF TG_OP = 'DELETE' THEN
        record_id := OLD.id::TEXT;
        old_data := to_jsonb(OLD);
        new_data := NULL;
    ELSIF TG_OP = 'INSERT' THEN
        record_id := NEW.id::TEXT;
        old_data := NULL;
        new_data := to_jsonb(NEW);
    ELSE  -- UPDATE
        record_id := NEW.id::TEXT;
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);
    END IF;

    INSERT INTO audit_logs (table_name, record_id, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, record_id, TG_OP, old_data, new_data);

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

套用審計

sql

CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

查詢審計紀錄

sql

-- 查詢某筆資料的歷史
SELECT action, old_data, new_data, changed_at
FROM audit_logs
WHERE table_name = 'users' AND record_id = '123'
ORDER BY changed_at DESC;

-- 查詢誰改了什麼
SELECT table_name, record_id, action, changed_by, changed_at
FROM audit_logs
WHERE changed_at > NOW() - INTERVAL '1 day';

六、同步其他表

更新統計資料

sql

CREATE OR REPLACE FUNCTION update_order_stats()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users
        SET order_count = order_count + 1,
            total_spent = total_spent + NEW.amount
        WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users
        SET order_count = order_count - 1,
            total_spent = total_spent - OLD.amount
        WHERE id = OLD.user_id;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE users
        SET total_spent = total_spent - OLD.amount + NEW.amount
        WHERE id = NEW.user_id;
    END IF;

    RETURN NULL;  -- AFTER 觸發器可以返回 NULL
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_user_stats
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION update_order_stats();

七、軟刪除

sql

-- 表結構
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMPTZ
);

-- 攔截 DELETE，改為軟刪除
CREATE OR REPLACE FUNCTION soft_delete()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE documents
    SET is_deleted = TRUE, deleted_at = NOW()
    WHERE id = OLD.id;

    RETURN NULL;  -- 阻止實際刪除
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_hard_delete
    BEFORE DELETE ON documents
    FOR EACH ROW
    EXECUTE FUNCTION soft_delete();

八、條件觸發

WHEN 子句

sql

-- 只有管理員的變更才記錄
CREATE TRIGGER audit_admin_changes
    AFTER UPDATE ON sensitive_data
    FOR EACH ROW
    WHEN (current_user = 'admin')
    EXECUTE FUNCTION audit_trigger();

-- 只有金額變更才觸發
CREATE TRIGGER amount_changed
    AFTER UPDATE ON orders
    FOR EACH ROW
    WHEN (OLD.amount IS DISTINCT FROM NEW.amount)
    EXECUTE FUNCTION notify_amount_change();

九、觸發器變數

在觸發器函數內可用的變數：

變數	說明
`NEW`	新資料（INSERT/UPDATE）
`OLD`	舊資料（UPDATE/DELETE）
`TG_OP`	操作類型（INSERT/UPDATE/DELETE/TRUNCATE）
`TG_TABLE_NAME`	表名
`TG_TABLE_SCHEMA`	Schema 名
`TG_WHEN`	BEFORE/AFTER/INSTEAD OF
`TG_LEVEL`	ROW/STATEMENT
`TG_NAME`	觸發器名稱

十、管理觸發器

查看觸發器

sql

-- 列出表的觸發器
SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'users';

停用/啟用觸發器

sql

-- 停用單一觸發器
ALTER TABLE users DISABLE TRIGGER audit_users;

-- 停用所有觸發器
ALTER TABLE users DISABLE TRIGGER ALL;

-- 啟用
ALTER TABLE users ENABLE TRIGGER audit_users;
ALTER TABLE users ENABLE TRIGGER ALL;

刪除觸發器

sql

DROP TRIGGER IF EXISTS audit_users ON users;

十一、效能考量

> **觸發器影響效能**

每個觸發器都會增加 DML 操作的開銷。避免在觸發器內做：

複雜的計算
大量的 I/O
遠端呼叫

批量操作時考慮停用

sql

-- 批量匯入前
ALTER TABLE orders DISABLE TRIGGER ALL;

-- 匯入資料
COPY orders FROM '/tmp/orders.csv' CSV;

-- 重新啟用
ALTER TABLE orders ENABLE TRIGGER ALL;

-- 手動更新統計
SELECT update_all_user_stats();

總結

概念	說明
BEFORE	操作前執行，可修改 NEW、可取消
AFTER	操作後執行，資料已寫入
FOR EACH ROW	每列觸發一次
FOR EACH STATEMENT	每語句觸發一次
WHEN	條件觸發
TG_OP	取得操作類型

常見應用：

自動更新 updated_at
審計日誌
資料驗證
統計同步
軟刪除

進階挑戰

建立一個審計日誌觸發器，記錄 users 表的所有 INSERT、UPDATE、DELETE 操作
使用 INSTEAD OF 觸發器，讓你可以透過 VIEW 進行寫入操作
比較 BEFORE 和 AFTER 觸發器在 updated_at 自動更新場景的差異

延伸閱讀與資源

← 上一章：預存程序與函數 | 返回專題首頁 | 下一章：檢視表與物化檢視 →

觸發器實戰 ​

一、 觸發器基礎 ​

語法結構 ​

二、 觸發時機 ​

BEFORE vs AFTER ​

FOR EACH ROW vs FOR EACH STATEMENT ​

三、 常見應用 ​

自動更新 updated_at ​

批量套用到多張表 ​

四、 資料驗證 ​

阻止不合法的操作 ​

只在特定欄位變更時觸發 ​

五、 審計日誌 ​

完整審計表設計 ​

觸發器函數 ​

套用審計 ​

查詢審計紀錄 ​

六、 同步其他表 ​

更新統計資料 ​

七、 軟刪除 ​

八、 條件觸發 ​

WHEN 子句 ​

九、 觸發器變數 ​

十、 管理觸發器 ​

查看觸發器 ​

停用/啟用觸發器 ​

刪除觸發器 ​

十一、 效能考量 ​

批量操作時考慮停用 ​

總結 ​

進階挑戰 ​

延伸閱讀與資源 ​

觸發器實戰

一、觸發器基礎

語法結構

二、觸發時機

BEFORE vs AFTER

FOR EACH ROW vs FOR EACH STATEMENT

三、常見應用

自動更新 updated_at

批量套用到多張表

四、資料驗證

阻止不合法的操作

只在特定欄位變更時觸發

五、審計日誌

完整審計表設計

觸發器函數

套用審計

查詢審計紀錄

六、同步其他表

更新統計資料

七、軟刪除

八、條件觸發

WHEN 子句

九、觸發器變數

十、管理觸發器

查看觸發器

停用/啟用觸發器

刪除觸發器

十一、效能考量

批量操作時考慮停用

總結

進階挑戰

延伸閱讀與資源