Re: Triggers and table mutation

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 06 May 1998 15:22:33 GMT
Message-ID: <35567ea3.7729784_at_192.86.155.100>

A copy of this was sent to Frank W Phillips JR <FWP_at_vpha.ufl.edu> (if that email address didn't require changing) On Mon, 04 May 1998 08:59:41 -0400, you wrote:

>I need to write an update trigger on a table but the trigger may need to
>update and/or insert additional records into the same table. Obviously
>this is not natively supported, the infamous table mutating error. Any
>ideas on how to handle this?

You need to do your work in an AFTER trigger, not a FOR EACH ROW trigger. The following example shows you how to do this if all you need access to is the after images (new images) of the records. It does this be remembering the rowids of the updated records in the for each row trigger and then using this information in an after trigger to process the changed data. If you need access to the before images (old values) you need to add appropriate type definitions to the package spec, declare some variables in the spec to hold those values, and add the assignments to the for each row trigger.

The following example works on the scott.emp table. It will print the average salary in the department of any EMP record that is updated.

Hope this helps..

create or replace package emp_pkg
as

    type ridArray is table of rowid index by binary_integer;

    rids 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;
/  

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 Wed May 06 1998 - 17:22:33 CEST