Re: PL/SQL: Mutating Table

Valentin Maier wrote:
>
> 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'

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint.

The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement.

As a result, you may violate this rule.

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.