触发器错误！！ORA-04091:表..发生了变化，触发器/函数不能读，---在线等待！！

[cqxhhe]

aa表为表头
bb表为名细，
bb上建有触发器，删除记录时需要读取表头的信息，并据此做相应的处理；

aa上用触发器实现aa-->bb的级连删除

脚本如下：
直接删除bb表记录没有问题，但试图删除aa表记录时出错；
可能是执行操作的顺序有问题,即级连删除时不能访问表头（以被修改）
但要想实现上面的功能应该怎么修改呢？
=================================================================

create table aa(
no varchar2(5),
flag varchar2(1)
);

create table bb(
no varchar2(5),
dd varchar2(10));

create or replace trigger ord_aa_tr before delete on aa for each row
begin
delete from bb where no=:old.no;
end ord_aa_tr;
/

create or replace trigger ord_bb_tr before delete on bb for each row
declare
q varchar2(10);
begin
select flag into q from aa where no=:OLD.no;
--......其它处理
end ord_aa_tr;
/

insert into aa values('001','1');
insert into bb values('001','abc');

delete from aa;

ERROR 位于第 1 行:
ORA-04091: 表 CCDUSER.AA 发生了变化，触发器/函数不能读
ORA-06512: 在"CCDUSER.ORD_BB_TR", line 4
ORA-04088: 触发器 'CCDUSER.ORD_BB_TR' 执行过程中出错
ORA-06512: 在"CCDUSER.ORD_AA_TR", line 2
ORA-04088: 触发器 'CCDUSER.ORD_AA_TR' 执行过程中出错

================================================================

[beckhambobo]

select flag into q from aa where no=:OLD.no;
exception
when others then
...
end;

但楼主语句没有涉及循环触发。

[cqxhhe]

select flag into q from aa where no=:OLD.no;
exception
when others then
...
end;

这样实现不了功能啊
只是在错误的时候执行..语句；

怎么才能在级连删除的时候得到表头的信息呢？非得再建一个表吗？

[bzszp]

对aa表执行行级触发器的时候
触发bb表的触发器，读aa表中数据//不可以这样

[cqxhhe]

那如果要实现这样的功能可怎么办呢？
只能再建一个表吗？

[cqxhhe]

up

[cqxhhe]

Doc ID: Note:74859.1
Subject: ORA-04091 Mutating Table Explanation and Workarounds
Type: BULLETIN
Status: PUBLISHED
Creation Date: 11-OCT-1999
Last Revision Date: 20-JUL-2000
Copyright ? Oracle Corp.

PURPOSE
The following bulletin discusses concepts of cascade update, delete, and
insert and how to avoid the mutating table error.

SCOPE & APPLICATION
For users requiring the ability to cascade update, delete or insert whilst
being able to maintain referential integrity between objects.

RELATED DOCUMENTS
Oracle7 Server Concepts Guide, Chapter 15
Oracle7 Server Application Developer's Guide, pages 8-9, 8-20


The purpose of this paper is to illustrate to those customers who require one of the following functional capabilities whilst being able to maintain referential integrity among objects:
o Cascade Update
o Cascade Delete
o Cascade Insert

For cascade Update and Insert functions, using stored triggers and procedures will result in an ORA-04091 - "Table <table_name> is mutating" error.

ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
Cause: A trigger (or a user defined plsql 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.

ORA-04091 is a very common error that occurs with triggers if triggers are not managed properly. A full understanding of triggers will help you avoid that error.

A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from.

If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

It must be stressed that this solution should ONLY be used to overcome DML restrictions imposed on triggers in order to maintain referential integrity. Whenever possible it is recommended that normal declarative integrity should be used to maintain foreign key integrity. Enforcing this integrity through
stored triggers and procedures will have an effect on performance compared with declarative integrity.

[beckhambobo]

楼主意思是否每论删除a、b都想得到flag值呢?
select flag into q from aa where no=:OLD.no;
那可以用函数来实现了。

create or replace function get_flag(p_id in varchar2)
return varchar2
as
v_q varchar2(10);
begin
select flag into q from aa where no=p_id;
return v_q;
end;
/

create or replace trigger ord_aa_tr before delete on aa for each row
declare
rt varchar2(10);
begin
rt:=get_flag(:old.no);
delete from bb where no=:old.no;
end ord_aa_tr;
/

create or replace trigger ord_bb_tr before delete on bb for each row
declare
q varchar2(10);
begin

q:=get_flag(:OLD.no);
--......其它处理
end ord_aa_tr;
/

[bzszp]

形成循环了！
这样不行

[cqxhhe]

怎么解决呢？

[bzszp]

可以通过另外一个表
保存需要的信息
通过触发器保存起来
然后再取出来用

[cqxhhe]

the problem is that i select datas out of move_task and want to update fields in the same table in one trigger.

The solution is to select all datas from move_task in a before statement trigger and put the result in package variables. After that select the stored datas out of the package variables in the before update trigger.

??

[cqxhhe]

就是级连删除时反过来又去读表头的信息处的错

只能再建一个表吗？

我觉得是级连触发的问题，可不可以不增加表？