Re: Triggers & Referencial integrity constraints

On 11 Apr 1997 19:06:01 GMT, nolan_at_helios.unl.edu (Michael Nolan) wrote:

>Antonio Galdo <agaldo_at_repsol.es> writes:
>
>>I'm having some problems with triggers and referencial integrity
>>constraints in the tables which fire and are modified by the triggers.
 

>>I think this error is raised because the insertion in table B requires
>>checking table A for integrity. When this check is done, table A is
>>beeing altered by the UPDATE which fired the trigger, i.e., table A is
>>"mutating".
 

>>Is there a way to solve this problem ?
>
>You've got several problems here that would appear to indicate a flaw in
>your logical design.
>
>Ignore the mutating trigger problem for a moment and attempt to duplicate
>the actions of the trigger by hand.
>
>If you attempt to update either 'CODE' or 'YEAR' in table_a, you will
>invalidate the foreign keys for any records in table_b that reference that
>row, violating the foreign key constraint. If you attempt to update 'ADATE',
>you will create a row with a duplicate primary key in table_b, violating the
>primary key constraint.
>--
>Mike Nolan
>nolan_at_tssi.com
>

I disagree here. The issue is the 'mutating' table. Antonio was correct in his assessment of the problem. Table B has a foreign key constraint to a table that is in the process of being updated, therefore Oracle cannot guarantee the consistency of the transaction. This is why the condition is raised. I think we can all agree that the logical design appears flawed from his representation, but sometimes the only way to guarantee referential integrity is by the use of a trigger to insert a row into another table that has a primary key enabled. If the primary key condition is not satisfied then the transaction will be rolled back. Any other comments?

#include <std_disclaimer.h>

Alan Caldera
Sr. Systems Analyst
Michaels Stores, Inc.
acaldera_at_airmail.net

Speed costs money, how fast do you want to go? Received on Sat Apr 12 1997 - 00:00:00 CDT