关于trigger与table mutating

1、对于after 类型的 for each row 级别的triggers，都不允许在 trigger 中访问本trigger所依赖的table，测试如下：

SQL> create table t1 ( c1 number,c2 varchar2(10));
Table created
SQL> create or replace trigger tri_t1
  2  after insert on t1 for each  row
  3  declare
  4    cvar  varchar2(10);
  5  begin
  6    select 'Y' into cvar from t1 WHERE ROWNUM=1; --这里访问了trigger 本表
  7  end;
  8  /
Trigger created

SQL> insert into t1 values (1,'a');
ORA-04091: table .T1 is mutating, trigger/function may not see it
ORA-06512: at "TRI_T1", line 4
ORA-04088: error during execution of trigger 'TRI_T1'

SQL> insert into t1 select '1','a' from dual;
ORA-04091: table T1 is mutating, trigger/function may not see it
ORA-06512: at "TRI_T1", line 4
ORA-04088: error during execution of trigger 'TRI_T1'

2、对于before 类型的 for each row 级别的triggers，如果使用 insert into ... values 语句触发此trigger ，则在trigger 中访问本table没有问题；
   但如果使用 insert into select .. from 语句触发此trigger ，则在trigger 中访问本table就报ora-04091错误；

   在Oracle 标准的开发文档中有这样的说明：
From the Application Developers Guide
        "There is an exception to this restriction;
        For single row INSERTs, constraining tables are mutating for
        AFTER row triggers, but not for BEFORE row triggers.
        INSERT statements that involve more than 1 row are not considered
        single row inserts."
            "INSERT INTO SELECT ..." are not considered single row
        inserts, even if they only result in 1 row being inserted.

测试如下：
SQL> drop trigger tri_t1;
Trigger dropped

SQL> insert into t1 values (1,'a');       --先插入一条数据，避免ORA-01403: no data found 错误。
1 row inserted
SQL> commit;

SQL> create or replace trigger tri_t1
  2  before insert on t1 for each  row
  3  declare
  4    cvar  varchar2(10);
  5  begin
  6    select 'Y' into cvar from t1 WHERE ROWNUM=1;
  7  end;
  8  /
Trigger created

SQL> insert into t1 values (2,'b');       -- insert  into ... values 没有问题
1 row inserted

SQL> insert into t1 select '3','c' from dual;  -- insert  into ... select .. from 报错
ORA-04091: table T1 is mutating, trigger/function may not see it
ORA-06512: at "TRI_T1", line 4
ORA-04088: error during execution of trigger 'TRI_T1'

from http://space.itpub.net/50897/viewspace-580287

补充一句：此规则对于update ,delete同样适用

 

The Mutating table error is a well-known problem encountered in development; most developers have come across this error.
ORA-04091: table is mutating,
trigger/function may not see it

The basic reason for this error is the way Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating. Mutation will not occur if a single record is inserted in the table (using VALUES clause). If bulk insertion is done or data is inserted from another table mutation will occur.

The mutating error is not only encountered during queries, but also for insert, updates and deletes present in the trigger. Below is a table that explains the various transaction scenarios that involves a trigger and whether it is prone to generate the mutating error. The OPERATION column explains the DML activity being performed and the TYPE column lists the type of trigger created and the execution level.

Case 1: When Trigger on table refers the same table:
---------------------------------------------------------------
OPERATION TYPE MUTATING?
---------------------------------------------------------------
insert before/statement-level No
insert after/statement-level No
update before/statement-level No
update after/statement-level No
delete before/statement-level No
delete after/statement-level No

insert before/row-level Single row Multi-row
No Yes
insert after/row-level Yes
update before/row-level Yes
update after/row-level Yes
delete before/row-level Yes
delete after/row-level Yes
---------------------------------------------------------------
A very simple example is given below.
SQL> create table am27
2 (col1 number,
3 col2 varchar2(30));
Table created.
SQL> create or replace trigger am27_trg
2 before insert or update or delete
3 on am27
4 for each row
5 declare
6 l_chk pls_integer;
7 begin
8 select count(1)
9 into l_chk
10 from am27;
11 - more processing...
12 end;
13 /
Trigger created.
SQL> insert into am27 values (1, 'testing');
1 row created.
SQL> update am27
2 set col1 = 2;
update am27
*
ERROR at line 1:
ORA04091: table SYSTEM.AM27 is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.AM27_TRG", line 4
ORA-04088: error during execution of trigger 'SYSTEM.AM27_TRG'

In the above example, as table AM27 is being queried in the trigger AM27_TRG that is based on the same table, a mutating error is received.

It is also possible for ORA-4091 to be encountered when querying a table other than the table on which the trigger is based! This happens when a foreign key reference is present with an on-delete-cascade option. A row level trigger on the master table will mutate if the detail table is being referred to in the trigger, for a delete transaction. This will only happen if the foreign key on the detail table is created with the on delete cascade option. No mutation occurs if the master table is being referred in a trigger on the detail table.

There is one odd case where mutation may occur when some other table in the trigger is referred to; below is an example of such a condition.

AM10 is a master table. AM10_DTL is the detail table that is related to the master table with the on-delete-cascade option. AM10_BEF_TRG is created on the master table that queries the detail table for some information. Issuing a delete on the master table results in the mutation error.

SQL> create table am10
2 (col1 number, col2 varchar2(10));
Table created.
SQL> create table am10_dtl
2 (col1 number,
3 col2 varchar2(10));
Table created.

SQL> alter table am10 add primary key (col1);
Table altered.
SQL> alter table am10_dtl add foreign key (col1) references am10(col1) on delete cascade;
Table altered.

SQL> create or replace trigger am10_bef_trg
2 before insert or update or delete on am10
3 for each row
4 declare
5 l_chk pls_integer;
6 begin
7 select 1
8 into l_chk
9 from am10_dtl
10 where col1 = :new.col1;
11 dbms_output.put_line('ok');
12 exception
13 when no_data_found then
14 dbms_output.put_line('no dtl recs');
15 end;
16 /
Trigger created.
SQL> insert into am10 values (1, 'amar');
err

1 row created.

SQL> insert into am10 values (2, 'chk');
err

1 row created.

SQL> insert into am10_dtl values(1, 'cooler');

1 row created.

SQL> insert into am10_dtl values (2, 'validator');

1 row created.

SQL> delete from am10 where col1= 1;
delete from am10 where col1= 1
*
ERROR at line 1:
ORA-04091: table SYSTEM.AM10_DTL is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.AM10_BEF_TRG", line 4
ORA-04088: error during execution of trigger 'SYSTEM.AM10_BEF_TRG'

Conclusion
Maintaining a consistent view of the data is an important feature of Oracle. The mutating error conflicts with maintaining a consistent view, therefore, care should be taken to write proper code and avoid such triggers. It is for the developers to write proper logic so that such complications do not arise.

If there is a requirement to update the base table from the row-level trigger, then split the logic across multiple triggers. The required information can be stored in a temporary table, PL/SQL table or package variables when the row-level trigger is executed. A statement-level trigger can then be used to pickup the stored information and apply it to the table.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1698901/viewspace-607992/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1698901/viewspace-607992/