Re: Triggers & Referencial integrity constraints

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