Re: Recursive Triggers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/04/04
Message-ID: <352997d3.13488375_at_192.86.155.100>


A copy of this was sent to Marty Manix <manixm_at_ix.netcom.com> (if that email address didn't require changing) On Tue, 31 Mar 1998 20:18:00 -0500, you wrote:

>I have a table structure that uses a parent/child relationship. A
>record can have a parent which is the primary key of that parent. The
>record at the top of the tree has no parent.
>
>I would like to have updates to a child record be reflected or rolled up
>to each of the parent records above the record being updated. I had
>hoped to use an update database trigger that only updates the parent,
>which will re-fire the trigger to update the parent's parent, etc. Kind
>of like a recursive firing of the trigger until the record no longer has
>a parent.
>
>I keep getting errors of the ilk... Table is mutating; trigger or
>procedure may not know of changes.
>
>Any ideas?
>
>Marty

We can do it in an AFTER trigger. We'll use a package to capture the primary keys of the updated child records and to store a flag. Then, we will use a BEFORE UPDATE trigger to reset the package prior to the update starting (this trigger will look at a flag in the package as well to prevent recursive firing). We'll use a ROW trigger to capture the number of child records updated and their primary keys (again, using that flag to prevent recursive firing). Then, in an AFTER trigger we will set the flag to prevent the triggers from firing again and then cascade the update from the child upto the parent records using a connect by (a recursive query)...

A full example follows:

SQL>

SQL> create table T ( id	     int primary key,
  2  				      pid    int references t(id),
  3  				      data   varchar2(20)

  4 );
Table created.

SQL> create index t_pid_idx on t(pid);
Index created.

SQL> create or replace package t_pkg
  2 as
  3 type array is table of number index by binary_integer;   4

  4  	     ids	     array;
  5  	     cnt     number;
  6  
  6  	     intrigger boolean default FALSE;
  7 end;
  8 /
Package created.

SQL> create or replace trigger t_bu
  2 before update on T
  3 begin

  4  	     if ( not t_pkg.intrigger ) then
  5  		     t_pkg.cnt := 0;
  6  	     end if;

  7 end;
  8 /
Trigger created.

SQL> create or replace trigger t_aufer
  2 after update on T
  3 for each row
  4 begin

  5  	     if ( not t_pkg.intrigger ) then
  6  		     t_pkg.cnt := t_pkg.cnt + 1;
  7  		     t_pkg.ids( t_pkg.cnt ) := :new.id;
  8  	     end if;

  9 end;
 10 /
Trigger created.

SQL> create or replace trigger t_au
  2 after update on T
  3 begin

  4  	     if ( not t_pkg.intrigger ) then
  5  	     begin
  6  		     t_pkg.intrigger := TRUE;
  7  
  7  		     for i in 1 .. t_pkg.cnt loop
  8  			     update T
  9  			    set data = ( select data from T where id = t_pkg.ids(i) )
 10  			      where id in
 11  				( select id
 12  				    from T
 13  				start with id=(select pid from T where id = t_pkg.ids(i))
 14  					connect by id = prior pid );
 15  		     end loop;
 16  		     t_pkg.intrigger := FALSE;
 17  	     exception
 18  		     when others then
 19  			     t_pkg.intrigger := FALSE;
 20  			     raise;
 21  	     end;
 22  	     end if;

 23 end;
 24 /
Trigger created.

Now to test it....
SQL> insert into t values ( 1, NULL, 'data for 1' ); 1 row created.
SQL> insert into t values ( 2, 1, 'data for 2' ); 1 row created.
SQL> insert into t values ( 3, 2, 'data for 3' ); 1 row created.
SQL> insert into t values ( 4, NULL, 'data for 4' ); 1 row created.
SQL> insert into t values ( 5, 4, 'data for 5' ); 1 row created.
SQL> insert into t values ( 6, 5, 'data for 6' ); 1 row created.

Original Data...
SQL> select * from t;

        ID PID DATA

---------- ---------- --------------------
         1            data for 1
         2          1 data for 2
         3          2 data for 3
         4            data for 4
         5          4 data for 5
         6          5 data for 6

6 rows selected.

After update of one child with 2 parents SQL> update t set data = 'new data' where id = 3; 1 row updated.

SQL> select * from t;

        ID PID DATA

---------- ---------- --------------------
         1            new data
         2          1 new data
         3          2 new data
         4            data for 4
         5          4 data for 5
         6          5 data for 6

6 rows selected.

After update of one child with 1 parent
SQL> update t set data = 'newer data' where id = 5; 1 row updated.

SQL> select * from t;

        ID PID DATA

---------- ---------- --------------------
         1            new data
         2          1 new data
         3          2 new data
         4            newer data
         5          4 newer data
         6          5 data for 6

6 rows selected.

After update of two children one with 1 parent other with 2 SQL> update t set data = 'newest data' where id in ( 2, 6 ); 2 rows updated.

SQL> select * from t;

        ID PID DATA

---------- ---------- --------------------
         1            newest data
         2          1 newest data
         3          2 new data
         4            newest data
         5          4 newest data
         6          5 newest data

6 rows selected.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Apr 04 1998 - 00:00:00 CEST

Original text of this message