oracle中经常使用了trigger经常会出现ora-04091变异表问题，这里带来两种处理方法给大家参考。
oracle中经常使用了trigger经常会出现ora-04091变异表问题，这里带来两种处理方法给大家参考。
--**** example  *****
场景描述：
--1. init table
create table sales (prod_code varchar2( 4),amount_sold number ) ;
insert into sales values ( '0100',0 );
insert into sales values ( '0200',0 );
insert into sales values ( '0300',0 );
commit ;
select * from sales order by 1 ;
-- 2. 如果插入或者更新子类别，父类别的总销量必须级联更新
-- 比如 如果更新了0111 类别销量加1 那么 父类别0100 的销量必须也加上1
/* test trigger :
create or replace trigger sales_amount_refresh
  after insert on sales
  for each row
  when (new.amount_sold>0)
declare
begin
  update sales
    set amount_sold = amount_sold + :new.amount_sold
  where substr(:new.prod_code, 0, length(rtrim(prod_code, '0'))) =
        rtrim(prod_code, '0') ;
end ;
*/
--会产生变异表的问题
/* test sql :
sql> insert into sales values ('0111',1) ;
insert into sales values ('0111',1)
ora-04091: table dexter.sales is mutating, trigger/function may not see it
ora-06512: at dexter.sales_amount_refresh, line 4
ora-04088: error during execution of trigger 'dexter.sales_amount_refresh'
*/
--解决办法1：可以使用自制事务来解决，但是有bug，即无法回滚
/*
create or replace trigger sales_amount_refresh
  after insert on sales
  for each row
  when (new.amount_sold>0)
declare
  pragma autonomous_transaction ;
begin
  update sales
    set amount_sold = amount_sold + :new.amount_sold
  where substr(:new.prod_code, 0, length(rtrim(prod_code, '0'))) =
        rtrim(prod_code, '0') ;
  commit ;
end ;
自治事务里面的语句不会回滚
*/
--解决办法2
使用package的集合变量记录需要变更的信息，然后使用statement级别的trigger 执行update操作，package中的变量只在session中有效
--只需要创建一个package 头即可
create or replace package pkg_vars is
  type c_t is table of number index by varchar2(4 ) ;
  refresh_sales_list c_t ;
end ;
/
--修改行级触发器为
create or replace trigger sales_amount_refresh
  after insert on sales
  for each row
  when (new.amount_sold> 0)
declare
begin
  if length(rtrim (:new.prod_code, '0'))> 2 then
    pkg_vars.refresh_sales_list(:new.prod_code) := :new.amount_sold ;
  end if ;
end ;
/
--新增语句级触发器
create or replace trigger sales_amount_refresh_statement
  after insert on sales
declare
  ind varchar2( 4) ;
begin
  ind := pkg_vars.refresh_sales_list.first ;
  while ind is not null loop
    update sales
    set amount_sold = amount_sold + pkg_vars.refresh_sales_list(ind)
  where substr(ind, 0 , length( rtrim(prod_code, '0' ))) =
        rtrim(prod_code, '0' ) and length(rtrim (prod_code, '0'))= 2 ;
    ind := pkg_vars.refresh_sales_list.next(ind);
  end loop;
  pkg_vars.refresh_sales_list.delete ;
end ;
/
推荐阅读：
ora-01172、ora-01151错误处理
ora-00600 [2662]错误解决
ora-01078 和 lrm-00109 报错解决方法
ora-00471 处理方法笔记
ora-00314,redolog 损坏，或丢失处理方法
ora-00257 归档日志过大导致无法存储的解决办法
，