萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle Trigger 觸發器建立實戰:DML 觸發時機、:NEW/:OLD、複合觸發器與除錯技巧

觸發器語法結構

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE [OR ...]}
ON table_name
[FOR EACH ROW [WHEN (condition)]]
[FOLLOWS | PRECEDES other_trigger]
[ENABLE | DISABLE]
DECLARE
  -- 宣告區 (行級觸發器可用)
BEGIN
  -- 執行區
  [EXCEPTION ...]
END;
/

關鍵字對照

關鍵字說明
BEFOREDML 執行前 (可修改 :NEW、阻止操作)
AFTERDML 執行後 (不可修改 :NEW、可讀 :OLD/:NEW)
INSTEAD OF僅用於 View,取代 DML 動作
FOR EACH ROW行級觸發 (每影響一列觸發一次)
FOR EACH STATEMENT陳述式級觸發 (整個 DML 觸發一次,預設)
:NEW.col新值 (INSERT/UPDATE 可用,DELETE 為 NULL)
:OLD.col舊值 (UPDATE/DELETE 可用,INSERT 為 NULL)

實戰範例:審計同步表

需求:`employees` 表異動時,同步寫入 `emp_audit`。

INSERT 觸發器

CREATE OR REPLACE TRIGGER trg_emp_ins
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit (emp_id, emp_name, action, mod_time)
  VALUES (:NEW.emp_id, :NEW.emp_name, 'INSERT', SYSDATE);
END;
/

DELETE 觸發器

CREATE OR REPLACE TRIGGER trg_emp_del
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit (emp_id, emp_name, action, mod_time)
  VALUES (:OLD.emp_id, :OLD.emp_name, 'DELETE', SYSDATE);
END;
/

UPDATE 觸發器 (合併寫法)

CREATE OR REPLACE TRIGGER trg_emp_upd
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit (emp_id, emp_name, action, mod_time)
  VALUES (:NEW.emp_id, :NEW.emp_name, 'UPDATE', SYSDATE);
END;
/

複合觸發器 (11g+):單一物件處理多種時機

CREATE OR REPLACE TRIGGER trg_emp_compound
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
  -- 共享變數區
  g_audit_rows  SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();

  BEFORE STATEMENT IS
  BEGIN
    g_audit_rows.DELETE;  -- 清空
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    g_audit_rows.EXTEND;
    g_audit_rows(g_audit_rows.LAST) :=
      CASE
        WHEN INSERTING THEN 'INSERT:' || :NEW.emp_id
        WHEN UPDATING  THEN 'UPDATE:' || :NEW.emp_id
        WHEN DELETING  THEN 'DELETE:' || :OLD.emp_id
      END;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FORALL i IN 1..g_audit_rows.COUNT
      INSERT INTO emp_audit (action, mod_time) VALUES (g_audit_rows(i), SYSDATE);
  END AFTER STATEMENT;
END trg_emp_compound;
/

優勢:**單次交易只寫一次審計表**、避免行級觸發器逐筆 INSERT 產生大量 Redo/Undo。

常見編譯錯誤:ORA-00984 "column not allowed here"

原因:在 SQL 陳述式中直接使用 PL/SQL 變數或 `:NEW/:OLD` 但語法位置不支援 (如 `VALUES (:NEW.id)` 外層漏括號、資料型別不匹配)。

排查步驟:

  1. `SHOW ERRORS TRIGGER trg_name` 看完整堆疊
  2. 檢查 `:NEW.col` 型別與目標欄位一致
  3. 確認 `BEGIN ... END;` 區塊每句以 `;` 結尾
  4. 若動態 SQL (`EXECUTE IMMEDIATE`),改用 `USING :NEW.col` 綁定變數

最佳實踐清單

  1. **能用約束 (PK/FK/CHECK) 解決的,絕不寫觸發器**。
  2. **避免觸發器巢狀/遞迴** (觸發器再觸發 DML → 再觸發觸發器),設定 `_system_trigger_level` 或用套件變數防重入。
  3. **大量 DML 時改用複合觸發器 + BULK COLLECT/FORALL** 批次寫入。
  4. **命名規範**:`trg_{table}_{timing}_{event}` (如 `trg_emp_aiu_row`)。
  5. **文檔化**:觸發器內註解業務規則、觸發條件、影響範圍。

結語

觸發器是「資料庫主動防禦線」,用對地方 (審計、衍生欄位、跨表同步、安全性) 是神器,濫用則成效能噩夢。掌握 `:NEW/:OLD`、行級/陳述式級、複合觸發器三大維度,並建立「約束優先、觸發器次之」原則,才能駕馭這把雙面刃。

copyright © 萬盛學電腦網 all rights reserved