Re: Mutating Table Insert Situation

On Tue, 24 Mar 1998 09:08:57 -0600, drpaner_at_intrex.net wrote:

>I suspect that Oracle performs a Select statement on the Master table
>behing-the-scenes to verify the foreign key integrity and that is causing the
>error. I have read the newsgroups buy did not see anyone coming up with this
>error without performing a Select statement in the trigger.
>
>Does anyone know of a workaround on this? Any help would be greatly
>appreciated!

I suspect that your problem is the foreign key from your detail table to your parent table. If you don't have one in place, I'm obviously incorrect, but I'm willing to wager that you *do* have on in place (after all, who wants orphaned detail rows).

This foreign key causes the problem because the keys of the parent table has been changed and the changes haven't been committed yet. Therefore, any attempt to modify the detail table (which must check the foreign key) fails because the parent table has been modified.

There are a number of ways you can handle this:

• Remove the foreign key from your detail table. This isn't a recommended option, but it is an option.
• Allow the perfectly normal situation of a parent which has no detail rows to exist. If it really bugs you, you could schedule a cron job or something to periodically check for parents without children and create a child for any childless parent.
• Have your application explicity create the parent row, commit, and then create the default detail rows.

I hope that one of these options helps solve your problem.

Jonathan Ingram Received on Wed Mar 25 1998 - 00:00:00 CST