觸發器是資料庫層面的業務規則執行與稽核利器。分類、語法、時機、條件謂詞、實戰模式一次掌握。
| 類型 | 觸發事件 | 定義對象 | 典型用途 |
|---|---|---|---|
| DML 觸發器 | INSERT/UPDATE/DELETE | Table / View | 複雜驗證、衍生欄位維護、稽核軌跡 |
| INSTEAD OF | INSERT/UPDATE/DELETE | View | 不可更新 View 的資料導向 |
| 系統觸發器 | DDL/LOGON/LOGOFF/STARTUP/SHUTDOWN | Database / Schema | DDL 稽核、登入管控、啟動初始化 |
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE [OF col1, col2] | DELETE}
ON table_or_view
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW] -- 行級觸發 (省略為語句級)
[WHEN (condition)] -- 觸發條件
DECLARE
-- 宣告區
BEGIN
-- 執行區
IF INSERTING THEN ...
ELSIF UPDATING THEN ...
ELSIF DELETING THEN ... END IF;
EXCEPTION
-- 例外處理
END;
/
```
關鍵概念速查
- :OLD / :NEW：行級觸發器可用的偽記錄。
INSERT 只有 :NEW；DELETE 只有 :OLD；UPDATE 兩者皆有。
- 行級 vs 語句級：
FOR EACH ROW 每影響一行觸發一次；語句級整個 DML 只觸發一次 (適合統計、日誌)。
- 條件謂詞：
INSERTING、UPDATING、DELETING 判斷觸發事件。
- 觸發順序：同事件同類型多觸發器依建立時間順序執行 (不可控)，建議合併邏輯。
實戰範例 1：防止重複教師姓名 (BEFORE ROW)
CREATE OR REPLACE TRIGGER trg_teacher_unique
BEFORE INSERT OR UPDATE OF tname ON teachers
FOR EACH ROW
WHEN (NEW.tname = 'David')
DECLARE
v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt FROM teachers WHERE tname = :NEW.tname;
IF v_cnt > 0 THEN
RAISE_APPLICATION_ERROR(-20001, '教師姓名已存在：' || :NEW.tname);
END IF;
END;
/
```
實戰範例 2：稽核所有 DML (AFTER ROW)
CREATE OR REPLACE TRIGGER trg_teacher_audit
AFTER INSERT OR UPDATE OR DELETE ON teachers
FOR EACH ROW
DECLARE
v_action VARCHAR2(10);
BEGIN
IF INSERTING THEN v_action := 'INSERT';
ELSIF UPDATING THEN v_action := 'UPDATE';
ELSE v_action := 'DELETE'; END IF;
INSERT INTO audit_log (table_name, action, pk_value, log_time, log_user)
VALUES ('TEACHERS', v_action,
COALESCE(:NEW.tid, :OLD.tid), SYSDATE, USER);
END;
/
```
實戰範例 3：DDL 稽核 (系統觸發器)
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON DATABASE
DECLARE
v_sql VARCHAR2(4000);
BEGIN
SELECT sql_text INTO v_sql FROM v$sqltext
WHERE address = (SELECT sql_address FROM v$session WHERE sid = USERENV('SID'))
ORDER BY piece;
INSERT INTO ddl_audit (ddl_user, ddl_time, ddl_type, object_name, sql_text)
VALUES (SYS_CONTEXT('USERENV', 'CURRENT_USER'), SYSDATE,
ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, v_sql);
END;
/
```
管理指令
-- 停用/啟用
ALTER TRIGGER trg_name DISABLE/ENABLE;
-- 刪除
DROP TRIGGER trg_name;
-- 查看狀態
SELECT trigger_name, trigger_type, triggering_event, table_name, status
FROM user_triggers;
避坑原則
- **避免觸發器巢狀過深** (觸發器中 DML 再觸發其他觸發器)，易導致
ORA-04091 (mutating table) 或效能雪崩。
- **大量資料操作 (Bulk Load) 先停用觸發器**，完成後再啟用並補跑邏輯。
- **INSTEAD OF 僅用於 View**，表請用 BEFORE/AFTER。
- **例外處理**：觸發器中未捕捉的例外會導致觸發 DML 回滾，務必在關鍵業務觸發器加
EXCEPTION WHEN OTHERS THEN ...。
掌握分類、語法、偽記錄、謂詞、避坑原則，觸發器成為資料庫層業務防線的核心力量。
