Jonathan, thank you again for your help. For posterity, I'm going to write about a problem I ran into here. I searched the bboard and found no mention of this, so I thought I'd write a little bit about it:
I set up two tables, FOO and BAR, as follows:
SQL> create table foo (
2 id integer primary key,
3 weight number
4 );
Table created.
SQL> create table bar (
2 foo_id references foo,
3 child_id references foo
4 );
Table created.
SQL> select * from foo;
ID WEIGHT
---------- ----------
1 35
2 15
3 20
4 10
5 10
SQL> select * from bar;
FOO_ID CHILD_ID
---------- ----------
1 2
1 3
3 4
3 5
Thus, the two tables describe a tree structure like this:
#1=35kilos
/
#2=15k #3=20k
/
#4=10k #5=10k
The weight (in kilos) is a sum of the children nodes.
If I add 5k to #4, it should also add 5k to #3 and #1. So I write my trigger (I am pretty inexperienced at this):
SQL> create or replace trigger foo_trigger
2 before update on foo
3 for each row
4 begin
5 update foo set weight = weight + (:new.weight - :old.weight)
6 where id = (select foo_id from bar where child_id = :old.id)
7 ;
8 end;
9 /
Trigger created.
SQL> update foo set weight = 20 where id = 4;
update foo set weight = 20 where id = 4
*
ERROR at line 1:
ORA-04091: table INTRANET.FOO is mutating, trigger/function may not see it
ORA-06512: at "INTRANET.FOO_TRIGGER", line 2
ORA-04088: error during execution of trigger 'INTRANET.FOO_TRIGGER'
Welcome to the world of mutating tables. I found a good resource on this at
http://www.cs.purdue.edu/homes/mcclure/cs448/info/trigger.html.
Tomorrow, I'll continue working on this. If anyone has some quick pointers, I would of course appreciate it. The manuals and books don't seem to be helping here.
