Re: Referential Integrity with Triggers problem
Date: 1996/09/23
Message-ID: <32460194.983804_at_dcsun4>#1/1
To see how to do update cascade, with FULL declaritive RI enabled, goto http://govt.us.oracle.com follow the link to Tech Center/downloadable utilities.
A paper discussing the issue along with the solution (and pl/sql to automatically generate the triggers for you) is available for download there as well.
On 22 Sep 96 01:07:57 -0700, dvazquez_at_msn.com (David Vazquez) wrote:
>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"
>
>
>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Sep 23 1996 - 00:00:00 CEST