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/