Re: Mutating Tables...how to handle..?

Michael Joseph wrote:
>
>
> Does anyone know the best way to deal with this...?
>
> I have a row level trigger that fires AFTER I insert a new record in table
> A to insert a set of child rows in the child table B which of course has a
> Foreign Key constraint defined to A.
>
> The trigger fires and returns the message 'Table A is Mutating any changes
> made may be lost" and nothing gets inserted into table B.
>
> What a pain....
>
> Thanks for the help...
>

There IS a way, but it is painful.

You have to implement the Foriegn Key constraint yourself, rather than have Oracle do it for you. There are examples of how to do this in the manuals. Follow them exactly. The locking is tricky, but the examples are right.

What I do is to leave the original FK constraint but disable it. I write my own before row triggers to enforce the FK constraint.

Now, if I want to write an after row trigger on table A which populates B I will hit the mutating table problem in my own code. The before row trigger on B won't be able to select from A to check the constraint.

The solution depends on the fact that the triggers on B can safely "trust" those on A. Before doing the insert, the after row trigger on A can set a package variable to indicate to the before row trigger on B that this insert is OK, and that the FK constraint does not need checking. It is VITAL that the package variable be unset on ALL routes out of the trigger on A. Use an EXCEPTION WHEN OTHERS clause. Received on Mon Apr 29 1996 - 00:00:00 CEST