I haven't used Oracle since 6, when enforced integrity and triggers
didn't exist, so please bear with me.
If I create a parent and child table with appropriate triggers to
cascade updates and ensure that the data referenced by the child
table exists in the parent table, I get problems. The child table
has a before insert or update trigger that verifies that the value
for the referenced column exists in the parent table. The parent
table has a before update trigger that updates the values of the
referenced columns in the child table to the new value. Now, an
update of a record in the parent table will cause the update trigger
on the parent to fire as well as the update trigger of the child
table to fire for any rows that meet the criteria. Problem is, the
child table's update trigger bombs out because Oracle claims that the
parent record is mutating, and that the child trigger may not be able
to see it. The same problem occurs if I use a foreign key constraint
on the child table instead of the update trigger on the child table.
I'm assuming that this is a common problem (sorry if it's in the FAQ
(is ther a FAQ?)--I don't do newsgroups much anymore) so I'd like to
know how any of you have solved it in the past. Can the trigger on
the parent table disable the child table's trigger and re-enable it
when it's done? Should I let the application do the cascade updates?
Please help, the Oracle core documentation sucks when it comes to
problems like this.. :(
Thanks very much:
-David Vazquez "dvazquez_at_msn.com"
Received on Sun Sep 22 1996 - 00:00:00 CEST
