Re: foreign key and triggers

There is an alternative to droping the constraint which many would suggest is a dangerous idea. See Mutating Table Error in the Oracle Application Developers Guide.  

When your trigger fires, at the row level, you are updating a child table which, because of the referential integrity constraint, must read the parent table, table x. Oracle defines a mutating table as a table which is being updated due to an insert/update/or delete or one being updated as a result of a delete cascade or update cascade. The problem is you cannot read a table, cannot update a table, cannot use a column in a join of a table which is mutating.  

There is a quick and dirty solution using views which only works if there is no index - nevermind, I forgot you were updating table y. Anyway, you need to use PL/SQL tables to capture :new column values at the row level and then do your updates in after statement triggers.  

There is a discussion of this in the October issue of Database Programming and Design Magazine. This issue has code examples which will help you with this messy problem. If you can't get a copy, email me and I can get you the published's number. It's Miller Freeman in San Francisco which publishes DBMS, Dr Dobbs, and Database Programming and Design.
Hope this helps.
Kevin Owens, Gaithersburg, MD. Received on Tue Oct 04 1994 - 04:58:02 CET