Re: Help on TRIGGER!!!!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/10
Message-ID: <343f66de.15018285_at_newshost>#1/1

On Fri, 10 Oct 1997 15:19:16 +0100, Paulo Pires <cofarbel_at_mail.telepac.pt> wrote:

>Hi,
>
>I need help to solve the following problem:
>
>I have 2 tables (A and B), and the table B has a FOREIGN KEY to Table A
>(Master/Detail).
>I want a record to be inserted (automatically) in table B every time I
>insert a record in table A.
>I Thought to use a trigger AFTER INSERT on table A, to insert the value
>on table B.
>The problem is that because of the FOREIGN KEY oracle gives me the:
> ORA-04091: table A is mutating trigger/function may
>not see it.
>I don't want to remove the FOREIGN KEY.
>
>What should I do to solve this problem ?
>
>Regards.
>
>
>

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 Received on Fri Oct 10 1997 - 00:00:00 CEST