Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid mutating error ? (10.2.0.1/ubuntu)
How to avoid mutating error ? [message #381047] Thu, 15 January 2009 00:05 Go to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi All,
I am getting the mutating trigger error.

I have triggers like…
1.After Insert on TABLE1 for each row –It should insert data into TABLE2.
2.Before Insert on TABLE2 for each row –in this trigger TABLE1 is used for some validating purpose.

In this case, is there any way to avoid mutating trigger problem?

Thanks in advance.

Regards,
Nanda
Re: How to avoid mutating error ? [message #381049 is a reply to message #381047] Thu, 15 January 2009 00:16 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
> is there any way to avoid mutating trigger problem?
Yes do NOT have table in FROM clause within a trigger against the same table the trigger exists.
Re: How to avoid mutating error ? [message #381051 is a reply to message #381047] Thu, 15 January 2009 00:19 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Mutating trigger is trigger that is currently being modified by DML opertion. For eg.,
You created a trigger trigger1 on table1, it should fire after update for each row. And you wrote some update statement on the same table (table1) inside the trigger . When you execute the individual update stmt on table1, the trigger get fires and the trigger also is currently being updated the same rows in table1, which is called mutating error and mutating trigger.
Re: How to avoid mutating error ? [message #381054 is a reply to message #381047] Thu, 15 January 2009 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In this case, is there any way to avoid mutating trigger problem?

Think what this error mean and fix your design or code.

Regards
Michel
Re: How to avoid mutating error ? [message #381057 is a reply to message #381054] Thu, 15 January 2009 00:35 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi,

Thanks for your quick reply.

The before insert trigger on table2 is the standard trigger of my application which I should not modify it.

Regards,
Nanda

Re: How to avoid mutating error ? [message #381058 is a reply to message #381057] Thu, 15 January 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can't modify the code then your application will never work.

Regards
Michel
Re: How to avoid mutating error ? [message #381153 is a reply to message #381058] Thu, 15 January 2009 07:58 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi Michel Cadot,

I agree with you.

But my requirement is to create the accounting combinations for each project automatically whenever I insert a new Project. So, I am writing the first trigger on the Project table. The second trigger is on the Accounting_Combinations table which is the standard one. The validations that are there in the second trigger should not be modified.

I tried temporary table option, but it did not work for me. I might have done something wrong. The process that I followed is
1. Trigger 1--> Inserts data into the temp table. (After insert on PROJECT for each row)
2. Trigger 2 --> Insert data from temp to accounting_combinations table. (After insert on TEMPORARY for each row)
3. Trigger 2 --> Standard one (Before insert on accounting_combinations for each row)

Is the process that I followed is correct?

Any suggestions?

Regards,
Nanda

[Updated on: Thu, 15 January 2009 07:58]

Report message to a moderator

Re: How to avoid mutating error ? [message #381161 is a reply to message #381153] Thu, 15 January 2009 08:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Apart from the fact that I don't understand why you'd need an intermediate temp table, did you search for the "normal" way to circumvent a mutating table problem? (And I don't mean using an autonomous transaction).

I think you could "fix" this by doing the insert into the accountings table in an after insert statement level trigger.
Re: How to avoid mutating error ? [message #381163 is a reply to message #381161] Thu, 15 January 2009 08:42 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi Frank,

I used temp table concept because I was getting mutating trigger error (still I am getting it).

As my trigger is After Insert and the another trigger is Before Insert trigger. First trigger's table is used in the second trigger.

1. Trigger 1--> Inserts data into the temp table. (After insert on PROJECT for each row)
2. Trigger 2 --> Standard one (Before insert on accounting_combinations for each row)

Thanks for your concern.

Regards,
Nanda
Re: How to avoid mutating error ? [message #381165 is a reply to message #381163] Thu, 15 January 2009 08:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You really won't fool Oracle by using an intermediate table.
Try my suggestion and remove the temp table.
Re: How to avoid mutating error ? [message #381167 is a reply to message #381153] Thu, 15 January 2009 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about explaining us what functional needs you want to achieve with your triggers?

Regards
Michel
Re: How to avoid mutating error ? [message #381170 is a reply to message #381167] Thu, 15 January 2009 09:08 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi,

My functional need is to crate the "accounting combinations" for the each project that is created. I mean when I insert a record into project table, for this new project new accounting combinations should be inserted into accounting table.

I could do it with the procedure (keeping a button on the project form..user just have to click after creating the project, but he wants it to be done automatically(background).

For this requirement I am trying to use trigger.

Regards,
Nanda
Re: How to avoid mutating error ? [message #381175 is a reply to message #381170] Thu, 15 January 2009 09:29 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi,

What I am doing:
1. Trigger 1 (after insert) on table1, which inserts data into table2.
2. Trigger 2(after insert) on table2 which inserts data into table3.
3.Trigger3 (before insert) on table3, which does the validations before insert into table3.

As per my understanding, Trigger1 should fire after the insert data into table1 and the Trigger2 should fire after the insert data into table2. At this time Tirgger1 is has nothing to do with Tigger3. But still I am getting mutating error.

Please correct me if I am wrong.
Re: How to avoid mutating error ? [message #381180 is a reply to message #381175] Thu, 15 January 2009 09:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you'd actually show us the code, and the error message that you get, we might be able to do more than guess at what the problem is.
Re: How to avoid mutating error ? [message #381184 is a reply to message #381180] Thu, 15 January 2009 10:09 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Trigger1:
------------------------------------
create or replace TRIGGER CUS_PROJECT_TRG
AFTER INSERT ON C_PROJECT FOR EACH ROW
DECLARE

BEGIN

IF (INSERTING) THEN

insert into aaa values(:new.C_PROJECT_ID);

END IF;
END CUS_PROJECT_TRG;

---------------------------------------
Trigger2:
----------------------------------------
create or replace TRIGGER AAA_TRG
AFTER INSERT ON AAA FOR EACH ROW
DECLARE

TYPE RECORD IS REF CURSOR;
CurOrg RECORD;
CurElement RECORD;
v_AcctSchema_ID NUMBER:=NULL;
v_ValidCombination_ID NUMBER;

BEGIN

IF (INSERTING) THEN

FOR CurOrg IN (SELECT * FROM AD_ORG WHERE AD_ORG_ID <> 0) LOOP
FOR CurElement IN(SELECT * FROM C_ELEMENTVALUE
WHERE ACCOUNTTYPE = 'E'
AND ISSUMMARY = 'N'
ORDER BY VALUE) LOOP

Ad_Sequence_Next('C_ValidCombination', v_AD_Client_ID, v_ValidCombination_ID);

INSERT INTO C_VALIDCOMBINATION(C_ValidCombination_ID, AD_Org_ID , Account_ID,C_Project_ID )
VALUES(v_ValidCombination_ID, CurOrg.AD_ORG_ID, CurElement.C_ELEMENTVALUE_ID,:new.aaa_id );

END LOOP;
END LOOP;

END IF;
END AAA_trg;
----------------------------------------------------------
Trigger3:

C_VALIDCOMBINATION_TRG does validations on C_PROJECT table.
------------------------------------------
Error:
------

ORA-04091: table ORT_DEV2.C_PROJECT is mutating, trigger/function may not see it ORA-06512: at "ORT_DEV2.C_VALIDCOMBINATION_TRG", line 63 ORA-06512: at "ORT_DEV2.C_VALIDCOMBINATION_TRG", line 157 ORA-04088: error during execution of trigger 'ORT_DEV2.C_VALIDCOMBINATION_TRG' ORA-06512: at "ORT_DEV2.AAA_TRG", line 57 ORA-04088: error during execution of trigger 'ORT_DEV2.AAA_TRG' ORA-06512: at "ORT_DEV2.CUS_PROJECT_TRG", line 61 ORA-04088: error during execution of trigger 'ORT_DEV2.CUS_PROJECT_TRG'

Regards,
Nanda

[Updated on: Thu, 15 January 2009 10:10]

Report message to a moderator

Re: How to avoid mutating error ? [message #381204 is a reply to message #381184] Thu, 15 January 2009 14:33 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi,

Thanks to all.

I have solved the issue without changing the standard trigger. Here are the solutions...

Solution 1: Statement level trigger on C_Project

Solution 2: I followed the following link...
http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php


Regards.

[Updated on: Thu, 15 January 2009 14:34]

Report message to a moderator

Re: How to avoid mutating error ? [message #381263 is a reply to message #381204] Fri, 16 January 2009 00:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I hope you don't see them as separate solutions. The statement level trigger on the project table is part of soution 2.
Previous Topic: Parsing in oracle
Next Topic: object dependencies (merged)
Goto Forum:
  


Current Time: Wed Dec 07 08:37:31 CST 2016

Total time taken to generate the page: 0.05275 seconds