Re: Trigger problem in Oracle

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/29
Message-ID: <8jg3fh$lne$1@nnrp1.deja.com>#1/1

In article <15f5621a.d24e7cf8_at_usw-ex0108-061.remarq.com>, cheyenne <chia_yenNOchSPAM_at_hotmail.com.invalid> wrote:
> Table A has a trigger to insert the record into table B.
> Table B has a trigger to insert record into table A when it
> is update.
>
> How to set the trigger off fro table A or table B when the
> record is inserted by the trigger of the other table?
>
> thanks
>
> * Sent from AltaVista http://www.altavista.com Where you can also
find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful
>

use a package variable that the triggers will inspect and set. In this way you can avoid the recursion. for example:

ops$tkyte_at_8i> create table a ( msg varchar2(50) );

Table created.

ops$tkyte_at_8i> create table b ( msg varchar2(50) );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package state_pkg   2 as
  3 dont_do_anything boolean default FALSE;   4 end;
  5 /

Package created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace trigger a_aifer 2 after insert on a for each row
3 begin

  4      if ( NOT state_pkg.dont_do_anything )
  5      then
  6          state_pkg.dont_do_anything := TRUE;
  7          begin
  8              insert into b values ( :new.msg || ' inserted by A
Trigger' );
  9              state_pkg.dont_do_anything := FALSE;
 10          exception when others then
 11              state_pkg.dont_do_anything := FALSE;
 12          end;
 13      end if;

14 end;
15 /

Trigger created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace trigger b_aifer

2 after insert on b for each row
3 begin

  4      if ( NOT state_pkg.dont_do_anything )
  5      then
  6          state_pkg.dont_do_anything := TRUE;
  7          begin
  8              insert into a values ( :new.msg || ' inserted by B
Trigger' );
  9              state_pkg.dont_do_anything := FALSE;
 10          exception when others then
 11              state_pkg.dont_do_anything := FALSE;
 12          end;
 13      end if;

14 end;
15 /

Trigger created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into a values ( 'Hello' );

1 row created.

ops$tkyte_at_8i> insert into b values ( 'World' );

1 row created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> select * from a;

MSG

Hello
World inserted by B Trigger

ops$tkyte_at_8i> select * from b;

MSG

Hello inserted by A Trigger
World

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Jun 29 2000 - 00:00:00 CDT