Oracle 觸發器完整教學：分類、語法、實戰案例與注意事項

觸發器核心概念

觸發器是特定事件自動執行的 PL/SQL 區塊，類似存儲過程但無法直接呼叫，由資料庫事件隱式觸發。包含宣告、執行、異常處理三段。

五大分類

六大組成要素

1. 觸發事件：DML/DDL/系統/用戶事件
2. 觸發時間：`BEFORE` / `AFTER` / `INSTEAD OF`
3. 觸發操作：PL/SQL 區塊
4. 觸發對象：表/視圖/Schema/資料庫
5. 觸發條件：`WHEN (condition)` 只有 TRUE 才執行
6. 觸發頻率：`FOR EACH ROW` (行級) / 無 (語句級)

通用語法

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
{BEFORE | AFTER | INSTEAD OF}
{DELETE | INSERT | UPDATE [OF column...]} [OR ...]
ON [schema.]table_name | view_name
[FOR EACH ROW [WHEN (condition)]]
[DECLARE ...]
BEGIN
  -- PL/SQL 區塊
END;
/
SHOW ERRORS

INSTEAD OF 觸發器專用語法

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF {INSERT | DELETE | UPDATE [OF column...]} [OR ...]
ON view_name
[REFERENCING {OLD [AS] old | NEW [AS] new | PARENT AS parent}]
FOR EACH ROW  -- 必須為行級
[DECLARE ...]
BEGIN
  -- 視圖操作邏輯
END;
/
-- 注意：INSTEAD OF 無 BEFORE/AFTER，無 WHEN 條件

實戰案例演示

建立測試環境

CREATE TABLE triggertest (
  id    VARCHAR2(20),
  name  VARCHAR2(20),
  score NUMBER
);
CREATE TABLE tab1 AS SELECT * FROM triggertest WHERE 1=0;

1. DML 行級觸發器：同步備份表

CREATE OR REPLACE TRIGGER trigger1
AFTER INSERT ON triggertest
FOR EACH ROW
BEGIN
  INSERT INTO tab1 (id, name) VALUES ('22', '33');
END;
/
-- 測試
INSERT INTO triggertest (id) VALUES ('aabbcc');
-- tab1 新增一筆 ('22','33')

2. 限制修改時間：非上班時間禁止 DML

CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE ON triggertest
FOR EACH ROW
BEGIN
  IF (TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT','SUN'))
     OR (TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '09:00' AND '18:00') THEN
    RAISE_APPLICATION_ERROR(-20001, '非上班時間，禁止修改 triggertest');
  END IF;
END;
/
-- 測試 (假設現在是週三 10:00)
INSERT INTO triggertest (id) VALUES ('test');  -- 成功
-- 若是週六或 20:00 → ORA-20001

3. 業務規則：特定人員分數只能升不能降

CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE ON triggertest
FOR EACH ROW
WHEN (OLD.name = '月兒')
BEGIN
  IF UPDATING('score') THEN
    IF :NEW.score < :OLD.score THEN
      RAISE_APPLICATION_ERROR(-20001, '月兒的分數只能提升不能下降');
    END IF;
  END IF;
END;
/
-- 測試
UPDATE triggertest SET score = 10 WHERE name = '月兒';  -- ORA-20001 (原 20)
UPDATE triggertest SET score = 30 WHERE name = '月兒';  -- 成功

4. 觸發器中呼叫存儲過程

CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE ON triggertest
FOR EACH ROW
BEGIN
  testpro1;
END;
/

CREATE OR REPLACE PROCEDURE testpro1 AS
BEGIN
  INSERT INTO tab1 (id, name, score) VALUES ('AAA', 'BBB', 200);
END;
/
-- 任何 DML 觸發 trigger1 皆會在 tab1 新增一筆

5. 級聯更新：主表名稱變更同步從表

CREATE OR REPLACE TRIGGER trigger1
AFTER UPDATE OF name ON triggertest
FOR EACH ROW
BEGIN
  UPDATE tab1 SET name = :NEW.name WHERE name = :OLD.name;
END;
/
-- 測試
UPDATE triggertest SET name = '水兒' WHERE name = '月兒';
-- tab1 中 name='月兒' 同步改為 '水兒'

6. INSTEAD OF 觸發器：讓多表連接視圖可更新

-- 基礎表
CREATE TABLE student (
  id       NUMBER,
  name     VARCHAR2(20),
  classid  VARCHAR2(10),
  score    NUMBER
);

-- 視圖：各班平均分
CREATE OR REPLACE VIEW student_view AS
SELECT classid, AVG(score) AS average_score
FROM student
GROUP BY classid;

-- 直接刪除視圖會報錯 ORA-01732
DELETE FROM student_view WHERE classid = '111';

-- 解決：建立 INSTEAD OF DELETE 觸發器
CREATE OR REPLACE TRIGGER student_view_delete
INSTEAD OF DELETE ON student_view
FOR EACH ROW
BEGIN
  DELETE FROM student WHERE classid = :OLD.classid;
END;
/
-- 再次執行刪除 → 成功，student 表對應班級資料被刪除

四大注意事項

1. 僅能使用 DML：觸發器本體只能含 `SELECT/INSERT/UPDATE/DELETE`，禁止 DDL (CREATE/ALTER/DROP)。
2. 不可 COMMIT/ROLLBACK：觸發器動作與觸發事件同一交易，由外層控制提交/回滾。
   例外：`AUTONOMOUS_TRANSACTION` 獨立交易觸發器。
3. 效能影響：單表觸發器越多，DML 效能越差。高頻表謹慎使用，考慮異步觸發 (AQ/DBMS_SCHEDULER)。
4. 大小限制：觸發器原始碼 ≤ 32KB。

除錯技巧

• 查看觸發器狀態：`SELECT trigger_name, status FROM user_triggers;`
• 啟用/停用：`ALTER TRIGGER trigger_name ENABLE/DISABLE;`
• 編譯錯誤：`SHOW ERRORS TRIGGER trigger_name;`
• 追蹤執行：`DBMS_APPLICATION_INFO.SET_MODULE` + `DBMS_TRACE` 或 `DBMS_PROFILER`。