-----Original Message-----
I have a two tables A and B. Both have a field expected_delay_now and when it gets
updated by a user in either table I want update it in corresponding row in the other
table. However when this update is as a result of a trigger (and not user updated) how
do I skip the update thru the trigger??? Thanks
Answer:
I see that you have another question on the scope of package variables, so it seems
like you have already found the solution. But here's an example to avoid that
"mutating table" error.
create table t1
(id number, name varchar2 (30), expected_delay_now number) ;
create table t2
(type varchar2 (1), type_desc varchar2 (10), expected_delay_now number) ;
create package p
as
t1_upd boolean := false ;
t2_upd boolean := false ;
end p ;
/
create trigger t1_b4u_st
before update on t1
begin
p.t1_upd := true ;
end ;
/
create trigger t1_b4u_row
before update on t1
for each row
begin
if not p.t2_upd
then
update t2 set expected_delay_now = :new.expected_delay_now
-- where
;
end if ;
end ;
/
create trigger t1_afu
after update on t1
begin
p.t1_upd := false ;
end ;
/
create trigger t2_b4u_st
before update on t2
begin
p.t2_upd := true ;
end ;
/
create trigger t2_b4u_row
before update on t2
for each row
begin
if not p.t1_upd
then
update t1 set expected_delay_now = :new.expected_delay_now
-- where
;
end if ;
end ;
/
create trigger t2_afu
after update on t2
begin
p.t2_upd := false ;
end ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
