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

Oracle 觸發器實戰：表間資料同步範例

編輯：Oracle 資料庫基礎

業務場景

當 `TAB_USER_DETAIL` 表新增一筆資料時，自動向 `TAB_USER` 表插入一筆對應記錄。

觸發器程式碼


CREATE OR REPLACE TRIGGER tri_user_insert
AFTER INSERT ON tab_user_detail
FOR EACH ROW
BEGIN
  INSERT INTO tab_user (
    user_id,
    user_name,
    user_pwd,
    user_customername,
    user_photos
  ) VALUES (
    :new.my_userid,      -- 引用新資料列的欄位值
    'username',          -- 固定值或可從 :new 取得
    'password',
    'truename',
    'Default.jpg'
  );
END;
/

關鍵語法解析

子句	含義
AFTER INSERT	觸發時機：INSERT 語句成功完成後觸發 (若 INSERT 失敗/回滾，觸發器不執行)
ON tab_user_detail	觸發來源表
FOR EACH ROW	行級觸發器：每插入一行觸發一次 (無此子句為陳述式級，整個 INSERT 只觸發一次)
:new.欄位名	引用新資料列的欄位值 (僅行級觸發器可用)
:old.欄位名	引用舊資料列的欄位值 (UPDATE/DELETE 可用)

觸發器類型對照

類型	觸發時機	FOR EACH ROW	典型用途
BEFORE INSERT	插入前	可選	資料驗證、自動填充欄位 (如 ID、建立時間)
AFTER INSERT	插入後	可選	同步其他表、寫入審計日誌、發送通知
BEFORE UPDATE	更新前	可選	驗證變更、記錄舊值
AFTER UPDATE	更新後	可選	同步更新、觸發下游流程
BEFORE DELETE	刪除前	可選	檢查引用完整性、備份刪除資料
AFTER DELETE	刪除後	可選	清理關聯資料、審計
INSTEAD OF	替代視圖 DML	必須	使不可更新視圖可更新

進階範例：雙向同步與遞迴防護


-- 需求：TAB_A 與 TAB_B 雙向同步 INSERT，防止觸發器遞迴無限循環
CREATE OR REPLACE TRIGGER trg_a_to_b
AFTER INSERT ON tab_a
FOR EACH ROW
DECLARE
  v_sync_flag NUMBER;
BEGIN
  -- 檢查套件變數防護旗標
  IF sync_pkg.g_sync_in_progress THEN
    RETURN;  -- 已在同步流程中，直接返回
  END IF;
  
  sync_pkg.g_sync_in_progress := TRUE;
  
  INSERT INTO tab_b (id, col1, col2, sync_src)
  VALUES (:new.id, :new.col1, :new.col2, 'A');
  
  sync_pkg.g_sync_in_progress := FALSE;
EXCEPTION
  WHEN OTHERS THEN
    sync_pkg.g_sync_in_progress := FALSE;
    RAISE;
END;
/

-- 套件定義防護旗標
CREATE OR REPLACE PACKAGE sync_pkg IS
  g_sync_in_progress BOOLEAN := FALSE;
END sync_pkg;
/

常見錯誤與最佳實踐

錯誤/問題	原因	解決
ORA-04091: table is mutating	行級觸發器中查詢/修改同一張觸發表	改用 AFTER 陳述式級觸發器 + 複合觸發器 (11g+)，或用套件暫存主鍵
觸發器遞迴循環	A 觸發 B，B 又觸發 A	套件變數旗標防護、或用 CONTEXT 變數標記來源
效能影響大	觸發器中執行複雜邏輯/遠端呼叫	異步化：觸發器只寫入佇列表，由 Job/排程器處理
異常導致主交易回滾	觸發器未處理例外	觸發器內部自行捕獲例外並記錄，不向外拋出 (視業務而定)
大量資料載入觸發器拖慢	逐行觸發	載入前 `ALTER TRIGGER ... DISABLE`，載入後 `ENABLE`

管理指令速查


-- 查詢觸發器狀態
SELECT trigger_name, table_name, status, triggering_event 
FROM user_triggers WHERE table_name = 'TAB_USER_DETAIL';

-- 啟用/停用
ALTER TRIGGER tri_user_insert ENABLE;
ALTER TRIGGER tri_user_insert DISABLE;

-- 重新編譯
ALTER TRIGGER tri_user_insert COMPILE;

-- 刪除
DROP TRIGGER tri_user_insert;

-- 查看觸發器原始碼
SELECT text FROM user_source WHERE name = 'TRI_USER_INSERT' AND type = 'TRIGGER';

11g+ 複合觸發器 — 解決 Mutating Table 問題


CREATE OR REPLACE TRIGGER trg_compound_example
FOR INSERT OR UPDATE ON tab_user_detail
COMPOUND TRIGGER

  -- 陳述式級前：初始化集合
  g_ids SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();

  BEFORE STATEMENT IS
  BEGIN
    g_ids.DELETE;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    g_ids.EXTEND;
    g_ids(g_ids.LAST) := :new.my_userid;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- 批次處理，避免逐行查詢觸發表
    FORALL i IN 1..g_ids.COUNT
      INSERT INTO tab_user (user_id, user_name, user_pwd, user_customername, user_photos)
      VALUES (g_ids(i), 'username', 'password', 'truename', 'Default.jpg');
  END AFTER STATEMENT;

END trg_compound_example;
/

觸發器是強大但雙面刃的工具：善用於審計、同步、衍生資料維護；慎用於複雜業務邏輯、跨資料庫操作。掌握行級/陳述式級、BEFORE/AFTER、複合觸發器特性，並建立防護機制，才能駕馭其威力。