Search了metalink,发现这个问题首先要理解 trigger 与 table mutating(变异)的概念,详细请参考附件的metalink的2个文档;简单归纳,就是2个原则: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 createdSQL> insert into t1 values (1,'a'); ORA-04091: table PASYS.T1 is mutating, trigger/function may not see it ORA-06512: at "PASYS.TRI_T1", line 4 ORA-04088: error during execution of trigger 'PASYS.TRI_T1'SQL> insert into t1 select '1','a' from dual; ORA-04091: table PASYS.T1 is mutating, trigger/function may not see it ORA-06512: at "PASYS.TRI_T1", line 4 ORA-04088: error during execution of trigger 'PASYS.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 <table_name> SELECT ..." are not considered single row inserts, even if they only result in 1 row being inserted.测试如下: SQL> drop trigger tri_t1; Trigger droppedSQL> 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 createdSQL> insert into t1 values (2,'b'); -- insert into ... values 没有问题 1 row insertedSQL> insert into t1 select '3','c' from dual; -- insert into ... select .. from 报错 ORA-04091: table PASYS.T1 is mutating, trigger/function may not see it ORA-06512: at "PASYS.TRI_T1", line 4 ORA-04088: error during execution of trigger 'PASYS.TRI_T1' 根据以上理论,检查出问题的pcis trigger PASYS.TRI_DC_KCOLDUE 属于before 类型的 for each row 级别的trigger,确实在trigger 中访问了trigger 本身的表 kcoldue , 但只有满足 if :new.cedrno is not null ,并且关联的 t_edrsmt 表的c_edr_rsn字段值为'23','10','22'三种类型时,才访问了trigger 本身的表 kcoldue 。 这就可以解释为什么只有部分数据下发的时候才报ora-04091 错误 。根据吴聪颖提供的无法下发的数据,得到了验证:SQL> select error_msg from epcisacct.receipt_down_log t where key_no='U16040005C00224' and rownum=1; ERROR_MSG -------------------------------------------------------------------------------- ORA-04091: table PASYS.KCOLDUE is mutating, trigger/function may not see it ORA-06512: at "PASYS.TRI_DC_KCOLDUE", line 10 ORA-06512: at "PASYS.TRI_DC_SQL> select cedrno from kcoldue where crctno='U16040005C00224'; CEDRNO -------------------- 31604000306005006143SQL> select c_edr_rsn from t_edrsmt where c_edr_no='31604000306005006143'; C_EDR_RSN --------- 10 --满足 trigger 中的条件。 咨询了黄伟,下发的procedure 中确实使用了 insert into pcis.kcoldue select * from epcis.kcoldue 的语法,而且是近期才改成的这种语句,以前是 insert into .. values 的语法。 解决方法:在下发程序中: select * into <一个基于kcoldue rowtype类型的数组变量> from epcis.kcoldue where 主键字段=主键字段值 , insert into pcis.kcoldue values (数组变量的字段列表) 不建议使用动态组装 insert into values 语句的方式,这样繁琐,而且没有使用绑定变量,大大影响性能。
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 createdSQL> insert into t1 values (1,'a');
ORA-04091: table PASYS.T1 is mutating, trigger/function may not see it
ORA-06512: at "PASYS.TRI_T1", line 4
ORA-04088: error during execution of trigger 'PASYS.TRI_T1'SQL> insert into t1 select '1','a' from dual;
ORA-04091: table PASYS.T1 is mutating, trigger/function may not see it
ORA-06512: at "PASYS.TRI_T1", line 4
ORA-04088: error during execution of trigger 'PASYS.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 <table_name> SELECT ..." are not considered single row
inserts, even if they only result in 1 row being inserted.测试如下:
SQL> drop trigger tri_t1;
Trigger droppedSQL> 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 createdSQL> insert into t1 values (2,'b'); -- insert into ... values 没有问题
1 row insertedSQL> insert into t1 select '3','c' from dual; -- insert into ... select .. from 报错
ORA-04091: table PASYS.T1 is mutating, trigger/function may not see it
ORA-06512: at "PASYS.TRI_T1", line 4
ORA-04088: error during execution of trigger 'PASYS.TRI_T1'
根据以上理论,检查出问题的pcis trigger PASYS.TRI_DC_KCOLDUE 属于before 类型的 for each row 级别的trigger,确实在trigger 中访问了trigger 本身的表 kcoldue ,
但只有满足 if :new.cedrno is not null ,并且关联的 t_edrsmt 表的c_edr_rsn字段值为'23','10','22'三种类型时,才访问了trigger 本身的表 kcoldue 。
这就可以解释为什么只有部分数据下发的时候才报ora-04091 错误 。根据吴聪颖提供的无法下发的数据,得到了验证:SQL> select error_msg from epcisacct.receipt_down_log t where key_no='U16040005C00224' and rownum=1;
ERROR_MSG
--------------------------------------------------------------------------------
ORA-04091: table PASYS.KCOLDUE is mutating, trigger/function may not see it
ORA-06512: at "PASYS.TRI_DC_KCOLDUE", line 10
ORA-06512: at "PASYS.TRI_DC_SQL> select cedrno from kcoldue where crctno='U16040005C00224';
CEDRNO
--------------------
31604000306005006143SQL> select c_edr_rsn from t_edrsmt where c_edr_no='31604000306005006143';
C_EDR_RSN
---------
10 --满足 trigger 中的条件。
咨询了黄伟,下发的procedure 中确实使用了 insert into pcis.kcoldue select * from epcis.kcoldue 的语法,而且是近期才改成的这种语句,以前是 insert into .. values 的语法。
解决方法:在下发程序中:
select * into <一个基于kcoldue rowtype类型的数组变量> from epcis.kcoldue where 主键字段=主键字段值 ,
insert into pcis.kcoldue values (数组变量的字段列表) 不建议使用动态组装 insert into values 语句的方式,这样繁琐,而且没有使用绑定变量,大大影响性能。