针对“表XXX发生了变化，触发器/函数不能读它....”问题的解决方案

最新推荐文章于 2021-02-11 21:22:45 发布

原创最新推荐文章于 2021-02-11 21:22:45 发布 · 2.7k 阅读

5 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

11 篇文章

订阅专栏

本文探讨了在Oracle数据库中使用触发器进行表扩展时遇到的异常情况，详细分析了问题产生的原因，并提供了三种解决方案，包括使用自定义事务处理、直接commit以及利用:new和:old变量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、异常出现的场景.

当需要对某一张表进行一个扩展，扩展操作便是在该表上创建一个触发器。将表中的数据同步读入到其他表中。

SQL语句如下：相当于当我往TB_INS表中插入一天或者更新一条数据的时候，通过触发器往TB_INS_TEMP临时表中同步插入一条

CREATE OR REPLACE Trigger TRG_TB_INS_TEMP
After insert or update ON TB_INS
FOR EACH ROW
declare
  instypeNew int 
BEGIN
    num :=0;
    if num =0 then 
    insert into TB_ins_temp (
        CLTNO  --可以有很多列，为了简短起见
    select      
        :NEW.CLTNO
    from dual where not exists
    (select 1 from TB_ins c where c.insid=insIdNew );
    end if ;
  end if;

end;

2、问题分析

Oracle中执行DML语句的时候是需要显示进行提交操作的。当我们进行插入的时候，会触发触发器执行对触发器作用表和扩展表的种种操作，但是这个时候触发器和插入语句是在同一个事务管理中的，因此在插入语句没有被提交的情况下，我们无法对触发器作用表进行其他额外的操作。如果执行其他额外的操作则会抛出如上异常信息。

3、解决方案

（1）我们知道，出错的原因是因为触发器和DML语句在同一事务管理中，所以方案一便是将触发器和DML语句分成两个单独的事务处理。这里可以使用Pragma autonomous_transaction; 告诉Oracle触发器是自定义事务处理。

SQL语句如下：

CREATE OR REPLACE Trigger TRG_TB_INS_TEMP
After insert or update ON TB_INS
FOR EACH ROW
declare
  pragma   autonomous_transaction;--oracle自定义事务处理
  instypeNew int 
BEGIN
    num :=0;
    if num =0 then 
    insert into TB_ins_temp (
        CLTNO  --可以有很多列，为了简短起见
    select      
        :NEW.CLTNO
    from dual where not exists
    (select 1 from TB_ins c where c.insid=insIdNew );
    end if ;
  end if;

end;

（2）当通过事务操作TB_INS,插入或者更新操作的时候也会调用触发器，所以会报“独立事务相关问题的错误” ，所以需要在触发器中操作完插入操作后直接commit；

 create trigger TB_INS_TRIGGER   
    after insert on tb_table   
    for each row   
    begin  
        insert into tt_table(tab_id,tab_name) values(:new.tab_id,:new.tab_name);   
        commit;--主要是针对报独立事务的解决方案   
    end tb_table;

（3）在Oracle 触发器Trigger中有:new,:old两个特殊变量，当触发器为行级触发器的时候，触发器就会提供new和old两个保存临时行数据的特殊变量，我们可以从俩个特殊的变量中取出数据执行扩张表的DML操作。

SQL语句如下：

 create trigger TB_INS_TRIGGER   
    after insert on tb_table   
    for each row   
    begin  
        insert into tt_table(tab_id,tab_name) values(:new.tab_id,:new.tab_name);      
    end tb_table;