关于 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'

 

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

转载于:http://blog.itpub.net/50897/viewspace-580287/