Oracle Trigger 觸發器完整教學：類型、語法與實作範例

一、觸發器基本概念

觸發器是在特定事件發生時自動執行的 PL/SQL 區塊，類似預存程序但無法由使用者直接呼叫。

主要功能

• 允許/限制對表的修改
• 自動產生衍生欄位（如自增欄位）
• 強制資料一致性
• 提供稽核與日誌記錄
• 防止無效交易
• 實作複雜業務邏輯

二、觸發器組成四要素

1. 觸發器名稱：建議命名慣例 biufer_表名_欄位名（b=before, i=insert, u=update, f=for each row, er=error handling）。
2. 觸發陳述式：DML（INSERT/UPDATE/DELETE）、DDL、資料庫啟動/關閉等。
3. 觸發器限制：WHEN 條件，非必要。
4. 觸發動作：PL/SQL 區塊（BEGIN ... END）。

三、範例：Before Insert/Update Row-Level Trigger

CREATE TRIGGER biufer_employees_department_id
BEFORE INSERT OR UPDATE OF department_id ON employees
REFERENCING OLD AS old_value NEW AS new_value
FOR EACH ROW
WHEN (new_value.department_id <> 80)
BEGIN
    :new_value.commission_pct := 0;
END;
/

解析

• 觸發時機：INSERT 任意欄位、或 UPDATE department_id 時。
• REFERENCING 別名簡化新舊值引用。
• FOR EACH ROW 表列級觸發器（每列觸發一次）。
• WHEN 限制：僅當新部門代碼不等於 80 時執行。
• 動作：將 commission_pct 強制歸零。

四、觸發器類型

五、實作範例：DML 稽核日誌（Statement Trigger）

1. 建立測試表與日誌表

CREATE TABLE scott.employees_copy AS SELECT * FROM scott.emp;
CREATE TABLE scott.employees_log (who VARCHAR2(30), when DATE);

2. 建立 Statement Trigger 記錄操作人與時間

CREATE OR REPLACE TRIGGER biud_employee_copy
BEFORE INSERT OR UPDATE OR DELETE ON scott.employees_copy
BEGIN
    INSERT INTO scott.employees_log (who, when) VALUES (USER, SYSDATE);
END;
/

3. 測試

UPDATE scott.employees_copy SET sal = sal * 1.1;
SELECT * FROM scott.employees_log;

六、進階：識別觸發的 DML 類型（INSERTING/UPDATING/DELETING）

CREATE OR REPLACE TRIGGER biud_employee_copy
BEFORE INSERT OR UPDATE OR DELETE ON employees_copy
DECLARE
    l_action employees_log.action%TYPE;
BEGIN
    IF INSERTING THEN
        l_action := 'Insert';
    ELSIF UPDATING THEN
        l_action := 'Update';
    ELSIF DELETING THEN
        l_action := 'Delete';
    ELSE
        RAISE_APPLICATION_ERROR(-20001, 'Unexpected trigger state');
    END IF;
    INSERT INTO employees_log (who, action, when) VALUES (USER, l_action, SYSDATE);
END;
/

七、列級觸發器進階：偵測特定欄位更新

IF UPDATING('COL1') OR UPDATING('COL2') THEN
    -- 特定欄位被更新時的處理
END IF;

八、注意事項

• 觸發器會隱性改變使用者輸入值（如範例將 commission_pct 改為 0），不會通知使用者。
• 避免在觸發器中執行耗時操作，影響 DML 效能。
• 巢狀觸發器（觸發器內再觸發其他觸發器）需謹慎設計，防止無限迴圈。
• 使用 DBMS_OUTPUT 除錯時，需在用戶端開啟 SET SERVEROUTPUT ON。