Re: table CLASS is mutating, trigger/function may not see it

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 22 Jun 1998 00:57:45 GMT
Message-ID: <3594ab2b.17922160@192.86.155.100>

A copy of this was sent to tomas_at_cmic.ca (Tomas CIRIP) (if that email address didn't require changing) On Mon, 22 Jun 1998 00:16:32 GMT, you wrote:

>Hi All,
>
>imagine I have table CLASS( A varchar2(10)
> B varchar2(10))
>When I delete record from table class, value of column B should be
>changed for some other records from the same table. This should be
>done in db trigger( before or after delete). But when I am trying to
>do this using UPDATE command on the same table, there is Oracle error
>"ORA-04091: table CLASS is mutating, trigger/function may not see it".
>Thanks for help.
>
>---------------------------------------------------------------------------------------------------------
>Tomas CIRIP
>tomas_at_cmic.ca
>---
>Tomas Cirip
>cviro_at_hotmail.com

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). 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 Sun Jun 21 1998 - 19:57:45 CDT