Home » SQL & PL/SQL » SQL & PL/SQL » Mutating error (Oracle 12.1)
Mutating error [message #664566] Sat, 22 July 2017 13:35 Go to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
I want to insert the data in the same table using different values while i am inserting record.For this purpose i created after insert trigger but the problem is it is giving mutating error , is there a work around for this.





CREATE TABLE ACCNT ( ACNT_CODE VARCHAR2(12),ACNT_COMP VARCHAR2(12),ACNT_NAME VARCHAR2(10))


--for any record i insert into this table a duplicate record with ACNT_COMP AS 'C03' must be created automatically with inserted ACNT_CODE and ACNT_NAME but the problem is i am getting the mutating error.



/* Formatted on 7/22/2017 10:26:32 PM (QP5 v5.256.13226.35538) */
CREATE OR REPLACE TRIGGER AFT_INS_ACNT
   AFTER INSERT
   ON ACCNT
   FOR EACH ROW
BEGIN
   INSERT INTO ACCNT (ACNT_CODE, ACNT_COMP, ACNT_NAME)
        VALUES (:new.ACNT_CODE, 'C03', :new.ACNT_NAME);
END;


--error [Error] Execution (7: 13): ORA-04091: table HR.ACCNT is mutating, trigger/function may not see it
--ORA-06512: at "HR.AFT_INS_ACNT", line 2
--ORA-04088: error during execution of trigger 'HR.AFT_INS_ACNT'











Re: Mutating error [message #664567 is a reply to message #664566] Sat, 22 July 2017 13:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Trigger code should not do SQL against same table upon which trigger is based.
Re: Mutating error [message #664568 is a reply to message #664566] Sat, 22 July 2017 17:24 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Based on your post I understand that you want assign the "acnt_comp" Column a specific value of
'C03'. As previously indicated you cannot perform DML on the same table that the trigger is assigned,
but you can perform data modifications of the new columns values. I changed the trigger to a
BEFORE INSERT so the value is assigned to the column Before the Insert occurs.
Hopefully the following will meet your requirements:


CREATE OR REPLACE TRIGGER bef_ins_acnt
   BEFORE INSERT
   ON ACCNT
   FOR EACH ROW
BEGIN

   :new.acnt_comp := 'C03';

END;
/
Re: Mutating error [message #664569 is a reply to message #664566] Sat, 22 July 2017 18:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If each insert triggers another insert, then that insert will trigger another insert, which will trigger another insert, and so on, resulting in an endless loop of inserts. You can, however, add the additional insert into whatever procedure does your insert, instead of using a trigger. There are also complex workarounds for triggering one-time inserts. However, this sounds like the root problem might be a design flaw.

[Updated on: Sat, 22 July 2017 18:05]

Report message to a moderator

Re: Mutating error [message #664570 is a reply to message #664569] Sat, 22 July 2017 20:04 Go to previous messageGo to next message
arifmd1705
Messages: 37
Registered: May 2016
Location: uae
Member
Thanks all of you.i would like to know
If there is any other workaround for this
As i want to duplicate row upon insert with different acnt code.
Re: Mutating error [message #664571 is a reply to message #664570] Sat, 22 July 2017 20:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
arifmd1705 wrote on Sat, 22 July 2017 18:04
Thanks all of you.i would like to know
If there is any other workaround for this
As i want to duplicate row upon insert with different acnt code.
Don't use any trigger.
Do two INSERT from application itself.
Re: Mutating error [message #664574 is a reply to message #664570] Sun, 23 July 2017 05:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could cover the table with a view, and use an INSTEAD OF trigger to rewrite inserts into the view to two inserts into the table.
Re: Mutating error [message #664600 is a reply to message #664574] Mon, 24 July 2017 08:03 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
There are various ways around the mutating trigger error. What version is your database?
Re: Mutating error [message #664601 is a reply to message #664600] Mon, 24 July 2017 08:08 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
oracle version 12.1.0.2
Re: Mutating error [message #664602 is a reply to message #664601] Mon, 24 July 2017 08:12 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
A simple way is to use the 3 trigger trick as shown in this link

https://oracle-base.com/articles/9i/mutating-table-exceptions

You can put them in all in a compound trigger as talked about in this article

https://oracle-base.com/articles/11g/trigger-enhancements-11gr1

Good luck

Previous Topic: foreign key conflict issue
Next Topic: Weeks, Quarters, and Counts
Goto Forum:
  


Current Time: Fri Mar 29 02:18:39 CDT 2024