Re: Referential Integrity with Triggers problem

> 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.

There are two solutions that I know of. The first is to use the Thomas Kyte's package, which you can find at the Tech Center at http://govt.us.oracle.com. The second is to use the example triggers printed in Oracle's interactive magazine (accessible from the site above), on June 10, '96. The latter do just what you've got, except that they use a package containing "busy" variables, which the cascaded update trigger sets. The foreign key trigger checks this variable, and doesn't enforce the constraint if true (because the cascaded update trigger will be enforcing it anyway).

> The same problem occurs if I use a foreign key constraint
> on the child table instead of the update trigger on the child table.

Yep. If you're going to do relationships between tables with triggers, you've got to do *all* relationships between those tables with triggers.

> 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)

It wasn't when I looked, last month. There are a number of Oracle FAQs around, and they generally cross-link to each other, but this should be in one of them by now. :-(

steve Received on Tue Sep 24 1996 - 00:00:00 CEST