troubleshooting Question

Mutating table

Avatar of lulubell-b
lulubell-b asked on
Oracle DatabaseEnterprise Software
18 Comments1 Solution1391 ViewsLast Modified:
Trigger worked as expected, but now i have a new requirement.  Table ais has 2000 plus records, table usage identifies records in ais of interest to a program.  My initial attempt included the delete in the after statement, but I removed it as i kept encountering a mutating table error.  So my thought is to only focus on the update statement.  You see the insert works fine as my customer wishes to see all new ais, but on update or delete- only the records of type 'DEF' need to be accounted for in the stage table

The below trigger compiles, but the mutating error occurs when testing.  Help needed on a solution, thank you!
CREATE OR REPLACE TRIGGER S_U_C_ID_SEQ_TR 
after update on C
for each row 
declare
  c_chng_dsc_tx_v  	s_c.a_chng_dsc_tx%type;
  ABC_GRP_V number;
begin
select COUNT(*) into ABC_GRP_V
FROM ais a, usage b
where b. usg_nm = 'DEF'
and :old.id = b. id;
if ABC_GRP_V > 1 then
 if updating (ID')
        or updating ('NM')
	or updating ('ACRYNM_TX')
then	
		if nvl(to_char(:old. ID),'null')!=nvl(to_char(:new.ID), 'null') then
		c_chng_dsc_tx_v := c_chng_dsc_tx_v || ID, ';
if nvl (:old.NM, 'null')!=nvl(:new. NM, 'null') then
 		c_chng_dsc_tx_v := c_chng_dsc_tx_v || 'NM, ';
		end if;
		if nvl (:old. ACRYNM_TX, 'null')!=nvl(:new. ACRYNM_TX, 'null') then
 		c_chng_dsc_tx_v := c_chng_dsc_tx_v || ACRYNM_TX, ';
		end if;
 insert into s_C
	(S_C_ID,
	ID,
 	NM,
	ACRYNM_TX,
	Ab_ID,
	Ab_ TYP,
	c_chng_cd,
  	c_mod_c_dt,
	c_d_src,
	c_chng_dsc_tx,
	c_cnfrm_rcpt_flg) 
	values 
	(S_C_ID_SEQ.NEXTVAL,
	:old. id, 
	:new. NM,
	:new. ACRYNM_TX,
	:new.Ab_ID,
	:new.Ab_TYP,
	'U',
	sysdate,
	'ABC',
 	c_chng_dsc_tx_v,
	'N');
end if;
end if;
END;
/
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 18 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros