Re: updating foreign key with a trigger

Etienne Gourdon (etienne_at_venus.univ-lr.fr) wrote:
: A primary key on DEP, called no_dep varchar(2)
: A foreign key on EMP to DEP :
: dep varchar(2) constraint d1 references DEP (no_dep)
 

: On a UPDATE of DEP table, we wanted to do via a trigger
: an update of all EMP of this department (It's look simple !)
 

: We got the message ORA-04091 "mutating table"
Yes, this is because the index for your parent table is altered by changes to it's child table's key. Since the trigger is firing from your parent table, you get a mutating table error. You can read all about it in chapter 8 of the Server Application Developer's Guide.

: So, we called ORACLE Hotline and they said to use :
: - a BEFORE statement trigger : to copy the EMP table in a temporary table
: - a row trigger : to update the temporary table
: - an AFTER statement trigger : to update the EMP table via the temporary
: table

That's certainly one way to do it. I would do the following:

  Instead of updating the column, I would insert duplicate rows   into the parent table, each new row having the new dep value.

  Execute a similar operation on the child table.

  Delete the rows in the child table having the old dep value.

  Delete the rows in the parent table having the old dep value.

This type of operation would best be handled by a stored procedure, since the processing for the problem will not change. The stored procedure should take parameters at least encmpassing the primary key of your parent table, as well as the column that has a dependency. Note that this is basically the same solution proposed by Oracle, with the exception that no temporary table is involved.

: With this solution, we've got the message ORA-02292 "Integrity constraint
: violated - Child record found" (foreign key constraint)
From what I can tell, you're not deleting the appropriate child rows before updating the parent -- add a step in to what you got from oracle, before updating the parent, to delete the child rows (which are now in the temp table, no need for duplicates :-). Then your code should work properly.

: So, do we need to drop all foreign constraints or is there a better
: solution ?

I would say that dropping the foreign keys is probably not the way to go, based on principle. If you felt the need for a relationship to be maintained at the database level, it probably needs to be maintained there.

Hope this is some help

Jonathan Received on Thu Nov 16 1995 - 00:00:00 CET