Mutating error [message #664566] |
Sat, 22 July 2017 13:35 |
|
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 #664569 is a reply to message #664566] |
Sat, 22 July 2017 18:04 |
|
Barbara Boehmer
Messages: 9097 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 #664574 is a reply to message #664570] |
Sun, 23 July 2017 05:06 |
John Watson
Messages: 8938 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.
|
|
|
|
|
|