Re: Mutating Table Problem. Please help...

From: Dorothy Foster <dor_foster_at_hotmail.com>
Date: 18 May 2002 15:29:03 -0700
Message-ID: <a8ecbe66.0205181429.6c7ef317@posting.google.com>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:uedi3ui8ov87e7_at_corp.supernews.com...

> The general strategy is
> before insert or update *statement* trigger
> setting up the pl/sql array
> after insert or update *for each row* trigger
> capturing the primary keys affected
> after insert or update *statement* trigger
> processing the array.
>
> Without your actual code, I don't think you will get more specific advice.
>
> Hth
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address

Thanks Sybrand ,, I followed the strategy you described above..somehow i could not find the solution to this....maybe I am wrong....Here is the code that has the above strategy but still does not work to me.. I debugged the code wherever possible and it seems nothing wrong in the code.. but it is not updating the table in the desired way..Appreciate if you can point me where I am wrong..

---

CREATE OR REPLACE package xx_hold as

type t_hold is table of credit_test.hold%TYPE index by binary_integer;
type t_customer_id is table of credit_test.customer_id%TYPE index by
binary_integer;
type t_site_id is table of credit_test.site_id%TYPE index by
binary_integer;

v_hold t_hold;
v_customer_id t_customer_id;

v_site_id t_site_id;
v_NumEntries binary_integer := 0;

end xx_hold;
/

---

TRIGGER xx_customer_hold_t1
after update of hold on credit_test FOR EACH ROW begin
xx_hold.v_NumEntries := xx_hold.v_NumEntries + 1; --if :new.site_id is null then

insert into test_temp values (1, :new.hold || ' ' ||'from trigger1'); insert into test_temp values (:new.customer_id, 'Customer Id from trigger 1');
insert into test_temp values (:new.site_id,'Site id from trigger1 ' );
--commit;

xx_hold.v_hold(xx_hold.v_NumEntries) := :new.hold;
xx_hold.v_customer_id(xx_hold.v_NumEntries) := :new.customer_id;
xx_hold.v_site_id(xx_hold.v_NumEntries) := :new.site_id;

--end if;
end xx_customer_hold_t1;
/

---

TRIGGER xx_customer_hold_t2
after update of hold on credit_test
declare

v_hold credit_test.hold%TYPE  ;
v_cust_id credit_test.customer_id%TYPE  ;
v_site_id credit_test.site_id%TYPE  ;
v_error_mesg    VARCHAR2(1000) := NULL;
v_error_num     NUMBER         := 0;
v_error_stage   VARCHAR2(30) := NULL;

mutating_table EXCEPTION;
  PRAGMA EXCEPTION_INIT (mutating_table, -4091);

begin

for v_loopIndex in 1 .. xx_hold.v_NumEntries loop

v_hold := xx_hold.v_hold(v_LoopIndex);
v_cust_id := xx_hold.v_customer_id(v_LoopIndex);
v_site_id := xx_hold.v_site_id(v_LoopIndex);

--if v_site_id is null then-- or NVL(:NEW.site_id, 'X') = 'X' insert into test_temp values (xx_hold.v_NumEntries , 'This is the num entry');

insert into test_temp values (1, v_hold);
insert into test_temp values (v_cust_id, 'The customer id');
insert into test_temp values (3, v_site_id);

commit;
update credit_test set

		hold = v_hold
	    	where customer_id = v_cust_id
		and site_id is not null;

commit;

--end if;
end loop;

EXCEPTION

  	   WHEN mutating_table then
             insert into test_temp values (1, 'Table is MUTATING');
 WHEN OTHERS THEN
       V_Error_Mesg := SQLERRM;
       

END xx_customer_hold_t2;
/ Received on Sat May 18 2002 - 17:29:03 CDT