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;
/
| 關鍵字 | 說明 |
|---|---|
| BEFORE | DML 執行前 (可修改 :NEW、阻止操作) |
| AFTER | DML 執行後 (不可修改 :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`。
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;
/
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;
/
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;
/
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。
原因:在 SQL 陳述式中直接使用 PL/SQL 變數或 `:NEW/:OLD` 但語法位置不支援 (如 `VALUES (:NEW.id)` 外層漏括號、資料型別不匹配)。
排查步驟:
觸發器是「資料庫主動防禦線」,用對地方 (審計、衍生欄位、跨表同步、安全性) 是神器,濫用則成效能噩夢。掌握 `:NEW/:OLD`、行級/陳述式級、複合觸發器三大維度,並建立「約束優先、觸發器次之」原則,才能駕馭這把雙面刃。