【DB笔试面试450】如何解决ORA-04091错误?

【DB笔试面试450】如何解决ORA-04091错误?

♣          题目         部分

如何解决ORA-04091错误?


     
♣          答案部分          



官方对ORA-04091的解释如下所示:

[ZFLHRZHDB1:oracle]:/oracle>oerr ora 04091

04091, 00000, "table %s.%s is mutating, trigger/function may not see it"

// *Cause: A trigger (or a user defined plsql function that is referenced in

//         this statement) attempted to look at (or modify) a table that was

//         in the middle of being modified by the statement which fired it.

// *Action: Rewrite the trigger (or function) so it does not read that table.

很多时候需要在触发器中对触发表进行查询,但在行级触发器中是不允许的。触发器中SQL的语句不能进行如下操作:

(1)读或修改触发语句的任何变异表,其中,包括触发表本身。变异表就是当前正被DML语句操作的表。对于触发器而言,变异表就是在其上定义该触发器的那张表。

(2)读或修改触发表的约束表中的主关键字、唯一关键字和外部关键字列。除此之外的其它列都可以修改。

如果违背了以上的原则那么就会产生ORA-04091的错误。可以使用如下4种方法来解决该问题:

方法一:采用自治事务。

方法二:用DBMS_JOB.RUN包。

方法三:用两个触发器(一个行级的,一个语句级的)和一个包。

方法四:首先在变异表上创建视图,然后在视图上建触发器来解决变异表不能进行DML操作的问题。

下面给出一个使用自治事务解决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'


上一篇:【DB笔试面试683】在Oracle中,什么是ORA-01555错误?


下一篇:【故障处理】分布式事务ORA-01591错误解决