Ora 4091 Cn

Oracle 的Trigger 中不能操作基表
[ 2005/11/23 14:57 | Author:hello_jack ]
大 | 中 | 小

在将SYBASE的Trigger移植到ORACLE的时候发现一个问题,

就是在一张表的Trigger中不能含有操作该基表的SQL,

例如STOCKINFO的Trigger里不有操作STOCKINFO的SQL。

那加权平均价如何取呢?

现在只能在原平均价的基础再与新价进行加权平均,结果是一样的。

注意:

AFTER UPDATE的Trigger不能操作基表,只有在BEFORE INSERT的Trigger里才能操作基表。

BEFORE INSERT的Trigger只有用INSERT INTO T1(COL1) VALUES('1');才有效,

INSERT INTO T1(COL1) SELECT '1' FROM DUAL;也是无效的。

例如:

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'

并且:old和:new只有在行级(for each row)的Trigger有效。

网上原文:
ORA-04091与 table mutating

近日解决了一个trigger中报ORA-04091错误的问题,补了关于Oracle table mutating的一课:

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

我们的开发人员因为不知道这个特别情况,近日在修改一个存储过程时候,将原来的insert values 写法改成了insert select 写法,而trigger 又是before类型的,导致出现了ORA-04091错误,搞得分析了好久也没有头绪。

其实,在metalink中有一篇note说到了:

文档 ID: 注释:132569.1
主题: ORA-4091 on BEFORE ROW TRIGGER with INSERT statement
类型: PROBLEM
状态: PUBLISHED
内容类型: TEXT/X-HTML
创建日期: 16-JAN-2001
上次修订日期: 09-AUG-2004

Problem Description


You want to do an insert into a table that has a BEFORE row Trigger.

When you hard code the values into the INSERT statement, the trigger works fine.

For example:

INSERT
INTO content (cont_name,cont_seg,cat_seq)
VALUES('blah',100,200);

1 row created.

However, your trigger errors with ERROR ORA-4091 with
INSERT INTO…select statement:

INSERT
INTO content (cont_name,cont_seq,cat_seq) (select….from category);

ERROR at line 1:
ORA-4091: table <schema>.CONTENT is mutating, trigger/function may not see it
ORA-6512: at "<schema>.INS_CONTENT", line 4
ORA-4088: error during execution of trigger '<schema>.INS_CONTENT'

TRIGGER:

CREATE OR REPLACE trigger INS_CONTENT
BEFORE INSERT on CONTENT
FOR EACH ROW
DECLARE
max_sort number;
BEGIN
SELECT max(cont_sort) INTO max_sort FROM CONTENT;
IF max_sort IS NOT NULL AND max_sort!= 99999 THEN
IF :new.cont_sort IS NULL THEN
:new.cont_sort := max_sort +1;
END IF;
END IF;
SELECT SEQ_CONT_SEQ.nextval INTO :new.CONT_SEQ from dual;
END;

Explanation


Error: ORA 4091
Text: table %s.%s is mutating, trigger/function may not see it


Cause: A trigger (or a user defined PL/SQL function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

Explanation:
You cannot look at or modify the table that is mutating.

Note:
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.

RELATED DOCUMENTS


Oracle Application Developer's Guide (A68003-01)
Chapter 'Using Database Triggers', page 13-22)

Reference:
http://www.skyage.cn/blog/read.php?133