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方案，创建一下表、触发器：

[sql] view plain copy

SQL> create table tr_table as select * from emp;
表已创建。
SQL> edit
已写入 file afiedt.buf
1 create or replace trigger tr_test
2 after update on emp
3 for each row
4 begin
5 update tr_table t set t.sal = (select sal from emp where empno=t.empno and empno=:new.empno);
6* end;
SQL> /
触发器已创建
SQL> update emp set sal=3700 where empno=7788;
update emp set sal=3700 where empno=7788
*
第 1 行出现错误:
<span style="color:#ff0000;"</span style=>ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.TR_TEST", line 2
ORA-04088: 触发器 'SCOTT.TR_TEST' 执行过程中出错

3.原因分析：

在Oracle中执行DML语句的时候是需要显示进行提交操作的。当我们进行插入的时候，会触发触发器执行对触发器作用表和扩展表的种种操作，但是这个时候触发器和插入语句是在同一个事务管理中的，因此在插入语句没有被提交的情况下，我们无法对触发器作用表进行其他额外的操作。如果执行其他额外的操作则会抛出如上异常信息。

4.解决方案：

1) 我们知道，出错的原因是因为触发器和DML语句在同一事务管理中，所以方案一便是将触发器和DML语句分成两个单独的事务处理。这里可以使用Pragma autonomous_transaction; 告诉Oracle触发器是自定义事务处理。

SQL语句如下：

[sql] view plain copy

create or replace trigger tr_test
after update on emp
for each row
declare
pragma autonomous_transaction;
begin
update tr_table t set t.sal = (select sal from emp where empno=t.empno and empno=:new.empno);
commit; --此处需要显示提交
end;

注：以上语句并不能实时获得更新的值。。。原因是我们在update emp表后还没来得及提交sal就触发了触发器，这个时候获取到的只能是老的sal值。

2) 在Oracle Trigger中有:new,:old两个特殊变量，当触发器为行级触发器的时候，触发器就会提供new和old两个保存临时行数据的特殊变量，我们可以从俩个特殊的变量中取出数据执行扩张表的DML操作。

SQL语句如下：

[sql] view plain copy

create or replace trigger tr_test
after update on emp
for each row
begin
update tr_table t set t.sal = :new.sal;
end;
/

5. 再次插入数据：

[sql] view plain copy

SQL> update emp set sal=3800 where empno=7788;
已更新 1 行。
SQL> commit;
提交完成。

[sql] view plain copy

SQL> select * from tr_table;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1800 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
<span style="BACKGROUND-COLOR: #009900"</span style=>7788 SCOTT ANALYST 7566 19-4月 -87 3800 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。

2、采用两个触发器（一个行级触发器FOR EACH ROW，一个表级触发器）和一个包来解决。

CREATE OR REPLACE PACKAGE ADATA AS
TYPE T_AID IS TABLE OF A.AID%TYPE INDEX BY BINARY_INTEGER;
TYPE T_ANO IS TABLE OF A.ANO%TYPE INDEX BY BINARY_INTEGER;
V_AID T_AID;
V_ANO T_ANO;
V_NUMENTRIES BINARY_INTEGER := 0;
END ADATA;

---------------------------------------
CREATE OR REPLACE TRIGGER TR_A
BEFORE INSERT OR UPDATE OF AID ON A
FOR EACH ROW
BEGIN
ADATA.V_NUMENTRIES := ADATA.V_NUMENTRIES + 1;
ADATA.V_AID(ADATA.V_NUMENTRIES) := :NEW.AID;
ADATA.V_ANO(ADATA.V_NUMENTRIES) := :NEW.ANO;
END TR_A;

----------------------------------------
CREATE OR REPLACE TRIGGER TR_B
AFTER INSERT OR UPDATE OF AID ON A
BEGIN
V_MAX CONSTANT NUMBER := 5;
V_CUR NUMBER;
V_AID A.AID%TYPE;
V_ANO A.ANO%TYPE;
BEGIN
FOR V_LOOPINDEX IN 1 .. ADATA.V_NUMENTRIES LOOP
V_AID := ADATA.V_AID(V_LOOPINDEX);
V_ANO := ADATA.V_ANO(V_LOOPINDEX);
SELECT COUNT(*) INTO V_CUR FROM A WHERE AID = V_AID;
IF V_CUR > V_MAXS THEN
RAISE_APPLICATION_ERROR(-20000,
'Too many students for major ' || V_AID ||
'because of student ' || V_ANO);
END IF;
END LOOP;
END TR_B;

变异表是指激发触发器的DML语句所操作的表

当对一个表创建行级触发器时，有下列两条限制：

1.不能读取或修改任何触发语句的变异表；
2.不能读取或修改触发表的一个约束表的PRIMARY KEY，UNIQUE 或FOREIGN KEY关键字的列, 但可以修改其他列

例如：有这样一个需求：在更新员工所在部门或向部门插入新员工时，部门中员工人数不超过7人

如果按照下面的触发器写就会使UPDATE操作时报错

CREATE OR REPLACE TRIGGER updatetrigger
BEFORE UPDATE ON EMP
FOR EACH ROW
DECLARE
v_num NUMBER;
BEGIN
SELECT count(*) INTO v_num FROM emp
WHERE deptno = :new.deptno;
IF (v_num > 7) THEN
RAISE_APPLICATION_ERROR(-20001,
'员工数多于'||v_num);
END IF;
END updatetrigger;

ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.UPDATETRIGGER", line 4
ORA-04088: 触发器 'SCOTT.UPDATETRIGGER' 执行过程中出错

如果既想更新变异表，同时又需要查询变异表，那么如何处理呢?

将行级触发器与语句级触发器结合起来，在行级触发器中获取要修改的记录的信息，存放到一个软件包的全局变量中，然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询，并根据查询的结果进行业务处理

例如：

为了实现在更新员工所在部门或向部门插入新员工时，部门中员工人数不超过7人，可以在emp表上创建两个触发器，同时创建一个共享信息的包

CREATE OR REPLACE PACKAGE mutate_pkg
AS
v_deptno NUMBER(2);
END;

CREATE OR REPLACE TRIGGER rmutate_trigger
BEFORE INSERT OR UPDATE OF deptno ON EMP
FOR EACH ROW
BEGIN
mutate_pkg.v_deptno:=:new.deptno;
END;

CREATE OR REPLACE TRIGGER smutate_trigger
AFTER INSERT OR UPDATE OF deptno ON EMP
DECLARE
v_num number(3);
BEGIN
SELECT count(*) INTO v_num FROM emp
WHERE deptno = mutate_pkg.v_deptno;
IF v_num>7 THEN
RAISE_APPLICATION_ERROR(-20003,'这部门的员工太多了 '||
mutate_pkg.v_deptno);
END IF;
END;

这样操作，就不会报ORA-04091: 表SCOTT.EMP 发生了变化,触发器/函数不能读它错误了。

本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接：http://www.linuxidc.com/Linux/2013-11/93146.htm

ORA-04091错误解决

http://asktom.oracle.com/tkyte/Mutate/index.html

Avoiding Mutating Tables

Ok, so you've just recieved the error:

ORA-04091: table XXXX is mutating, trigger/function may not see it

and you want to get around that. This short article will describe and demonstrate the various methods of getting around the mutating table error.

If you are interested in why you are getting it and in what cases you will get it, please see the Oracle Server Application Developers Guide (click here to read it right now -- this link is to technet.oracle.com. You need a password to access this site but you can get one right away for free).

Avoiding the mutating table error is fairly easy. We must defer processing against the mutating or constrainng table until an AFTER trigger. We will consider two cases:

Hitting the ORA-4091 in an INSERT trigger or an UPDATE trigger where you only need access to the :new values
Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access the :old values

Case 1 - you only need to access the :new values

This case is the simplest. What we will do is capture the ROWIDS of the inserted or udpated rows. We can then use these ROWIDS in an AFTER trigger to query up the affected rows.

It always takes 3 triggers to work around the mutating table error. They are:

A before trigger to set the package state to a known, consistent state
An after, row level trigger to capture each rows changes
An after trigger to actually process the change.

As an example -- to show how to do this, we will attempt to answer the following question:

I have a table containing a key/status/effective date combination. When status
changes, the values are propagated by trigger to a log table recording the
status history. When no RI constraint is in place everything works fine.

When an RI trigger enforces a parent-child relationship, the status change
logging trigger fails because the parent table is mutating. Propagating the
values to the log table implicitly generates a lookup back to the parent table
to ensure the RI constraint is satisfied.

I do not want to drop the RI constraint. I realize that the status is
denormalized. I want it that way. What is a good way to maintain the log?

Here is the implementation:

SQL> create table parent
2 ( theKey        int primary key,
3    status        varchar2(1),
4    effDate       date
5 )
6 /
Table created.

SQL> create table log_table
2 (       theKey int references parent(theKey),
3          status varchar2(1),
4          effDate date
5 )
6 /
Table created.

SQL> REM this package is used to maintain our state. We will save the rowids of newly
SQL> REM inserted / updated rows in this package. We declare 2 arrays -- one will
SQL> REM hold our new rows rowids (newRows). The other is used to reset this array,
SQL> REM it is an 'empty' array

SQL> create or replace package state_pkg
2 as
3          type ridArray is table of rowid index by binary_integer;
4
4          newRows ridArray;
5          empty   ridArray;
6 end;
7 /
Package created.

SQL> REM We must set the state of the above package to some known, consistent state
SQL> REM before we being processing the row triggers. This trigger is mandatory,
SQL> REM we *cannot* rely on the AFTER trigger to reset the package state. This
SQL> REM is because during a multi-row insert or update, the ROW trigger may fire
SQL> REM but the AFTER tirgger does not have to fire -- if the second row in an update
SQL> REM fails due to some constraint error -- the row trigger will have fired 2 times
SQL> REM but the AFTER trigger (which we relied on to reset the package) will never fire.
SQL> REM That would leave 2 erroneous rowids in the newRows array for the next insert/update
SQL> REM to see. Therefore, before the insert / update takes place, we 'reset'

SQL> create or replace trigger parent_bi
2 before insert or update on parent
3 begin
4 state_pkg.newRows := state_pkg.empty;
5 end;
6 /
Trigger created.

SQL> REM This trigger simply captures the rowid of the affected row and
SQL> REM saves it in the newRows array.

SQL> create or replace trigger parent_aifer
2 after insert or update of status on parent for each row
3 begin
4 state_pkg.newRows( state_pkg.newRows.count+1 ) := :new.rowid;
5 end;
6 /
Trigger created.

SQL> REM this trigger processes the new rows. We simply loop over the newRows
SQL> REM array processing each newly inserted/modified row in turn.

SQL> create or replace trigger parent_ai
2 after insert or update of status on parent
3 begin
4          for i in 1 .. state_pkg.newRows.count loop
5                  insert into log_table
6                  select theKey, status, effDate
7                    from parent where rowid = state_pkg.newRows(i);
8          end loop;
9 end;
10 /
Trigger created.

SQL> REM this demonstrates that we can process single and multi-row inserts/updates
SQL> REM without failure (and can do it correctly)

SQL> insert into parent values ( 1, 'A', sysdate-5 );
1 row created.

SQL> insert into parent values ( 2, 'B', sysdate-4 );
1 row created.

SQL> insert into parent values ( 3, 'C', sysdate-3 );
1 row created.

SQL> insert into parent select theKey+6, status, effDate+1 from parent;
3 rows created.

SQL> select * from log_table;

    THEKEY S EFFDATE
---------- - ---------
         1 A 04-AUG-99
         2 B 05-AUG-99
         3 C 06-AUG-99
         7 A 05-AUG-99
         8 B 06-AUG-99
         9 C 07-AUG-99

6 rows selected.

SQL> update parent set status = chr( ascii(status)+1 ), effDate = sysdate;
6 rows updated.

SQL> select * from log_table;

    THEKEY S EFFDATE
---------- - ---------
         1 A 04-AUG-99
         2 B 05-AUG-99
         3 C 06-AUG-99
         7 A 05-AUG-99
         8 B 06-AUG-99
         9 C 07-AUG-99
         1 B 09-AUG-99
         2 C 09-AUG-99
         3 D 09-AUG-99
         7 B 09-AUG-99
         8 C 09-AUG-99
         9 D 09-AUG-99

12 rows selected.

Case 2 - you need to access the :old values

This one is a little more involved but the concept is the same. We'll save the actual OLD values in an array (as opposed to just the rowids of the new rows). Using tables of records this is fairly straightforward. Lets say we wanted to implement a flag delete of data -- that is, instead of actually deleting the record, you would like to set a date field to SYSDATE and keep the record in the table (but hide it from queries). We need to 'undo' the delete.

In Oracle8.0 and up, we could use "INSTEAD OF" triggers on a view to do this, but in 7.3 the implementation would look like this:

SQL> REM this is the table we will be flag deleting from.
SQL> REM No one will ever access this table directly, rather,
SQL> REM they will perform all insert/update/delete/selects against
SQL> REM a view on this table..

SQL> create table delete_demo ( a            int,
2                             b            date,
3                             c            varchar2(10),
4                             hidden_date date default to_date( '01-01-0001', 'DD-MM-YYYY' ),
5                             primary key(a,hidden_date) )
6 /
Table created.

SQL> REM this is our view. All DML will take place on the view, the table
SQL> REM will not be touched.

SQL> create or replace view delete_demo_view as
2 select a, b, c from delete_demo where hidden_date = to_date( '01-01-0001', 'DD-MM-YYYY' )
3 /
View created.

SQL> grant all on delete_demo_view to public
2 /
Grant succeeded.

SQL> REM here is the state package again. This time the array is of
SQL> REM TABLE%ROWTYPE -- not just a rowid

SQL> create or replace package delete_demo_pkg
2 as
3      type array is table of delete_demo%rowtype index by binary_integer;
4
4      oldvals    array;
5      empty    array;
6 end;
7 /
Package created.

SQL> REM the reset trigger...

SQL> create or replace trigger delete_demo_bd
2 before delete on delete_demo
3 begin
4 delete_demo_pkg.oldvals := delete_demo_pkg.empty;
5 end;
6 /
Trigger created.

SQL> REM Here, instead of capturing the rowid, we must capture the before image
SQL> REM of the row.
SQL> REM We cannot really undo the delete here, we are just capturing the deleted
SQL> REM data

SQL> create or replace trigger delete_demo_bdfer
2 before delete on delete_demo
3 for each row
4 declare
5      i    number default delete_demo_pkg.oldvals.count+1;
6 begin
7      delete_demo_pkg.oldvals(i).a := :old.a;
8      delete_demo_pkg.oldvals(i).b := :old.b;
9      delete_demo_pkg.oldvals(i).c := :old.c;
10 end;
11 /
Trigger created.

SQL> REM Now, we can put the deleted data back into the table. We put SYSDATE
SQL> REM in as the hidden_date field -- that shows us when the record was deleted.

SQL> create or replace trigger delete_demo_ad
2 after delete on delete_demo
3 begin
4      for i in 1 .. delete_demo_pkg.oldvals.count loop
5          insert into delete_demo ( a, b, c, hidden_date )
6          values
7          ( delete_demo_pkg.oldvals(i).a, delete_demo_pkg.oldvals(i).b,
8            delete_demo_pkg.oldvals(i).c, sysdate );
9      end loop;
10 end;
11 /
Trigger created.

SQL> REM Now, to show it at work...
SQL> insert into delete_demo_view values ( 1, sysdate, 'Hello' );
1 row created.

SQL> insert into delete_demo_view values ( 2, sysdate, 'Goodbye' );
1 row created.

SQL> select * from delete_demo_view;

         A B         C
---------- --------- ----------
         1 09-AUG-99 Hello
         2 09-AUG-99 Goodbye

SQL> delete from delete_demo_view;
2 rows deleted.

SQL> select * from delete_demo_view;
no rows selected

SQL> select * from delete_demo;

         A B         C          HIDDEN_DA
---------- --------- ---------- ---------
         1 09-AUG-99 Hello      09-AUG-99
         2 09-AUG-99 Goodbye    09-AUG-99

All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.

About Me

...............................................................................................................................

● 本文转载自网络http://blog.itpub.net/143904/viewspace-862876/

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

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

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。