首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【DB笔试面试450】如何解决ORA-04091错误?

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

作者头像
小麦苗DBA宝典
发布2019-09-30 19:25:30
1.9K0
发布2019-09-30 19:25:30
举报
题目部分

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

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

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

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

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2018-12-04,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DB宝 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档