您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle Trigger 觸發器完整詳解：語法、類型、四大應用場景與實戰範例

編輯：Oracle 資料庫相關知識

什麼是觸發器

資料庫觸發器是一個與資料表關聯的預儲 PL/SQL 區塊。當特定 DML 操作 (INSERT/UPDATE/DELETE) 發生在指定資料表上時，Oracle 自動執行觸發器定義的邏輯。觸發器與應用程式無關，能確保資料一致性與完整性。

觸發器語法結構

CREATE [OR REPLACE] TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | DELETE | UPDATE [OF column [, ...]]}
  [OR {INSERT | DELETE | UPDATE [OF column [, ...]]} ...]
  ON [schema.]table_name | [schema.]view_name
  [REFERENCING {OLD [AS] old | NEW [AS] new | PARENT AS parent}]
  [FOR EACH ROW]
  [WHEN condition]
  PL/SQL_BLOCK | CALL procedure_name;

核心選項說明

選項	說明
`BEFORE / AFTER`	觸發時機：操作前 / 操作後
`FOR EACH ROW`	列級觸發器：每影響一列觸發一次。省略則為陳述式級 (整句 SQL 觸發一次)
`REFERENCING`	指定 :OLD / :NEW 相關名稱，PL/SQL 區塊中需加冒號 `:OLD.col`，WHEN 子句中不加
`WHEN`	觸發條件 (僅列級 BEFORE/AFTER 可用)，邏輯運算式不可含查詢或 PL/SQL 函數

觸發器類型對照 (每張表最多 12 種)

BEFORE INSERT              BEFORE INSERT FOR EACH ROW
AFTER  INSERT              AFTER  INSERT FOR EACH ROW
BEFORE UPDATE              BEFORE UPDATE FOR EACH ROW
AFTER  UPDATE              AFTER  UPDATE FOR EACH ROW
BEFORE DELETE              BEFORE DELETE FOR EACH ROW
AFTER  DELETE              AFTER  DELETE FOR EACH ROW

四大經典應用場景與範例

1. 複雜安全性檢查 — 非工作時間禁止寫入

CREATE OR REPLACE TRIGGER trg_no_offhour_insert
BEFORE INSERT ON xgj_test
DECLARE
BEGIN
  IF TO_CHAR(SYSDATE, 'DAY') IN ('星期六','星期日')
     OR TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) NOT BETWEEN 9 AND 18 THEN
    RAISE_APPLICATION_ERROR(-20001, '非工作時間禁止插入資料');
  END IF;
END;
/

關鍵：陳述式級 (FOR EACH ROW 省略)，RAISE_APPLICATION_ERROR 回傳自訂錯誤碼 (-20000~-20999)。

2. 資料確認 — 薪資不得下調

CREATE OR REPLACE TRIGGER trg_salary_not_decrease
BEFORE UPDATE ON xgj_test
FOR EACH ROW
BEGIN
  IF :NEW.sal < :OLD.sal THEN
    RAISE_APPLICATION_ERROR(-20002,
      '漲後薪資 ' || :NEW.sal || ' 小於漲前 ' || :OLD.sal);
  END IF;
END;
/

關鍵：列級觸發器，使用 :OLD、:NEW 偽記錄比較欄位新舊值。

3. 資料庫稽核 — 高薪變動記錄

CREATE TABLE xgj_record (info VARCHAR2(256));

CREATE OR REPLACE TRIGGER trg_audit_high_salary
AFTER UPDATE ON xgj_test
FOR EACH ROW
BEGIN
  IF :NEW.sal > 6000 THEN
    INSERT INTO xgj_record VALUES (:NEW.sal || '-' || :NEW.username || '-' || :NEW.job);
  END IF;
END;
/

4. 資料備份與同步 — 同步更新備份表

CREATE TABLE xgj_test_bak AS SELECT * FROM xgj_test;

CREATE OR REPLACE TRIGGER trg_sync_backup
AFTER UPDATE ON xgj_test
FOR EACH ROW
BEGIN
  UPDATE xgj_test_bak SET sal = :NEW.sal WHERE username = :NEW.username;
END;
/

補充：觸發器為同步機制；Oracle 快照/物化視圖為非同步。

進階實戰範例

刪除記錄寫入歷史表

CREATE TABLE emp_his AS SELECT * FROM emp WHERE 1=2;

CREATE OR REPLACE TRIGGER trg_del_emp_hist
BEFORE DELETE ON scott.emp
FOR EACH ROW
BEGIN
  INSERT INTO emp_his VALUES (:OLD.deptno, :OLD.empno, :OLD.ename, :OLD.job,
                              :OLD.mgr, :OLD.sal, :OLD.comm, :OLD.hiredate);
END;
/

限制非上班時間修改 DEPARTMENTS 表

CREATE OR REPLACE TRIGGER trg_dept_time_restrict
BEFORE INSERT OR DELETE OR UPDATE ON departments
BEGIN
  IF TO_CHAR(SYSDATE,'DAY') IN ('星期六','星期日')
     OR TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:30' AND '18:00' THEN
    RAISE_APPLICATION_ERROR(-20001, '非上班時間不可修改 departments');
  END IF;
END;
/

條件式列級觸發 — 僅部門 80 觸發

CREATE OR REPLACE TRIGGER trg_dept80_sal_comm
BEFORE UPDATE OF salary, commission_pct OR DELETE ON hr.employees
FOR EACH ROW
WHEN (OLD.department_id = 80)
BEGIN
  CASE
    WHEN UPDATING('salary') THEN
      IF :NEW.salary < :OLD.salary THEN RAISE_APPLICATION_ERROR(-20001,'部門80薪資不可降'); END IF;
    WHEN UPDATING('commission_pct') THEN
      IF :NEW.commission_pct < :OLD.commission_pct THEN RAISE_APPLICATION_ERROR(-20002,'部門80獎金不可降'); END IF;
    WHEN DELETING THEN
      RAISE_APPLICATION_ERROR(-20003,'不可刪除部門80人員');
  END CASE;
END;
/

級聯更新 — 修改 REGIONS 自動更新 COUNTRIES

CREATE OR REPLACE TRIGGER trg_cascade_region
AFTER UPDATE OF region_id ON regions
FOR EACH ROW
BEGIN
  UPDATE countries SET region_id = :NEW.region_id WHERE region_id = :OLD.region_id;
END;
/

觸發器中呼叫預儲程序

CREATE OR REPLACE PROCEDURE add_job_history (
  p_emp_id NUMBER, p_start_date DATE, p_end_date DATE,
  p_job_id VARCHAR2, p_dept_id NUMBER
) IS
BEGIN
  INSERT INTO job_history VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_dept_id);
END;
/

CREATE OR REPLACE TRIGGER trg_update_job_hist
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
  add_job_history(:OLD.employee_id, :OLD.hire_date, SYSDATE, :OLD.job_id, :OLD.department_id);
END;
/