Re: Mutating Table Problem. Please help...

Hello Swany..
Here are my commets:
The first trigger is for an update of hold that fires for each row while the second trigger is for an update of hold that fires after the statement is executed..

There is a difference between the triggers.

Thanks
Dorothy...

• Original Message ----- From: "Swany" <swany_at_easynews_nospam_.com> Newsgroups: comp.databases.oracle.server Sent: Saturday, May 18, 2002 11:05 PM Subject: Re: Mutating Table Problem. Please help...

> I think the problem with this code is you have two triggers on update of
> hold..
>
> when the first trigger fires, the second trigger fires, which fires the
> first trigger, which ....
>
> Hence you have what is called in normal circles an infinite loop, but in
> oracle a mutating trigger..
>
>
>
> It sounds like the mutation problem you had before was caused by trying
> to update rows in the triggered table other than the rows that raised
> the trigger. I don't think you can do that. I think the only rows you
> can access inside the table are those that raised the trigger (someone
> correct me if I'm wrong).
>
> Swany

dor_foster_at_hotmail.com (Dorothy Foster) wrote in message news:<a8ecbe66.0205181429.6c7ef317_at_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 Sun May 19 2002 - 01:52:59 CDT