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 ; /