|Notify me of new responses|
I'm trying to set up some triggers for pset5. For example, I have one that removes 'spouse' pointers on deleting a relative:
create or replace trigger widow_spouse before delete on family_relatives for each row begin update family_relatives set spouse = NULL where spouse = :old.relative_id; end;
But this always gives an error 'ORA-04091: table WEBUSER2.FAMILY_RELATIVES is mutating, trigger/function may not see it'
I guess this is because the trigger acts on the same table that triggers it - if so, what's the workaround?
-- Nick Strugnell, July 18, 2000
I don't think there is a very elegant way to do this, but it is possible. The solution is to use the horrid trio pattern, whereby you delay updating the table until the after delete statement level trigger. Basically this:
- Create a temporary table with one column of type relative_id.
- Create a before delete for each row trigger which inserts each relative_id you delete into the temprory table.
- Create a after delete statement level trigger which loops through the temporary table and updates the family_relatives table.
Now, before you blame me for coming up with this idea, you should take a look at /doc/sql/ecommerce.sql. Do a C-s for "horrid". You can also find this in other places in the ACS code (like the bookmarks module) but they use packages. You would have to do that too, if you weren't using > Oracle 8.1.
Why not dispatch will all of this and just add a
deleted_datefield? Then, you could create a
family_relatives_display_viewwhich not only abstracts all those decodes and ||'s, but also has a where clause of
deleted_date is null. This works just like a actually deleting the row, except you don't have to do hard, boring things like cleaning up references. (Don't forget, you've also got that relative_id in the father and mother fields, as well as the
Even if you don't take any of my recommendations, perhaps you'll agree that we should be moving pset 5 questions over to the Problem Set 5 forum?
-- Ryan Campbell, July 19, 2000
create or replace trigger widow_spouse_trg before delete on family_relatives_t for each row begin update family_relatives_t set spouse = NULL where spouse = :old.relative_id; end; Why are you updating a table just before delete? This does not make sence. The mutating table error comes up any time you do one of two things in a trigger 1. declare a cursor referencing the table for which the trigger is created. 2. You attempt to modify the table for which the table is created.
-- Brad Johns, July 26, 2000