Re: Mutating Tables...how to handle..?

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/04/29
Message-ID: <4m2ucc$b06_at_inet-nntp-gw-1.us.oracle.com>#1/1

qmick_at_nyc.pipeline.com (Michael Joseph) wrote:

>
>Does anyone know the best way to deal with this...?
>
>I have a row level trigger that fires AFTER I insert a new record in table
>A to insert a set of child rows in the child table B which of course has a
>Foreign Key constraint defined to A.
>
>The trigger fires and returns the message 'Table A is Mutating any changes
>made may be lost" and nothing gets inserted into table B.
>
Here is how to get around the mutating table error, the example here assumes that when you insert/update a parent record, you will create three child records to go with it. Basically, you would capture into pl/sql tables enough information in an "AFTER INSERT OR UPDATE FOR EACH ROW" trigger and then use that information in an "AFTER INSERT OR UPDATE" trigger. The mutating table occurrs since the "AFTER INSERT OR UPDATE FOR EACH ROW" trigger happens as the rows are being put into the table, if you were doing an "update emp set sal = sal*1.10" for example and you COULD read the emp table, you would see many versions of the emp table that never existed at any point in time (eg: you would see the emp table after the first row was updated, the second, etc. Each view of the table, except for the last, would be incorrect. The update statement is a 'scalar' event, not a bunch of single updates. To see the table after each and every update would be seeing the wrong information).

create table parent

(	id		number primary key,
	data	varchar2(2000)

)
/

create table child

(	id 		number,
	seq		number,
	data	varchar2(2000),
	constraint child_pk primary key(id,seq),
	constraint child_fk foreign key(id) references parent(id)

)
/

create or replace package child_tmp
as

        type idArray is table of parent.id%type index by binary_integer;

	cnt		number;
	empty	idArray;
	id		idArray;

end child_tmp;
/

create or replace trigger parent_biu
before insert or update on parent
begin

	child_tmp.cnt := 0;
	child_tmp.id  := child_tmp.empty;

end;
/

create or replace trigger parent_aiufer
after insert or update on parent
for each row
begin

	child_tmp.cnt := child_tmp.cnt+1;
	child_tmp.id( child_tmp.cnt ) := :new.id;

end;
/

create or replace trigger parent_aiu
after insert or update on parent
begin

	for i in 1 .. child_tmp.cnt loop
		for j in 1 .. 3 loop
			insert into child 
			( id, seq, data ) 
			values 
			( child_tmp.id(i), j, 'Some Other Data' );
		end loop;
	end loop;

end;
/

>What a pain....
>
>Thanks for the help...
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Mon Apr 29 1996 - 00:00:00 CEST