Re: ORA-04091 - Mutating table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/06/27
Message-ID: <35973d18.4616718@192.86.155.100>#1/1

A copy of this was sent to nwilson_at_bdsinc.com (Ned Wilson) (if that email address didn't require changing) On Sat, 27 Jun 1998 13:47:14 -0400, you wrote:

>Good afternoon.
>
>I have run into the venerable mutating tables error, and I have been on
>the internet and read the documentation, and fixed the problem. When I
>changed the trigger so that it no longer referenced the table being
>updated, it caused another problem with our employee timesheets. So, I
>had to put the code back into place, mutating tables be damned.
>
>What I am trying to do is this: I simply want to do a select count(*)
>statement to count the number of records matching several constraints.
>Unfortunately, this table is in the process of being updated by yet
>another trigger, hence the mutate table error.
>
>Is there a way to tell if the offending trigger has completed firing, and
>committed its changes? If not, is there a PL/SQL equivalent of the sleep
>command? If there was a way to make the select statement wait until all
>previous modifications are complete, it would make my life a whole lot
>easier.
>
>Thanks in advance.
>
>
>Ned Wilson
>In-House Developer
>Business Data Services, Inc.

Here is the general purpose way to do this... This is an update example (eg: don't keep rowids of deleted rows, they won't exist in the AFTER DELETE trigger if you are processing a DELETE trigger). for your needs, you need to keeps pl/sql tables of the deleted values (you might for example need 2 tables -- one for A and one for B or you could use a table of records, whatever).

The concept is this--

a FOR EACH ROW Trigger will capture the values into pl/sql tables. An AFTER trigger, which can read/write to the affected table or child tables (if its declaritive RI that is the issue).

We need to implement

• a package to hold the state
• a BEFORE trigger to reset the package state to a known 'safe' state.
• an AFTER, FOR EACH ROW trigger to capture the changes into the package
• an AFTER trigger to process the captured information...

Here is an example:

create or replace package emp_pkg
as

    type ridArray is table of rowid index by binary_integer;

    rids ridArray;
    empty ridArray;
    cnt number;
end;
/

create or replace trigger emp_bu
before update on emp
begin

    emp_pkg.cnt := 0;
end;
/

create or replace trigger emp_aufer
after update on emp
for each row
begin

    emp_pkg.cnt := emp_pkg.cnt + 1;
    emp_pkg.rids( emp_pkg.cnt ) := :new.rowid; end;
/

create or replace trigger emp_au
after update on emp
declare

    avg_sal number;
begin

    for i in 1 .. emp_pkg.cnt loop

        select avg(sal) into avg_sal
          from emp
         where deptno = ( select deptno
                            from emp
                           where rowid = emp_pkg.rids(i) );

        -- do something about it.....
        dbms_output.put_line( emp_pkg.rids(i) || ' avg = ' || avg_sal );

    end loop;
end;
/

update emp set ename = upper(ename);    

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jun 27 1998 - 00:00:00 CDT