Re: Trigger Problem - Server 7.3.3

From: Jack Ploeg <jack_at_trisol.nl>
Date: 2000/05/29
Message-ID: <393212ce.1479360_at_news.jackploeg.tmfweb.nl>


Ira,

the problem is that you can't perform an update in a row-level trigger, if that update would trigger the same trigger again. You're not the first one to encounter this 'mutating table' problem, and there is a generic solution for it:

Create a package, that contains a pl-sql table in which you can store the ID's of the rows that you update

create a before-statement trigger, in which yoy clear te packaged pl-sql table, using a procedure in the package

in the after update row-level trigger add the id of the row to the pl-sql table, using a procedure in the package

create an after-statement trigger, which porcesses all the rows of which the id's are stored in the pl-sql table

Good Luck!

Jack

On Sat, 27 May 2000 09:17:04 -0500, Ira Rosen <IROSEN1_at_email.mot.com> wrote:

>
>--------------2F84FC3666A6A503405DB873
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>While testing update triggers within my DB, the server (Release 7.3.3 on
>Solaris 2.6) gave me error ORA-04091. The full description of this error
>is below:
>
>
> ORA-04091 table name is mutating, trigger/function may not see
> it
>
> Cause:
> A trigger or a user-defined PL/SQL function that is referenced
> in the statement attempted to query or modify a table that was
> in the middle of being modified by the statement that fired
> the trigger.
>
> Action:
> Rewrite the trigger or function so it does not read the table.
>
>Here is the trigger I was trying to execute:
>
>CREATE OR REPLACE TRIGGER U_TestResults_TCID
>AFTER UPDATE ON TestCases
>FOR EACH ROW
>BEGIN
> UPDATE TestResults
> SET TestCaseID = :new.TestCaseID
> WHERE TestCaseID = :old.TestCaseID;
>END;
>
>
>I have a similar update trigger between one other source table and the
>same destination table. That trigger previously worked fine, but is now
>responding with the same server error. There have been no changes in the
>server release that I can detect since I implemented the first rigger.
>The error messages for my triggers also include ORA-6512 which points to
>an error in line two of the trigger. In addition, I have constraints
>issuing delete statements between the same tables referenced in my
>triggers. The idea is that when a user updates or deletes a record in
>the source table, the same operation is performed on records in the
>destination table with the same record value as the source table. I find
>the ORA-04091 error rather confusing because the message is reporting an
>error condition within a trigger that describes the situation where it
>is appropriate for a trigger to fire: when a table is being modified.
>Unless I have some misunderstanding regarding the CREATE TRIGGER
>command, isn't this timing the reason for the 'AFTER UPDATE' clause I
>provided? I have temporarily dropped the offending triggers since these
>errors do not allow any actions to be taken on the source table. In case
>the 'table in the middle of being modified' is a reference to
>uncommitted transactions before firing the trigger, the same error
>occurs if I manually issue a COMMIT statement just before executing the
>UPDATE statement that fires the triggers. Any suggestions?
>
>TIA for any help that you can offer.
>
>-Ira Rosen
>
>
>
>--------------2F84FC3666A6A503405DB873
>Content-Type: text/html; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
><html>
>While testing update triggers within my DB, the server (Release 7.3.3 on
>Solaris 2.6) gave me error ORA-04091. The full description of this error
>is below:
><blockquote>&nbsp;
><br>ORA-04091 table name is mutating, trigger/function may not see it
><p>&nbsp;Cause:
><br>A trigger or a user-defined PL/SQL function that is referenced in the
>statement attempted to query or modify a table that was in the middle of
>being modified by the statement that fired the trigger.
><p>Action:
><br>Rewrite the trigger or function so it does not read the table.</blockquote>
>
><p><br>Here is the trigger I was trying to execute:
><p>CREATE OR REPLACE TRIGGER U_TestResults_TCID
><br>AFTER UPDATE ON TestCases
><br>FOR EACH ROW
><br>BEGIN
><br>&nbsp;&nbsp;&nbsp; UPDATE TestResults
><br>&nbsp;&nbsp;&nbsp; SET&nbsp;&nbsp;&nbsp; TestCaseID = :new.TestCaseID
><br>&nbsp;&nbsp;&nbsp; WHERE&nbsp; TestCaseID = :old.TestCaseID;
><br>END;
><br>&nbsp;
><p>I have a similar update trigger between one other source table and the
>same destination table. That trigger previously worked fine, but is now
>responding with the same server error. There have been no changes in the
>server release that I can detect since I implemented the first rigger.
>The error messages for my triggers also include ORA-6512 which points to
>an error in line two of the trigger. In addition, I have constraints issuing
>delete statements between the same tables referenced in my triggers. The
>idea is that when a user updates or deletes a record in the source table,
>the same operation is performed on records in the destination table with
>the same record value as the source table. I find the ORA-04091 error rather
>confusing because the message is reporting an error condition within a
>trigger that describes the situation where it is appropriate for a trigger
>to fire: when a table is being modified. Unless I&nbsp;have some misunderstanding
>regarding the CREATE&nbsp;TRIGGER command, isn't this timing the reason
>for the 'AFTER&nbsp;UPDATE' clause I provided? I&nbsp;have temporarily
>dropped the offending triggers since these errors do not allow any actions
>to be taken on the source table. In case the 'table in the middle of being
>modified' is a reference to uncommitted transactions before firing the
>trigger, the same error occurs if I manually issue a COMMIT statement just
>before executing the UPDATE statement that fires the triggers. Any suggestions?
><p>TIA for any help that you can offer.
><p>-Ira Rosen
><br>&nbsp;
><br>&nbsp;</html>
>
>--------------2F84FC3666A6A503405DB873--
>
Received on Mon May 29 2000 - 00:00:00 CEST

Original text of this message