Oracle变异表解决方法--ORA-04091错误

原因：触发器（或者被语句中引用的用户自定义PL/SQL函数）视图去查询（或修改）一个被另一语句修改而触发的表。

解决方法：重写触发器（或函数）避免读该表。

对表进行DELETE，UPDATE，INSERT操作时，所操作的表就变成了变异表，对表的行级（FOR EACH ROW）触发器中不能对该表进行DML操作。这时可以使用两个方法解决。

[@more@]

1、采用自治事务pragma autonomous_transaction解决。

下面给出一个使用自治事务解决ORA-04091错误的示例：

CREATE OR REPLACE TRIGGER SCOTT.TRG_UPDATE_EMP

  AFTER UPDATE ON SCOTT.EMP

  FOR EACH ROW

DECLARE

  V_NUM NUMBER;

BEGIN

  SELECT COUNT(1) INTO V_NUM FROM SCOTT.EMP T WHERE DEPTNO = :NEW.DEPTNO;

  IF V_NUM > 2 THEN

    RAISE_APPLICATION_ERROR(-20001, V_NUM);

  END IF;

END;

执行更新操作报错ORA-04091：

SYS@lhrdb> UPDATE SCOTT.EMP SET SAL=0 ;

UPDATE scott.emp set sal=0

             *

ERROR at line 1:

ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.TRG_UPDATE_EMP", line 4

ORA-04088: error during execution of trigger 'SCOTT.TRG_UPDATE_EMP'

这里加上自治事务后可以解决该问题：

CREATE OR REPLACE TRIGGER SCOTT.TRG_UPDATE_EMP

  AFTER UPDATE ON SCOTT.EMP

  FOR EACH ROW

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

  V_NUM NUMBER;

BEGIN

  SELECT COUNT(1) INTO V_NUM FROM SCOTT.EMP T WHERE DEPTNO = :NEW.DEPTNO;

  IF V_NUM > 2 THEN

    RAISE_APPLICATION_ERROR(-20001, V_NUM);

  END IF;

END;

再次执行：

SYS@lhrdb> UPDATE SCOTT.EMP SET SAL=0 ;

UPDATE SCOTT.EMP SET SAL=0

             *

ERROR at line 1:

ORA-20001: 4

ORA-06512: at "SCOTT.TRG_UPDATE_EMP", line 7

ORA-04088: error during execution of trigger 'SCOTT.TRG_UPDATE_EMP'

------------------------------------------------ 再给出一个例子

 使用scott方案，创建一下表、触发器：