Oracle触发器实现表数据同步失败：原因排查与解决方案

问题原因 #

原触发器存在两个核心问题：

• 行级触发器+自治事务导致数据不可见：触发器定义为after insert on patrik_test for each row，每插入一行就触发一次。加上pragma autonomous_transaction后，触发器的事务独立于主插入事务，主事务中未提交的新数据（比如测试的'AA'）无法被自治事务读取，因此select * from patrik_test查不到刚插入的数据，最终patrik_test2被截断后没有数据写入。
• 重复触发造成无效操作：如果是批量插入数据，行级触发器会触发N次，每次都执行截断和全表插入，不仅效率极低，还会导致patrik_test2出现多次空表的异常状态。

解决方案 #

调整触发器为语句级，移除自治事务并增加数据判断 #

将触发器改为语句级AFTER INSERT触发器，确保整个插入语句执行完成后仅触发一次；移除自治事务以保证能读取到主事务插入的数据；同时增加判断逻辑，仅当patrik_test插入后存在数据时，才同步更新patrik_test2，避免KNIME流程失败时清空patrik_test2。

修改后的触发器代码：

create or replace trigger patrik_test_trg
after insert on patrik_test
begin
  -- 仅当patrik_test存在数据时，才同步更新patrik_test2
  if (select count(*) from patrik_test) > 0 then
    execute immediate 'truncate table patrik_test2';
    insert into patrik_test2 select * from patrik_test;
  end if;
end;
/

补充说明 #

• 语句级触发器优势：整个插入操作完成后仅触发一次，避免了行级触发器的重复执行问题，同时能完整读取到本次插入的所有数据。
• 数据判断逻辑：当KNIME流程失败（仅截断patrik_test但未插入数据），select count(*) from patrik_test结果为0，触发器不会执行截断和插入操作，patrik_test2将保留原有数据。
• 移除自治事务：主事务的插入操作完成后，触发器在同一事务上下文执行，能正常读取到插入的数据，无需使用独立的自治事务。

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