Oracle触发器调用存储过程后TABLE_2未同步最新记录问题求助

这个问题的核心原因是你在触发器里使用了自治事务（PRAGMA AUTONOMOUS_TRANSACTION），再加上两张表无主键的设计缺陷，导致同步逻辑无法获取主事务的最新数据，最终出现TABLE_2同步不全的情况。

具体问题拆解 #

1. 自治事务的隔离性问题
   触发器里的PRAGMA AUTONOMOUS_TRANSACTION会让触发器的执行逻辑脱离应用的主事务，成为一个独立运行的事务。当Java/Node应用在主事务中向TABLE_1插入数据时，主事务还未提交，自治事务里调用的存储过程无法看到主事务中未提交的变更：

   • 首次插入RECORD_ID=1时，存储过程查询TABLE_1看不到这条未提交的记录，所以TABLE_2没有数据；
   • 第二次插入同RECORD_ID的记录时，第一次的记录已经被主事务提交，存储过程只能查到这条已提交的旧记录，查不到刚插入的未提交新记录，因此TABLE_2只保留旧数据。

2. 无主键的逻辑缺陷
   两张表都没有主键，导致同步时只能通过RECORD_ID批量删除数据，无法精准定位单条记录，进一步放大了事务隔离带来的问题。

解决方案 #

1. 移除自治事务与多余的COMMIT

这是解决同步看不到最新数据的关键步骤，让触发器和存储过程在应用的主事务上下文里执行，就能获取到主事务中的所有变更：

修改后的触发器代码：

create or replace trigger SCHEMA_1.TRIGGER_CALL_SP_OF_SCHEMA_2
AFTER UPDATE OR INSERT OR DELETE ON SCHEMA_1.TABLE_1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  SCHEMA_2.SP_OF_SCHEMA_2(:NEW.RECORD_ID);
EXCEPTION
WHEN OTHERS THEN RAISE;
END;

修改后的存储过程代码：

create or replace PROCEDURE SCHEMA_2.SP_OF_SCHEMA_2(P_RECORD_ID NUMBER )
AS
BEGIN
  DELETE FROM SCHEMA_2.TABLE_2 WHERE RECORD_ID = P_RECORD_ID;
  FOR rec IN (SELECT RECORD_ID, COL2, COL3, COL4 from SCHEMA_1.TABLE_1 where RECORD_ID = P_RECORD_ID)
  LOOP
    INSERT INTO SCHEMA_2.TABLE_2 (RECORD_ID, COL2, COL3, COL4) VALUES(rec.RECORD_ID, rec.COL2, rec.COL3,rec.COL4 );
  END LOOP;
  -- 移除COMMIT，由应用的主事务统一提交
END;

2. 为两张表添加主键（强烈建议）

无主键的设计会导致数据同步、更新、删除时无法精准定位记录，极易出现数据混乱。即使RECORD_ID不唯一，也应该添加自增主键或复合主键（比如结合COL2/COL3等字段），确保每条记录能被唯一识别。

3. 优化同步逻辑（可选，提升效率）

当前“全删全插”的逻辑效率较低，可以根据触发器的操作类型（INSERT/UPDATE/DELETE）做针对性处理，避免不必要的批量删除：

修改触发器传递操作类型：

create or replace trigger SCHEMA_1.TRIGGER_CALL_SP_OF_SCHEMA_2
AFTER UPDATE OR INSERT OR DELETE ON SCHEMA_1.TABLE_1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  v_operation VARCHAR2(10);
BEGIN
  IF INSERTING THEN
    v_operation := 'INSERT';
  ELSIF UPDATING THEN
    v_operation := 'UPDATE';
  ELSIF DELETING THEN
    v_operation := 'DELETE';
  END IF;
  SCHEMA_2.SP_OF_SCHEMA_2(
    v_operation,
    :NEW.RECORD_ID, :NEW.COL2, :NEW.COL3, :NEW.COL4,
    :OLD.RECORD_ID, :OLD.COL2, :OLD.COL3, :OLD.COL4
  );
EXCEPTION
WHEN OTHERS THEN RAISE;
END;

对应存储过程的精准处理逻辑：

create or replace PROCEDURE SCHEMA_2.SP_OF_SCHEMA_2(
  P_OPERATION VARCHAR2,
  P_NEW_RECORD_ID NUMBER,
  P_NEW_COL2 VARCHAR2,
  P_NEW_COL3 VARCHAR2,
  P_NEW_COL4 VARCHAR2,
  P_OLD_RECORD_ID NUMBER,
  P_OLD_COL2 VARCHAR2,
  P_OLD_COL3 VARCHAR2,
  P_OLD_COL4 VARCHAR2
)
AS
BEGIN
  CASE P_OPERATION
    WHEN 'INSERT' THEN
      INSERT INTO SCHEMA_2.TABLE_2 (RECORD_ID, COL2, COL3, COL4) 
      VALUES(P_NEW_RECORD_ID, P_NEW_COL2, P_NEW_COL3, P_NEW_COL4);
    WHEN 'UPDATE' THEN
      -- 此处依赖主键或复合唯一键精准匹配更新，需先为表添加唯一标识
      UPDATE SCHEMA_2.TABLE_2 
      SET COL2 = P_NEW_COL2, COL3 = P_NEW_COL3, COL4 = P_NEW_COL4
      WHERE RECORD_ID = P_OLD_RECORD_ID AND COL2 = P_OLD_COL2 AND COL3 = P_OLD_COL3 AND COL4 = P_OLD_COL4;
    WHEN 'DELETE' THEN
      DELETE FROM SCHEMA_2.TABLE_2 
      WHERE RECORD_ID = P_OLD_RECORD_ID AND COL2 = P_OLD_COL2 AND COL3 = P_OLD_COL3 AND COL4 = P_OLD_COL4;
  END CASE;
END;

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