Re: Mutating Table Problem. Please help...

Will someone please help me on this???

Thanks
D.....

dor_foster_at_hotmail.com (Dorothy Foster) wrote in message news:<a8ecbe66.0205182252.520aaa32_at_posting.google.com>...
> 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 Mon May 20 2002 - 12:08:19 CDT