Re: Database Trigger on Inserting Record!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/09
Message-ID: <348d7aaa.10771778_at_inet16>#1/1

On 9 Dec 1997 12:45:16 GMT, yywong_at_math.hkbu.edu.hk (YyWong) wrote:

>I am new in writing database trigger. Recently, I want to write a
>trigger to insert a new record into another table, says B, after a
>new record is inserted into a table, says A.
>
>The trigger's content is:
>
>CREATE OR REPLACE TRIGGER b_insert
>AFTER INSERT
>ON A
>FOR EACH ROW
>BEGIN
> INSERT INTO B
> (b_code, b_num)
> VALUES
> (:new.grp_code, 0);
>END;
>
>However, an error always occurs:
>
>ORA-04091: table TEST.A is mutating, trigger/function may not see it
>ORA-06512: at "TEST.B_INSERT", line 4
>ORA-04088: error during execution of trigger 'TEST.B_INSERT'
>
>Am I do something wrong on :new?
>Or how can I handle the newly inserted record in A and insert any column
>into B?
>
>Thanks!

You must have declaritive RI from B to A. This means that while table A is in the middle of changing, table B cannot read it (its in a state of flux) so the declaritive RI cannot be enforced (and hence you cannot insert).

Here is the way to get around this. You will create a package specification that can maintain a 'state'. In the following example, the only state we need to remember is the rowid(s) of the newly inserted data. We could be keeping any state (including full before/after images of records for example).

We will create three triggers -- a BEFORE, AFTER FOR EACH ROW, and AFTER trigger. The BEFORE trigger will reset the package state to ensure consistency. The AFTER FOR EACH ROW trigger will capture the changes and store them in this package state. The AFTER trigger will read the state and apply the changes.

The following example demonstrates this technique. An insert into A will cause an insert into B even tho B has a foreign key to A.

create table A ( x int primary key,

                 y int,
                 z int );
 
create table B ( a int,
                 b date,
                 c int,
                 constraint b_pk primary key ( a, b ),
                 constraint b_fk foreign key ( a ) references A(x) );
 

create or replace package A_pkg
as

    type Array is table of rowid index by binary_integer;  

    rowids Array;
    empty Array;
    cnt number;  

end;
/  

create or replace trigger A_bi
before insert on A
begin

    A_pkg.cnt := 0;
    A_pkg.rowids := A_pkg.empty;
end;
/  

create or replace trigger A_aifer
after insert on A
for each row
begin

    A_pkg.cnt := A_pkg.cnt+1;
    A_pkg.rowids( A_pkg.cnt ) := :new.rowid; end;
/  

create or replace trigger A_ai
after insert on A
begin

    for i in 1 .. A_pkg.cnt loop

        insert into B ( a, b, c )
        select x, sysdate, z
          from A
         where rowid = A_pkg.rowids(i);

    end loop;  

    A_pkg.cnt := 0;
    A_pkg.rowids := A_pkg.empty;
end;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

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 Dec 09 1997 - 00:00:00 CET