Re: PL/SQL: Mutating Table

danhw_at_aol.com (DanHW) wrote:

>Valentin wrote...
>>
>>Environment: Oracle 7.3, running on an NT box.
>>
>>I have an update trigger that is very simple:
>>
>>CREATE OR REPLACE TRIGGER article_FNAME
>>AFTER UPDATE OF FNAME ON mfs.article FOR EACH ROW
>>BEGIN
> > IF (:old.FNAME IS NOT NULL) THEN
>> INSERT INTO mfs.arttrans
>>(AN,DTUPDATE,OPERINIT,UPDATETYPE,UPDATEPROCESS)
>> VALUES (:old.AN,SYSDATE,:old.PRINIT,'C','A') ;
>> END IF ;
>>END ;
 
>>When I try to update article.FNAME field, I get the error message:
 

>>ERROR at line 1:
>>ORA-04091: table MFS.ARTICLE is mutating, trigger/function may not see it
>>ORA-06512: at "MFS.ARTICLE_FNAME", line 4
>>ORA-04088: error during execution of trigger 'MFS.ARTICLE_FNAME'
 

>>Any idea why this happens?
 

>The mutating table error is due to an attempt to update a row in another
>table that has a foreign key relationship to the table on which the
>trigger is located. In your case, you probably have a foreign key between
>ARTICLE and ARTTRANS.

>Dan

Try changing the AFTER in the trigger to a BEFORE. BEFORE means that the trigger fires before any referential integrity is checked. Received on Wed Mar 26 1997 - 00:00:00 CST