Re: Wanted : Help on M u t a t i n g trigger

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 13 Oct 1998 14:18:45 GMT
Message-ID: <36265dec.92675129@192.86.155.100>

A copy of this was sent to "David Vanmarcke" <david.vanmarcke_at_argenta.be> (if that email address didn't require changing) On Tue, 13 Oct 1998 12:42:45 +0200, you wrote:

>Hi,
>
>I'm having troubles with a mutating trigger here ...
>
>Here's an example of what I'm trying to do ...
>
>CREATE TRIGGER my_table_b_d_r
>BEFORE DELETE ON my_table
>FOR EACH ROW
>--
>DECLARE
> v_my_parent VARCHAR2(20);
>--
>BEGIN
>--
> SELECT name_child INTO v_my_parent
> FROM my_table
> WHERE child_nr = :OLD.child_parent;
>--
> INSERT INTO my_table_his
> (sequence, name_child, name_parent, date)
> VALUES
> (:OLD.sequence, :OLD.name_child, v_my_parent, sysdate);

the way to do this is to defer reading the table MY_TABLE (which is being changed at this point) until the AFTER DELETE trigger. The code following this demonstrates this with the EMP table found in the scott schema.

This example also convienently points out WHY there exists a mutating table problem -- why it is BAD to read a table while its mutating. If you look at the after trigger below (which tries to read the EMP table) we see that the data we are trying to read MAY IN FACT NO LONGER EXIST (our delete statement might have deleted the rows we wanted to read as well as the row that fired the trigger). In my example, we are using EMPNOS as a primary key and MGR as a foriegn key. We are trying to save the ENAME of the deleted row as well as the ENAME of the mgr pointed to by this row. What happens tho if we delete both the EMPLOYEE and their MGR in the same delete AND we let you read the table in the for each row trigger? If the table if mutating, depending on the rather arbitrary way we will delete records you may either get the employees manager record -- or not (we might delete the mgr before the emp or vice versa). In fact, if you put 2 records into the table (emp and mgr) and repeated the test over and over again the results would be indeterminate -- one time you might see the mgr record, the next time not.. it would not be deterministic -- leading to data inconsistencies and unreproducible sequences of events. This (below) makes it deterministic....

create table emp as select * from scott.emp;

alter table emp add constraint emp_pk primary key(empno);

alter table emp add constraint emp_fk foreign key(mgr) references emp(empno);

create table delete_history( ename varchar2(30), mgr_name varchar2(30), ts date);

create or replace package emp_pkg
as

    type empnoArray is table of emp.empno%type index by binary_integer;     type enameArray is table of emp.ename%type index by binary_integer;

    empnos empnoArray;
    empty empnoArray;

    enames enameArray;
end;
/

create or replace trigger emp_bd
before delete on emp
begin

    emp_pkg.empnos := emp_pkg.empty;
end;
/

create or replace trigger emp_adfer
after delete on emp for each row
begin

    emp_pkg.empnos( emp_pkg.empnos.count+1 ) := :old.mgr;     emp_pkg.enames( emp_pkg.empnos.count ) := :old.ename; end;
/

create or replace trigger emp_ad
after delete on emp
begin

    for i in 1 .. emp_pkg.empnos.count loop

        insert into delete_history
        select emp_pkg.enames(i), ENAME, sysdate
          from emp
         where empno = emp_pkg.empnos(i);

        if ( sql%rowcount = 0 ) then
            -- you deleted >1 record and got this guys MGR as well
            insert into delete_history values
            ( emp_pkg.enames(i), NULL, sysdate );
        end if;

    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 Tue Oct 13 1998 - 09:18:45 CDT