trigger problems...

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?

Cheers,
Nick

-- Nick Strugnell, July 18, 2000

Answers

Horrid Trio Pattern

Nick,

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:

1. Create a temporary table with one column of type relative_id.
2. Create a before delete for each row trigger which inserts each relative_id you delete into the temprory table.
3. 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_date field? Then, you could create a family_relatives_display_view which 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 family_photo_relative_map and family_story_relative_map.)

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?

Good Luck...

-- 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

-- Brad Johns, July 26, 2000