萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 實現數據變更審計與備份:基於觸發器的行級歷史記錄方案

需求場景

業務要求對核心表的增、刪、改操作進行完整審計,記錄操作時間、操作人、修改前後值,以便異常時追溯恢復。建議將審計表存放於不同磁盤或不同數據庫,隔離主業務 IO 壓力。

實施步驟

1. 建立審計表

CREATE TABLE AUD_PRGAMD1(
  W_ROWID    VARCHAR2(50),  -- 行ID
  W_ACTION   VARCHAR2(1),   -- 操作類型: I/U/D
  DTTM       DATE,          -- 操作時間
  OPUSER     VARCHAR2(30),  -- 操作用戶
  B$CODE     VARCHAR2(10),  -- 修改前 CODE
  A$CODE     VARCHAR2(10),  -- 修改後 CODE
  B$FTY      VARCHAR2(1),   -- 修改前 FTY
  A$FTY      VARCHAR2(1)    -- 修改後 FTY
);

2. 建立業務表

CREATE TABLE prgamd (
  code VARCHAR2(20),
  fty  VARCHAR2(20)
);

3. 創建行級觸發器

CREATE OR REPLACE TRIGGER AUD_PRGAMD_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON PRGAMD
FOR EACH ROW
DECLARE
  V_ROWID   VARCHAR2(50);
  V_ACTION  VARCHAR2(1);
  V_OPUSER  VARCHAR2(30) := 'UNKNOWN';
BEGIN
  IF INSERTING THEN
    V_ROWID  := :NEW.ROWID;
    V_ACTION := 'I';
  ELSIF UPDATING THEN
    V_ROWID  := :NEW.ROWID;
    V_ACTION := 'U';
  ELSIF DELETING THEN
    V_ROWID  := :OLD.ROWID;
    V_ACTION := 'D';
  END IF;

  V_OPUSER := USER;

  INSERT INTO AUD_PRGAMD1 (W_ROWID, W_ACTION, DTTM, OPUSER, B$CODE, B$FTY, A$CODE, A$FTY)
  VALUES (V_ROWID, V_ACTION, SYSDATE, V_OPUSER, :OLD.CODE, :OLD.FTY, :NEW.CODE, :NEW.FTY);
END;

關鍵點: :OLD 代表舊值,:NEW 代表新值。DELETE 時 :NEW 為 NULL;INSERT 時 :OLD 為 NULL。

4. 測試驗證

INSERT INTO prgamd VALUES('x','x');
SELECT * FROM AUD_PRGAMD1;  -- 顯示 I 操作記錄
UPDATE prgamd SET code='m1' WHERE fty='x';
COMMIT;
SELECT * FROM AUD_PRGAMD1;  -- 新增 U 操作記錄,含前後值

優化建議

  • 大批量操作時觸發器可能影響性能,可考慮基於 DBMS_FGA 精細審計或 CDC 異步捕獲。
  • 審計表定期歸檔分區,防止表過大影響查詢。
  • 敏感字段可加密存儲。
copyright © 萬盛學電腦網 all rights reserved