RE: ** table triggers

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 30 Jul 2003 16:39:25 -0800
Message-ID: <F001.005C7E8B.20030730163925@fatcity.com>

-----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: Jacques.Kilchoer_at_quest.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Jul 30 2003 - 19:39:25 CDT