Oracle 觸發器語法詳解：創建語法、觸發時機、行級/語句級與實戰範例

前言

觸發器是由事件自動驅動的存儲程序，實現數據完整性、審計日誌、衍生列維護、安全控制等橫切關注點。本文系統講解創建語法、觸發時機、行級/語句級差異、:NEW/:OLD 伪記錄、異常處理及實戰模式。

一、創建語法完整結構

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE | UPDATE OF column_list }
ON table_name | view_name
[ REFERENCING { OLD AS old_name | NEW AS new_name } ]
[ FOR EACH ROW ]
[ WHEN (condition) ]
DECLARE
    -- 聲明區：變量、游標、異常
BEGIN
    -- 執行區：PL/SQL 語句
    [ EXCEPTION
        -- 異常處理區
    ]
END [trigger_name];

二、核心選項詳解

1. 觸發時機

2. 觸發事件

• INSERT：插入觸發
• UPDATE：更新觸發，可選 UPDATE OF col1, col2 限定列
• DELETE：刪除觸發
• 組合：INSERT OR UPDATE OR DELETE

3. 行級 vs 語句級

4. WHILE 條件謂詞

• 僅行級觸發器支持
• 在執行體前額外過濾，減少無效觸發開銷
• 示例：WHEN (NEW.sal > OLD.sal * 1.2) 僅薪資漲幅超 20% 時觸發

三、:NEW 與 :OLD 伪記錄

訪問語法

:NEW.column_name
:OLD.column_name

四、實戰範例

範例 1：禁止週末修改表 (語句級 BEFORE)

CREATE OR REPLACE TRIGGER trg_no_weekend_mod
BEFORE INSERT OR UPDATE OR DELETE ON auths
DECLARE
    l_day VARCHAR2(3);
BEGIN
    l_day := TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
    IF l_day IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(-20600, '週末禁止修改 AUTH 表');
    END IF;
END;
/

範例 2：自動填充創建時間/更新時間 (行級 BEFORE)

CREATE OR REPLACE TRIGGER trg_emp_audit_cols
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.created_at := SYSDATE;
        :NEW.created_by := USER;
    END IF;
    :NEW.updated_at := SYSDATE;
    :NEW.updated_by := USER;
END;
/

範例 3：薪資變更審計日誌 (行級 AFTER)

CREATE TABLE emp_sal_audit (
    empno      NUMBER(10),
    old_sal    NUMBER(10,2),
    new_sal    NUMBER(10,2),
    changed_at DATE,
    changed_by VARCHAR2(30)
);

CREATE OR REPLACE TRIGGER trg_emp_sal_audit
AFTER UPDATE OF sal ON emp
FOR EACH ROW
WHEN (OLD.sal != NEW.sal)  -- 僅薪資真正變化時觸發
BEGIN
    INSERT INTO emp_sal_audit
    VALUES (:OLD.empno, :OLD.sal, :NEW.sal, SYSDATE, USER);
END;
/

範例 4：複雜視圖 INSTEAD OF 觸發器

-- 視圖：員工+部門聯合
CREATE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- INSTEAD OF 觸發器使視圖可更新
CREATE OR REPLACE TRIGGER trg_v_emp_dept_iof
INSTEAD OF UPDATE ON v_emp_dept
FOR EACH ROW
BEGIN
    -- 僅允許更新員工薪資
    IF UPDATING('sal') THEN
        UPDATE emp SET sal = :NEW.sal WHERE empno = :OLD.empno;
    ELSE
        RAISE_APPLICATION_ERROR(-20601, '視圖僅允許更新薪資列');
    END IF;
END;
/

五、異常處理與 RAISE_APPLICATION_ERROR

1. 語法

RAISE_APPLICATION_ERROR(error_number, error_message [, {TRUE | FALSE}]);

2. 參數規範

• error_number：-20000 到 -20999 (用戶自定義錯誤碼範圍)
• error_message：最長 2KB，超長自動截斷
• TRUE：錯誤棧包含預定義異常信息；FALSE (默認)：僅用戶錯誤

3. 觸發器中異常處理模式

CREATE OR REPLACE TRIGGER trg_safe
BEFORE INSERT ON target_table
FOR EACH ROW
BEGIN
    -- 業務邏輯
    NULL;
EXCEPTION
    WHEN OTHERS THEN
        -- 記錄日誌
        INSERT INTO trigger_error_log (trigger_name, error_code, error_msg, occurred_at)
        VALUES ('TRG_SAFE', SQLCODE, SQLERRM, SYSDATE);
        -- 重新拋出或轉換為業務錯誤
        RAISE_APPLICATION_ERROR(-20001, '觸發器執行失敗: ' || SQLERRM);
END;
/

六、觸發器管理與調試

1. 查看觸發器定義

SELECT trigger_name, trigger_type, triggering_event, table_name, status
FROM user_triggers
WHERE trigger_name = 'TRG_EMP_SAL_AUDIT';

-- 獲取源碼
SELECT text FROM user_source WHERE name = 'TRG_EMP_SAL_AUDIT' ORDER BY line;

2. 啟用/禁用

ALTER TRIGGER trg_emp_sal_audit DISABLE;
ALTER TRIGGER trg_emp_sal_audit ENABLE;

3. 編譯錯誤查看

SHOW ERRORS TRIGGER trg_emp_sal_audit;
SELECT * FROM user_errors WHERE name = 'TRG_EMP_SAL_AUDIT' ORDER BY sequence;

七、最佳實踐與避坑指南

八、遞歸觸發器防範 (Mutating Table ORA-04091)

問題

-- 行級觸發器中查詢/修改同一表
CREATE TRIGGER trg_bad
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM emp;  -- ORA-04091: table is mutating
END;

解決方案 (11g+ 複合觸發器)

CREATE OR REPLACE TRIGGER trg_good
FOR UPDATE ON emp
COMPOUND TRIGGER
    -- 語句級前：初始化集合
    BEFORE STATEMENT IS
    BEGIN
        g_emp_ids := sys.odcinumberlist();
    END BEFORE STATEMENT;

    -- 行級：收集 ID
    AFTER EACH ROW IS
    BEGIN
        g_emp_ids.EXTEND;
        g_emp_ids(g_emp_ids.COUNT) := :NEW.empno;
    END AFTER EACH ROW;

    -- 語句級後：批量處理
    AFTER STATEMENT IS
    BEGIN
        FORALL i IN 1..g_emp_ids.COUNT
            INSERT INTO emp_audit (empno, audit_time) VALUES (g_emp_ids(i), SYSDATE);
    END AFTER STATEMENT;
END trg_good;

結語

觸發器是雙刃劍：用得好是橫切關注點的優雅實現，用不好是性能殺手與維護噩夢。掌握 BEFORE/AFTER/INSTEAD OF、行級/語句級、:NEW/:OLD、WHEN 條件、複合觸發器等核心機制，遵循「簡單、約束優先、文檔化、可禁用」原則，方能駕馭這把利器。