Re: Novel scenario of trigger mutation - take a look

badri_at_cc.gatech.edu (badri) wrote:
>
>Hi:
>
> It is the favorite mutation question of triggers. I
>know the cause of the error, only I cannot figure out why I am
>violating it!
>
> I have a before insert or update trigger that does a
>select on the same table on which the trigger is fired from. I
>thought that I cannot update/do some action that causes my trigger
>to fire again - trigger mutation is only caused by multiple firing
>of the same trigger. Will select on the same table (same row included
> or not included) also cause mutation? I am baffled!
>
> I enclose the skeleton text of my trigger for further details:
>
 [trigger code deleted]
>Please help,
>
>thanks a lot,
>
>-Badri

You can't even do a select on the same table which caused the trigger to fire. At least you can't on an update or delete. My experience is that it will let you do it if the trigger fired as a result of an insert. I don't know why. The way I have found to get around it is to have a trigger which fires before update or delete for each row which inserts the :old or :new values into a temporary table. Then I have another trigger which fires after update or delete at the statement level which reads the temporary table, does whatever other things need to be done, and deletes from the temporary table. For inserts I just have a before insert trigger for each row which does what I need to do. Like I said I never get the error on an insert.

I hope this helps.

Ken Denny
Insight Industries, Inc.
RTP, NC All opinions are my own and not those of my employer. Received on Mon Jan 15 1996 - 00:00:00 CET