ITPub博客

首页 > 数据库 > Oracle > oracle中触发器使用遇到的mutating table

oracle中触发器使用遇到的mutating table

Oracle 作者:封印银月 时间:2014-03-11 15:58:05 0 删除 编辑

mutating table 是指一个当前正在被update,delete,insert语句修改的表,如果在一个行级别的trigger中读取或修改一个mutating table,则往往会遇到ORA-04091错误.例如,如果在trigger中使用了select或dml 语句访问trigger所在的表,则就会收到这个错误。

然而,Oracle8i和9i文档中都没有解释清楚before和after 类型的 row trigger 在对待两种不同的insert语句(insert into ... values ... 与 insert into ... select ...)时的差别:

1、对于after 类型的 for each row 级别的triggers,不论哪种insert语句触发了trigger,都不允许在 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 TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TES.TRI_T1'

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


2、对于before 类型的 for each row 级别的triggers,如果使用 insert into ... values 语句触发此trigger ,则在trigger 中访问本table没有问题;
但如果使用 insert into select .. from 语句触发此trigger ,则在trigger 中访问本table就报ora-04091错误;
只有在Oracle 7标准的开发文档中有这样的说明:
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 TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'

<!-- 正文结束 -->

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

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-06-17