需求場景
資料異動追蹤：誰、何時、做了什麼操作 (新增/修改/刪除)。觸發器為資料庫層面實作，無侵入應用程式碼。
完整範例腳本
-- 1. 建立審計日誌表
CREATE TABLE audit_log (
dml_type VARCHAR2(20), -- INSERT/UPDATE/DELETE
operation_ts TIMESTAMP DEFAULT SYSTIMESTAMP,
table_name VARCHAR2(30),
pk_value VARCHAR2(100), -- 主鍵值 (JSON/字串)
changed_by VARCHAR2(30) DEFAULT USER,
client_info VARCHAR2(64) -- DBMS_APPLICATION_INFO.SET_CLIENT_INFO
);
-- 2. 目標業務表
CREATE TABLE t2 (x NUMBER PRIMARY KEY);
-- 3. 建立行級觸發器
CREATE OR REPLACE TRIGGER trg_t2_audit
BEFORE INSERT OR UPDATE OR DELETE ON t2
FOR EACH ROW
DECLARE
v_dml VARCHAR2(20);
BEGIN
IF INSERTING THEN
v_dml := 'INSERT';
ELSIF UPDATING THEN
v_dml := 'UPDATE';
ELSIF DELETING THEN
v_dml := 'DELETE';
END IF;
INSERT INTO audit_log (dml_type, table_name, pk_value, changed_by, client_info)
VALUES (v_dml, 'T2',
COALESCE(:NEW.x, :OLD.x), -- 取新/舊主鍵值
USER,
SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'));
END;
/
-- 4. 測試驗證
INSERT INTO t2 VALUES (1);
UPDATE t2 SET x = 2 WHERE x = 1;
DELETE FROM t2 WHERE x = 2;
SELECT * FROM audit_log ORDER BY operation_ts;
進階增強建議
| 增強點 | 實作方式 |
|---|
| 記錄完整舊/新值 | 使用 :OLD.col, :NEW.col 逐欄位比對，或 DBMS_METADATA 動態產生欄位清單 |
| 非同步寫入降低延遲 | 觸發器寫入暫存表 → DBMS_SCHEDULER 批次轉移正式審計表 |
| DDL 操作審計 | 建立 AFTER DDL ON DATABASE 觸發器，記錄 ORA_DICT_OBJ_NAME, ORA_SYSEVENT |
| 防竄改 | 審計表設唯寫權限、啟用 Vault/加密、定期匯出異地備份 |
| 應用端識別 | 應用連線建立時 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('OrderService:v1.2')，觸發器讀取 SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') |
效能與維護注意事項
- 觸發器內避免複雜邏輯/跨表查詢，影響 DML 延遲。
- 大量批次操作 (如 ETL) 時，考慮暫時
ALTER TRIGGER ... DISABLE，事後補寫審計。
- 定期分割/歸檔
audit_log (依時間分區表)，避免表過大影響查詢。
替代方案對比
