Re: Another mutating table problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 05 Jun 1998 14:39:45 GMT
Message-ID: <357802d6.4574888_at_192.86.155.100>

A copy of this was sent to Jason Boesch <jgboesc_at_REMOVE-THISuswest.com> (if that email address didn't require changing) On Thu, 04 Jun 1998 18:00:15 -0500, you wrote:

>I am trying to create a BEFORE INSERT trigger that not only modifies the
>new record, but also inserts additional records in the triggering table.
>
>I've read some other postings that suggest using a row level trigger to
>store the current values and then use a statement level trigger to
>insert the new values. I understand how they work., but I think this
>problem is different because I'm triggering INSERTs as the result of an
>INSERT (not an update).
>
>Since the statement level trigger is AFTER INSERT it starts inserting
>the newly created records over and over again, producing an endless loop
>and an error.
>
>Any ideas on how this can be done?
>
>Thanks!

You do this almost the same way. Just add another variable to the package to avoid the recursion. For example, here is a demo that takes every inserted record and re-inserts it after negating the primary key:

drop table demotbl;
create table demotbl ( x int primary key );

create or replace package demotbl_pkg
as

    type        ridArray is table of rowid index by binary_integer;
    rids        ridArray;
    cnt         number;

    inTrigger boolean default FALSE;
end;
/

create or replace trigger demotbl_bi
before insert on demotbl
begin

    if ( NOT demotbl_pkg.inTrigger ) then

        demotbl_pkg.cnt := 0;
    end if;
end;
/

create or replace trigger demotbl_aifer
after insert on demotbl
for each row
begin

    if ( NOT demotbl_pkg.inTrigger ) then

        demotbl_pkg.cnt := demotbl_pkg.cnt + 1;
        demotbl_pkg.rids( demotbl_pkg.cnt ) := :new.rowid;

    end if;
end;
/

create or replace trigger demotbl_ai
after insert on demotbl
begin

    if ( NOT demotbl_pkg.inTrigger ) then

        demotbl_pkg.inTrigger := TRUE;

        for i in 1 .. demotbl_pkg.cnt loop
            insert into demotbl
            select -x from demotbl where rowid = demotbl_pkg.rids(i);
        end loop;

        demotbl_pkg.inTrigger := FALSE;

    end if;
exception

    when others then

        demotbl_pkg.inTrigger := FALSE;
        raise;

end;
/

insert into demotbl values ( 1 );
select * from demotbl;

Hope this helps...  

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 Fri Jun 05 1998 - 16:39:45 CEST