Re: Is there a workaround for the 'mutating table' problem in a trigger ?

coolcat_at_citynet.net wrote:

>Hi
 

>In a trigger on a table, I want to do a
>SELECT MAX() ...
>from that table and update a denormalized column in another table.
 

>However, the table obviously mutates.

Hopefully, the following example is similar enough to your case. In short, what you do is capture enough information in a for each row trigger (eg: the primary key or part of the primary key) in order to do the processing in an AFTER trigger. For example, t1 has a multi-part primary key (a,b). We want to keep the maximum value of B for a given value of A in another table t2. Here is what you might code:

create table t1

(	a	number,
	b	number,
	c	number,
	constraint t1_pk primary key(a,b)

create table t2

( 	a	number,
	max_b	number,
	constraint t2_pk primary key(a,max_b)

create or replace package t1_pkg
as

	type Array is table of t1.a%type index by binary_integer;
	arr	Array;
	empty	Array;
	cnt	number;

create or replace trigger t1_bui
before update or insert on t1
begin

	t1_pkg.cnt := 0;
	t1_pkg.arr := t1_pkg.empty;

create or replace trigger t1_auifer
after update or insert on t1 for each row begin

	t1_pkg.cnt := t1_pkg.cnt + 1;
	t1_pkg.arr( t1_pkg.cnt ) := :new.a;

create or replace trigger t1_aui
after update or insert on t1
begin

	for i in 1 .. t1_pkg.cnt loop
		update t2 
		   set max_b = ( select max(b) from t1 where a = t1_pkg.arr(i) )
		 where a = t1_pkg.arr(i);
		if ( sql%rowcount = 0 ) then
			insert into t2 
			select a, max(b) 
			  from t1 
			 where a = t1_pkg.arr(i)
			 group by a;
		end if;
	end loop;

     auifer = after update insert for each row
     aui = after update insert.

>I understand SYBASE has a workaround using which you can do a SELECT
>on the table on which the trigger is defined.

They only have one trigger firing point, the equivalent of our AFTER INSERT or UPDATE trigger. From there you can select on the table, just as you can in Oracle.

>Does ORACLE ?

Yes.

>TIA.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government