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.??
exception
when others then
...
end;但楼主语句没有涉及循环触发。
exception
when others then
...
end;这样实现不了功能啊
只是在错误的时候执行..语句；怎么才能在级连删除的时候得到表头的信息呢？非得再建一个表吗？
触发bb表的触发器，读aa表中数据//不可以这样
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 InsertFor 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.
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;
/