Re: Mutating Table Problem. Please help...

Dorothy,

What do you *observe*? Do you get an error? Does the process go on forever? What?

I think Swany is right about the infinite loop your creating: in your trigger t2 you update column hold, which fires trigger t1and t2 again for every row you update, etcetera, ad infinitum.

You have to think of something so that in trigger t1 you can distinguish between when t1 is fired due to - an 'original' update of hold, and
- an update from t2 of hold.

The only thing I can think of is a trick: in t2 don't update hold to Y or N, but for instance to 1 or 0.

Now:

Change t1 to a BEFORE UPDATE trigger and in it check the value of :new.hold. If it's Y or N: do what you do now, because this is an 'original' update. t2 will fire an update the other rows.

If :new.hold is 1 or 0, change it to Y or N and don't fill the PL/SQL table. When t2 fires the table will be empty and there will be no more updates.

HTH, Jaap.

On 20 May 2002 10:08:19 -0700, dor_foster_at_hotmail.com (Dorothy Foster) wrote:

>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 - 14:58:07 CDT