trigger_ORA-04091

 

近日学习到了一个trigger中报ORA-04091错误的相关问题，补了关于Oracle table mutating的一课：
mutatingtable 是指一个当前正在被update,delete,insert语句修改的表，如果在一个行级别的trigger中读取或修改一个mutating table，则往往会遇到ORA-04091错误.例如，如果在trigger中使用了select或dml 语句访问trigger所在的表，则就会收到这个错误。
然而，Oracle8i和9i文档中都没有解释清楚before和after 类型的 rowtrigger 在对待两种不同的insert语句(insertinto ... 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 <table_name> SELECT ..." are not considered singlerow
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'
做过上面的试验自己回头想想也是那么回事。Trigger 定义的就是each row的在单行操作时可以通过，而在操作时用批量语句这样在语义检查时就不会过，这也是SQL的安全性的表现。
PL/SQL Developer的调试窗口给的Script如下:
insert intosource_files

(SOURCE_ID, OPER_LIST_ID, SOURCE_NAME,ORI_PATH, OPER_TYPE, PROC_LIST, SWITCH_ID, EXCHANGE_ID, COLLECT_ID, CURR_PROC,CREATED_TIME, SOURCE_FILES_TYPE, SOURCE_STATE, MEMO, LATN_ID, BYTES)
values

(:v0, :v1, :v2, :v3, :v5, :v6, :v7, :v8, :v9,:v10, :v14, :v15, :v16, :v20, :v21, :v22)
按上面的说明来看这个句子在before的trigger执行时是不会出问题的。
可再回头想想我是在界面上批量复制粘贴的。后台脚本应该是个insert intoselect .. from
类型的语句。这是PL/SQL Developer调试器中的一个BUG吧。