ORA-04091: table XXX is mutating, trigger/function may not see it

错误的原因
该错误是在编写trigger时常遇到的问题，其根本原因是由于对本表的操作造成的.ORACLE DB里默认在写TRIGGER的时候把本表锁死,不允许对其进行操作，当对某个表T进行update时，在trigger的body或trigger调用的存储过程又有对update表的查询，这时常会碰到该错误。

治标的解决办法
利用自治事物进行解决。
自治事物的概念：就是在subprogram里进行事物的提交不影响主程序的事务，同样主程序的提交或回滚都不影响子程序的commit，即子程序的事物和主程序的事物完全独立。

Exp1：
SQL> CREATE TABLE T(ID NUMBER(18),MC VARCHAR2(20),DT DATE);

表已创建。

SQL> CREATE OR REPLACE TRIGGER TR_T
2  AFTER DELETE ON T
3  FOR EACH ROW
4  DECLARE V_COUNT NUMBER;
5  --PRAGMA AUTONOMOUS_TRANSACTION;
6  BEGIN
7     INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
8     COMMIT;
9  END TR_DEL_CABLE;
10  /

触发器已创建

SQL> INSERT INTO T VALUES(1,'111111',SYSDATE);

已创建 1 行。

SQL> INSERT INTO T VALUES(2,'222222',SYSDATE);

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;

ID MC                   TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
1 111111               20080802 11:07:36
2 222222               20080802 11:07:43

SQL> DELETE FROM T WHERE ID=1;
DELETE FROM T WHERE ID=1
*
第 1 行出现错误:
ORA-04091: 表 TEST.T 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "TEST.TR_T", line 4
ORA-04088: 触发器 'TEST.TR_T' 执行过程中出错


SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;

ID MC                   TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
1 111111               20080802 11:07:36
2 222222               20080802 11:07:43

SQL> CREATE OR REPLACE TRIGGER TR_T
2  AFTER DELETE ON T
3  FOR EACH ROW
4  DECLARE V_COUNT NUMBER;
5  PRAGMA AUTONOMOUS_TRANSACTION;
6  BEGIN
7     INSERT INTO T VALUES(:OLD.ID,:OLD.MC,SYSDATE);
8     COMMIT;
9  END TR_DEL_CABLE;
10  /

触发器已创建

SQL> DELETE FROM T WHERE ID=1;

已删除 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT ID,MC,TO_CHAR(DT,'YYYYMMDD HH24:MI:SS') FROM T;

ID MC                   TO_CHAR(DT,'YYYYM
---------- -------------------- -----------------
2 222222               20080802 11:07:43
1 111111               20080802 11:08:32
（注：此前有人说可以通过把NEW OLD中的值放到local变量或package变量中可以避免此类错误，但我尝试过很多，还是照样抛出该错误，故将值存到变量中的方法不可行。）


治本的办法
首先自治事务必须慎用，因为一个DML可能会产生许多个独立的事物，这很容易引发死锁，ASKTOM上对AUTONOMOUS_TRANSACTION的看法是：唯一的用途就是作审计日志，其他一概不该使用。
治本的办法就是彻底废除trigger，把相应的处理逻辑放到存储过程中。


Exp2：

CREATE   OR   REPLACE  PACKAGE BOM_AUTONUMBER
IS
TYPE t_MAX_SEQNUMBER  is   table   of   number   INDEX   BY  PLS_INTEGER;
v_MAX_SEQNUMBER t_MAX_SEQNUMBER;
end  BOM_AUTONUMBER;
/

CREATE   OR   REPLACE   TRIGGER  TR_BOM_AUTONUMBER_SEQNUMBER
BEFORE  INSERT
ON  BOM 
REFERENCING NEW  AS  NEW OLD  AS  OLD
FOR  EACH ROW
DECLARE
    vNumber  number ;
    vBOMID  number ;
BEGIN
    vNumber: =   0 ;
    vBOMID: =  :New.BOMID;
     if   not  BOM_AUTONUMBER.v_MAX_SEQNUMBER. EXISTS (vBOMID)  then
         SELECT  GREATEST(nvl( Max (to_number(SEQ_NUMBER)), 0 ),  Count ( * ))  INTO  vNumber  FROM  BOM  Where  ITEM  =  vBOMID;
        BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID) : =  nvl(vNumber,  0 );
     end   if ;
    BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID) : =  BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID)  +   1 ;
   :NEW.SEQ_NUMBER : =  BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID);
END  TR_BOM_ AUTONUMBER_SEQNUMBER;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-676203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-676203/