Mutating Table Problem

From: Vikram <vikramk_at_renewal-iis.com>
Date: 1998/03/10
Message-ID: <01bd4bb9$69899860$3c98cdd0@W95-6B2.renewal-iis.com>#1/1

This code is for information only.

I had Oracle infamous MUTATING TABLE problem due to trigger conflict on two tables. I got hints after searching through many postings to the problem in the Newsgroup archives. It may be useful to someone who wants to know more about this.

The mutating table problem may arise due to conflict in trigger. In the code script shown below, when 'pz' is updated in table A, the trigger on it will try to set value of 'lz' in table B and in turn trigger on table B will try to set 'fg' in table A. The code in q.sql is a prototype of actual problem. The mutating table error occurs for "update A set pz = 13 where sq = 1". The problem is due to access of B table from row level trigger on A. The code in x.sql uses the Before Update row level trigger to set the value in a package and a statement level after Update Trigger on A will set the value of 'lz' in B. The point is - the statement level triggers are not the cause of mutating table problem and package remembers the value which is necessary as :old or :new are not in statement level trigger.

Hope this code example helps someone.

Vikram

<<put the code below in q.sql file>>

drop table A
/

drop table B
/

create table A (

sq	number(10)	not null,
pz	number (2)	not null,
fg	char (1)	not null

)
/

create table B (

sq	number(10)	not null,
lz	number(2)	not null

)
/

CREATE OR REPLACE TRIGGER AFTER_UPDATE_A AFTER UPDATE OF pz
ON A
FOR EACH ROW
BEGIN

	UPDATE    	B
	SET       	lz = :old.pz
        	WHERE   	( sq = :old.sq);

END;
/

CREATE OR REPLACE TRIGGER AFTER_IUD_B
AFTER INSERT OR DELETE OR UPDATE
ON B
FOR EACH ROW
BEGIN

	UPDATE	  A
	SET       	fg = 'Y'
        	WHERE   	( sq = :new.sq ) OR
	        		( sq = :old.sq );

END;
/

insert into A values (1, 99, 'N')
/

insert into B values (1, 13)
/

commit
/

update A set pz = 13
where sq = 1
/

<<put the code below in x.sql file>>

drop table A
/

drop table B
/

create table A (

sq	number(10)	not null,
pz	number (2)	not null,
fg	char (1)	not null

)
/

create table B (

sq	number(10)	not null,
lz	number(2)	not null

)
/

CREATE OR REPLACE PACKAGE TRIGGER_VAR_A IS

   sq   	number(10) 	:= 0;
   pz	number(2) 	:= 0;

END;
/

CREATE OR REPLACE TRIGGER BEFORE_UPDATE_A BEFORE UPDATE
ON A
FOR EACH ROW
BEGIN
   trigger_var_A.sq := :old.sq;
   trigger_var_A.pz := :old.pz;
END;
/

CREATE OR REPLACE TRIGGER AFTER_UPDATE_A AFTER UPDATE OF pz
ON A
BEGIN

	UPDATE    	B
	SET       	lz = trigger_var_A.pz
        	WHERE   	( sq = trigger_var_A.sq);

END;
/

CREATE OR REPLACE TRIGGER AFTER_IUD_B
AFTER INSERT OR DELETE OR UPDATE
ON B
FOR EACH ROW
BEGIN

	UPDATE	  A
	SET       	fg = 'Y'
        	WHERE   	( sq = :new.sq ) OR
		        	( sq = :old.sq );

END;
/

insert into A values (1, 99, 'N')
/

insert into B values (1, 13)
/

commit
/

update A set pz = 13
where sq = 1
/
Received on Tue Mar 10 1998 - 00:00:00 CST