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

Oracle Trigger 完全指南：語法、六大功能、自增主鍵實戰與進階範例

編輯：Oracle 資料庫基礎

Trigger 基本語法

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

關鍵字解析

子句	說明
BEFORE / AFTER	觸發時機：DML 執行前/後。BEFORE 可修改 `:NEW` 值；AFTER 可讀取最終狀態。
FOR EACH ROW	列級觸發器：每影響一列觸發一次。省略則為陳述式觸發器，整條 DML 觸發一次。
:NEW / :OLD	偽記錄：`:NEW.col` 新值，`:OLD.col` 舊值。INSERT 僅有 `:NEW`，DELETE 僅有 `:OLD`，UPDATE 兩者皆有。
WHEN	列級觸發器可選條件，僅符合條件才執行本體，減少開銷。

Trigger 六大核心功能

允許/限制表修改：複雜業務規則驗證（如非工作日禁止刪除）。
自動產生衍生欄：自增主鍵、建立時間、修改時間、審計欄位。
強制資料一致性：跨表檢查、級聯更新（謹慎使用，避免遞迴）。
提供稽核與日誌：記錄誰、何時、改了什麼（寫入稽核表）。
防止無效交易：攔截違規操作，RAISE_APPLICATION_ERROR 回滾。
啟用複雜業務邏輯：觸發後續流程（如發送通知、同步快照表）。

實戰一：Sequence + BEFORE INSERT 實現自增主鍵

-- 1. 建表
CREATE TABLE t1 (id NUMBER, name NVARCHAR2(8));

-- 2. 建 Sequence
CREATE SEQUENCE t1_id INCREMENT BY 1 START WITH 1 MAXVALUE 9999999;

-- 3. 建 Trigger
CREATE OR REPLACE TRIGGER tig_insert_t1
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
  IF :NEW.id IS NULL THEN
    SELECT t1_id.NEXTVAL INTO :NEW.id FROM dual;
  END IF;
END;
/

測試：INSERT INTO t1(name) VALUES('abc'); → id 自動填入 1,2,3…

實戰二：通用自增 Trigger 產生器（動態 SQL）


CREATE OR REPLACE PROCEDURE proc_create_seq_trig(p_tables IN VARCHAR2) AS
  v_sql VARCHAR2(4000);
  v_tname VARCHAR2(50);
  v_pid VARCHAR2(50);
BEGIN
  -- 解析逗號分隔表名，迴圈為每張表建 Sequence + Trigger
  -- 略（動態 SQL 組裝 CREATE SEQUENCE / CREATE TRIGGER）
END;
/

適用於批量為現有表補裝自增主鍵，減少手工重複工作。

進階主題

1. 遞迴觸發器與 `WHEN` 防禦


CREATE OR REPLACE TRIGGER trg_emp_sal
AFTER UPDATE OF sal ON emp
FOR EACH ROW
WHEN (NEW.sal != OLD.sal)  -- 僅薪資真正變動時觸發
BEGIN
  UPDATE dept SET total_sal = total_sal + :NEW.sal - :OLD.sal
  WHERE deptno = :NEW.deptno;
END;

2. 複合觸發器（11g+）

單一 Trigger 內包含 BEFORE STATEMENT、BEFORE EACH ROW、AFTER EACH ROW、AFTER STATEMENT 四個時機區塊，解決「列級需彙總後在陳述式級處理」的需求（如大量收集欄位變化最後一次性寫入稽核表），避免 ORA-04091 變動表錯誤。