觸發器核心概念
儲存於資料庫的 PL/SQL 區塊，**由事件自動觸發執行**，使用者無法直接 CALL。適用於：稽核日誌、衍生欄位維護、跨表同步、安全防護、資料完整性補強。
觸發事件類型
| 類別 | 事件 |
|---|
| DML | INSERT、UPDATE、DELETE |
| DDL | CREATE、ALTER、DROP |
| 資料庫系統 | STARTUP、SHUTDOWN、SERVERERROR |
| 使用者 | LOGON、LOGOFF、DDL/DML 執行 |
觸發器三要素
- 觸發事件/語句：上表任一事件。
- 觸發限制：
WHEN (condition) 布林運算式，FALSE/NULL 時跳過執行。
- 觸發動作：PL/SQL 區塊或
CALL procedure。
六大分類與適用場景
| 分類 | 觸發時機 | 執行次數 | 典型用途 |
|---|
| 列級 BEFORE | 每列變更前 | N 列 = N 次 | 驗證/修正新值、自動填充衍生欄位、防刪保護 |
| 列級 AFTER | 每列變更後 | N 列 = N 次 | 稽核日誌、跨表同步、發送通知 |
| 語句級 BEFORE | 整句執行前 | 1 次/句 | 全域暫存表初始化、權限檢查 |
| 語句級 AFTER | 整句執行後 | 1 次/句 | 統計更新、批次日誌彙總 |
| 複合觸發器 | 4 時間點合一 | 視邏輯 | 需同時存取 BEFORE/AFTER 狀態、避免 mutating table 錯誤 |
| INSTEAD OF | 取代 DML | N 列 = N 次 | 不可更新視圖的寫入轉導基礎表 |
編寫關鍵：:NEW / :OLD 伪記錄
| 操作 | :OLD | :NEW |
|---|
| INSERT | 無 (NULL) | 新值 |
| UPDATE | 舊值 | 新值 |
| DELETE | 舊值 | 無 (NULL) |
**僅列級觸發器可用**。語句級觸發器無 :NEW/:OLD。
判斷觸發 DML 類型
IF INSERTING THEN ... ELSIF UPDATING THEN ... ELSIF DELETING THEN ... END IF;
實戰範例
列級 BEFORE：自動維護修改時間
CREATE OR REPLACE TRIGGER trg_emp_biu
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
:NEW.modified_dt := SYSDATE;
:NEW.modified_by := USER;
END;
列級 AFTER：稽核日誌
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
v_action VARCHAR2(10);
BEGIN
IF INSERTING THEN v_action := 'INS';
ELSIF UPDATING THEN v_action := 'UPD';
ELSE v_action := 'DEL';
END IF;
INSERT INTO emp_audit (emp_id, action, old_name, new_name, action_dt, action_by)
VALUES (NVL(:NEW.emp_id, :OLD.emp_id), v_action, :OLD.ename, :NEW.ename, SYSDATE, USER);
END;
複合觸發器：解決 Mutating Table (ORA-04091)
CREATE OR REPLACE TRIGGER trg_compound
FOR UPDATE OF salary ON emp
COMPOUND TRIGGER
-- 1. 語句前
BEFORE STATEMENT IS BEGIN NULL; END BEFORE STATEMENT;
-- 2. 每列前
BEFORE EACH ROW IS BEGIN NULL; END BEFORE EACH ROW;
-- 3. 每列後（可安全查詢同表）
AFTER EACH ROW IS
BEGIN
-- 此處可 SELECT emp 而不報 ORA-04091
NULL;
END AFTER EACH ROW;
-- 4. 語句後
AFTER STATEMENT IS BEGIN NULL; END AFTER STATEMENT;
END;
INSTEAD OF：視圖寫入轉導
CREATE VIEW v_emp_dept AS
SELECT e.emp_id, e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;
CREATE OR REPLACE TRIGGER trg_v_emp_dept_iof
INSTEAD OF INSERT OR UPDATE ON v_emp_dept
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO emp (emp_id, ename, deptno) VALUES (:NEW.emp_id, :NEW.ename, :NEW.deptno);
ELSE
UPDATE emp SET ename = :NEW.ename, deptno = :NEW.deptno WHERE emp_id = :OLD.emp_id;
END IF;
END;
觸發器管理
| 操作 | SQL |
|---|
| 停用 | ALTER TRIGGER trg_name DISABLE; |
| 啟用 | ALTER TRIGGER trg_name ENABLE; |
| 刪除 | DROP TRIGGER trg_name; |
| 查看狀態 | SELECT trigger_name, status FROM user_triggers; |
避坑指南
- Mutating Table (ORA-04091)： 列級觸發器中查詢/修改同一觸發表 → 改用複合觸發器
AFTER EACH ROW 或語句級觸發器。
- 遞迴觸發： 觸發器動作再次觸發同觸發器 → 使用包變數標記
pkg.flag 防重入。
- 效能： 大批次 DML 觸發列級觸發器極慢 → 改語句級、或暫停觸發器
ALTER TRIGGER ... DISABLE 批次跑完再啟用。
- 隱蔽邏輯： 觸發器在背景執行，除錯困難 → 關鍵邏輯改用存儲過程顯式呼叫，觸發器僅做薄封裝。
總結
觸發器 = 事件驅動的自動化 PL/SQL。掌握 **列級 vs 語句級、BEFORE vs AFTER、:NEW/:OLD、複合觸發器解決 Mutating、INSTEAD OF 處理視圖寫入** 六大核心，並嚴守「慎用、可追蹤、可停用」三原則，讓觸發器成為資料完整性守門員而非維護噩夢。
