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

Oracle Trigger 觸發器實戰：審計日誌自動記錄最佳實踐

編輯：Oracle 資料庫基礎

業務場景

需自動記錄特定用戶對關鍵表（如 DEPT）的 DML 操作（INSERT/UPDATE/DELETE），包含操作用戶、時間、類型，做為審計追蹤依據。

步驟 1：建立審計日誌表

CREATE TABLE mylog (
  user_name      VARCHAR2(10),
  oper_time      DATE,
  operate_type   VARCHAR2(10)
);

步驟 2：建立複合 DML 觸發器

CREATE OR REPLACE TRIGGER my_tri
AFTER INSERT OR UPDATE OR DELETE ON dept
BEGIN
  IF INSERTING THEN
    INSERT INTO mylog VALUES (USER, SYSDATE, 'INSERT');
  ELSIF UPDATING THEN
    INSERT INTO mylog VALUES (USER, SYSDATE, 'UPDATE');
  ELSE  -- DELETING
    INSERT INTO mylog VALUES (USER, SYSDATE, 'DELETE');
  END IF;
END;
/

核心語法解析

子句	說明
AFTER INSERT OR UPDATE OR DELETE	觸發時機：DML 完成後觸發，可見提交後資料
ON dept	目標表
INSERTING / UPDATING / DELETING	條件判斷函數，識別觸發 DML 類型
USER / SYSDATE	內建函數，取得當前會話用戶與系統日期

進階增強：捕獲舊/新值（行級觸發器）

若需記錄具體欄位變更內容，需改為行級觸發器：

CREATE OR REPLACE TRIGGER dept_audit_row
AFTER INSERT OR UPDATE OR DELETE ON dept
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO dept_audit_log (op_type, deptno, dname, loc, op_user, op_time)
    VALUES ('INSERT', :NEW.deptno, :NEW.dname, :NEW.loc, USER, SYSDATE);
  ELSIF UPDATING THEN
    INSERT INTO dept_audit_log (op_type, deptno, dname, loc, op_user, op_time)
    VALUES ('UPDATE', :NEW.deptno, :NEW.dname, :NEW.loc, USER, SYSDATE);
  ELSE
    INSERT INTO dept_audit_log (op_type, deptno, dname, loc, op_user, op_time)
    VALUES ('DELETE', :OLD.deptno, :OLD.dname, :OLD.loc, USER, SYSDATE);
  END IF;
END;
/

:NEW 代表新值（INSERT/UPDATE 可用），:OLD 代表舊值（UPDATE/DELETE 可用）。

觸發器類型對比

類型	觸發頻率	適用場景	效能影響
Statement-level (預設)	每條 DML 執行一次	統計計數、簡單審計、DDL 防護	低
Row-level (FOR EACH ROW)	每影響一行觸發一次	詳細欄位變更、複雜業務規則、跨表同步	高（大量 DML 謹慎）
INSTEAD OF	取代 DML 執行	視圖可更新化、複雜邏輯封裝	中
DDL / Database Event	結構變更/啟動關機	結構審計、自動配置、登入觸發	低

最佳實踐與避坑指南

避免觸發器巢狀過深：觸發器中執行 DML 再觸發其他觸發器，易導致遞迴與效能雪崩
謹慎使用自主交易：PRAGMA AUTONOMOUS_TRANSACTION 可獨立提交審計日誌，但會與主交易隔離，需權衡一致性
大量批次操作考慮停用觸發器：ALTER TRIGGER my_tri DISABLE; 批次完成後再啟用
命名規範：trg_<table>_<timing>_<event>，如 trg_dept_aiud
文檔化：在註釋說明業務目的、觸發條件、異常處理邏輯

驗證測試

-- 測試插入
INSERT INTO dept VALUES (50, 'AUDIT_TEST', 'TAIPEI');
SELECT * FROM mylog;

-- 測試更新
UPDATE dept SET loc = 'TAICHUNG' WHERE deptno = 50;
SELECT * FROM mylog;

-- 測試刪除
DELETE FROM dept WHERE deptno = 50;
SELECT * FROM mylog;

觸發器是資料庫主動防禦與審計的核心機制。善用複合 DML 與行級觸發器組合，在效能可控前提下構建完整資料變更審計鏈路。