Re: Recursive Triggers
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