Re: Mutating Table Problem. Please help...

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;
>> > /

1 The before statement trigger is missing. Hence the pl/sql array is not initialized and is not reinitialized during a second run. Obviously, strange things may happen 2 There are commits in the after update statement trigger. commits are forbidden in a trigger and they result in errors 3 There is a needless test_temp table, don't know whether there are additional triggers on this one to screw up the process. Generally tracking info during a debug process is best processed by autonomous transactions or by calling dbms_output. You are testing the trigger interactively, so the output will go to your screen, provided of course serveroutput is enabled.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon May 20 2002 - 14:13:36 CDT