Re: PL/SQL: Mutating Table

On Wed, 12 Mar 1997 15:29:09 +0000, Valentin Maier <maier_at_mailman.epnet.com> 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?

I suspect that the insert statement in your trigger is affecting one of the table's foreign key constraints. If you add a new value to the index on the other table and there's a foreign key, your statement can't continue processing because the fk index is now "locked".

the only solution to this problem is to (either temporarily or permanently) disable the foreign key constraint that is causing the problem.

Jonathan Received on Sun Mar 16 1997 - 00:00:00 CST