Oracle多表审计结构：会话级关联数据快照实现方案及问题咨询

你的思路方向是对的——用统一的change session id来标记同一事务内的变更，但核心卡在了明细触发器无法查询关联明细的变异表问题上。下面给你几个可行的替代方案，完美解决这个Oracle触发器的限制：

方案1：使用复合触发器（Compound Trigger） #

Oracle的复合触发器允许你在同一个触发器中结合语句级和行级逻辑，还能维护会话级的集合变量，刚好避开变异表问题。具体步骤：

1. 先创建一个序列生成change session id：

CREATE SEQUENCE CHANGE_SESSION_SEQ START WITH 1 INCREMENT BY 1;

2. 为HEADER_TABLE创建复合触发器，在事务结束时统一生成完整快照：

CREATE OR REPLACE TRIGGER HEADER_AUDIT_TRIGGER
FOR INSERT OR UPDATE OR DELETE ON HEADER_TABLE
COMPOUND TRIGGER
  -- 定义集合存储当前事务中受影响的表头ID
  TYPE HeaderIdList IS TABLE OF HEADER_TABLE.HEADER_ID%TYPE;
  v_header_ids HeaderIdList := HeaderIdList();
  v_session_id NUMBER;

  BEFORE STATEMENT IS
  BEGIN
    -- 为当前事务生成唯一的change session id
    v_session_id := CHANGE_SESSION_SEQ.NEXTVAL;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    -- 收集所有受影响的表头ID（新增/修改/删除都要捕获）
    v_header_ids.EXTEND;
    IF INSERTING OR UPDATING THEN
      v_header_ids(v_header_ids.LAST) := :NEW.HEADER_ID;
    ELSE
      v_header_ids(v_header_ids.LAST) := :OLD.HEADER_ID;
    END IF;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- 去重表头ID（避免同一事务内多次变更同一表头）
    FOR header_rec IN (SELECT DISTINCT id FROM TABLE(v_header_ids)) LOOP
      -- 插入表头审计快照（变更前的数据，用OLD值）
      INSERT INTO HEADER_TABLE_AUDIT (CHANGE_SESSION_ID, HEADER_ID, /* 其他字段 */)
      SELECT v_session_id, HEADER_ID, /* 其他字段 */
      FROM HEADER_TABLE
      WHERE HEADER_ID = header_rec.id;

      -- 插入所有关联明细的审计快照（变更前的完整数据）
      INSERT INTO DETAIL_AUDIT_TABLE_1 (CHANGE_SESSION_ID, DETAIL_ID, HEADER_ID, /* 其他字段 */)
      SELECT v_session_id, DETAIL_ID, HEADER_ID, /* 其他字段 */
      FROM DETAIL_TABLE_1
      WHERE HEADER_ID = header_rec.id;
    END LOOP;
  END AFTER STATEMENT;
END;
/

3. 移除DETAIL_TABLE_1上的原有触发器——因为复合触发器已经在表头变更时自动生成了所有明细的快照，不需要再单独触发明细的审计逻辑。如果需要处理单独更新明细的场景，再为DETAIL_TABLE_1创建类似的复合触发器，在AFTER STATEMENT阶段根据关联的HEADER_ID生成完整快照（同样要检查change session id是否已存在，避免重复生成）。

方案2：使用会话上下文+独立存储过程 #

如果觉得复合触发器太复杂，可以用会话上下文标记变更状态，配合存储过程生成快照：

1. 创建会话上下文：

CREATE OR REPLACE CONTEXT CHANGE_CONTEXT USING CHANGE_SESSION_PKG;

2. 创建存储过程，负责生成指定HEADER_ID的完整快照：

CREATE OR REPLACE PACKAGE CHANGE_SESSION_PKG IS
  PROCEDURE GENERATE_SNAPSHOT(p_header_id IN HEADER_TABLE.HEADER_ID%TYPE);
END CHANGE_SESSION_PKG;
/

CREATE OR REPLACE PACKAGE BODY CHANGE_SESSION_PKG IS
  PROCEDURE GENERATE_SNAPSHOT(p_header_id IN HEADER_TABLE.HEADER_ID%TYPE) IS
    v_session_id NUMBER;
  BEGIN
    -- 检查当前会话是否已生成过该表头的快照
    SELECT SYS_CONTEXT('CHANGE_CONTEXT', 'SESSION_ID') INTO v_session_id FROM DUAL;
    IF v_session_id IS NULL THEN
      v_session_id := CHANGE_SESSION_SEQ.NEXTVAL;
      DBMS_SESSION.SET_CONTEXT('CHANGE_CONTEXT', 'SESSION_ID', v_session_id);

      -- 插入表头快照
      INSERT INTO HEADER_TABLE_AUDIT (CHANGE_SESSION_ID, /* 其他字段 */)
      SELECT v_session_id, /* 其他字段 */ FROM HEADER_TABLE WHERE HEADER_ID = p_header_id;

      -- 插入所有关联明细快照
      INSERT INTO DETAIL_AUDIT_TABLE_1 (CHANGE_SESSION_ID, /* 其他字段 */)
      SELECT v_session_id, /* 其他字段 */ FROM DETAIL_TABLE_1 WHERE HEADER_ID = p_header_id;
    END IF;
  END GENERATE_SNAPSHOT;
END CHANGE_SESSION_PKG;
/

3. 在HEADER_TABLE和DETAIL_TABLE_1的行级触发器中调用这个存储过程：

-- 表头触发器
CREATE OR REPLACE TRIGGER HEADER_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON HEADER_TABLE
FOR EACH ROW
BEGIN
  IF INSERTING OR UPDATING THEN
    CHANGE_SESSION_PKG.GENERATE_SNAPSHOT(:NEW.HEADER_ID);
  ELSE
    CHANGE_SESSION_PKG.GENERATE_SNAPSHOT(:OLD.HEADER_ID);
  END IF;
END;
/

-- 明细触发器
CREATE OR REPLACE TRIGGER DETAIL_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON DETAIL_TABLE_1
FOR EACH ROW
BEGIN
  IF INSERTING OR UPDATING THEN
    CHANGE_SESSION_PKG.GENERATE_SNAPSHOT(:NEW.HEADER_ID);
  ELSE
    CHANGE_SESSION_PKG.GENERATE_SNAPSHOT(:OLD.HEADER_ID);
  END IF;
END;
/

4. 记得在事务结束后清除会话上下文（可以用AFTER STATEMENT触发器或者应用层处理），避免下一个事务复用同一个session id：

CREATE OR REPLACE TRIGGER CLEAR_CONTEXT_TRIGGER
AFTER STATEMENT ON HEADER_TABLE
BEGIN
  DBMS_SESSION.CLEAR_CONTEXT('CHANGE_CONTEXT');
END;
/

方案3：改用语句级触发器+临时表 #

如果你的业务场景中同一事务内只会变更单个表头，也可以用语句级触发器配合临时表：

1. 创建全局临时表存储受影响的表头ID：

CREATE GLOBAL TEMPORARY TABLE TEMP_HEADER_IDS (HEADER_ID NUMBER) ON COMMIT DELETE ROWS;

2. 表头的行级触发器将受影响的ID写入临时表：

CREATE OR REPLACE TRIGGER HEADER_ROW_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON HEADER_TABLE
FOR EACH ROW
BEGIN
  IF INSERTING OR UPDATING THEN
    INSERT INTO TEMP_HEADER_IDS VALUES (:NEW.HEADER_ID);
  ELSE
    INSERT INTO TEMP_HEADER_IDS VALUES (:OLD.HEADER_ID);
  END IF;
END;
/

3. 表头的语句级触发器读取临时表的ID，生成完整快照：

CREATE OR REPLACE TRIGGER HEADER_STATEMENT_TRIGGER
AFTER STATEMENT ON HEADER_TABLE
BEGIN
  FOR rec IN (SELECT DISTINCT HEADER_ID FROM TEMP_HEADER_IDS) LOOP
    -- 插入表头和明细快照，逻辑同方案1
    INSERT INTO HEADER_TABLE_AUDIT (CHANGE_SESSION_ID, /* 其他字段 */)
    SELECT CHANGE_SESSION_SEQ.NEXTVAL, /* 其他字段 */ FROM HEADER_TABLE WHERE HEADER_ID = rec.HEADER_ID;
    
    INSERT INTO DETAIL_AUDIT_TABLE_1 (CHANGE_SESSION_ID, /* 其他字段 */)
    SELECT CHANGE_SESSION_SEQ.CURRVAL, /* 其他字段 */ FROM DETAIL_TABLE_1 WHERE HEADER_ID = rec.HEADER_ID;
  END LOOP;
END;
/

关键注意点 #

• 所有方案都避开了在行级触发器中查询触发表的操作，彻底解决Oracle的变异表（ORA-04091）错误。
• 要确保同一事务内只生成一次快照，通过change session id或会话上下文来做幂等校验。
• 如果需要支持多表头同时变更的场景，方案1的复合触发器是最灵活的选择。

内容的提问来源于stack exchange，提问作者bashby82