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

• 2019 年 10 月 11 日
• 筆記

如何解决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'

& 说明：

有关变异表的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140133/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步