Home » SQL & PL/SQL » SQL & PL/SQL » ora-04091 table mutating error with trigger (oracle 10g)
ora-04091 table mutating error with trigger [message #382193] Wed, 21 January 2009 05:38 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

i have created a trigger and executed it and got the error like this

CREATE OR REPLACE
TRIGGER "TR_Update_AppointmentDetails" AFTER UPDATE OF APPOINTMENT_STATUS_ID,APPOINTMENT_DATE ON EMRAPPOINTMENTDETAILS
FOR EACH ROW
BEGIN

if(:new.APPOINTMENT_STATUS_ID != 11 ) THEN

INSERT INTO EMRAppointmentDetailsHistory (
APPOINTMENT_ID, PATIENT_ID, REFERED_BY, SPECIALTY_ID, REASON, CONSULTANT_ID,
APPOINTMENT_TYPE_ID, APPOINTMENT_DATE, APPOINTMENT_TIME, FRONTDESK_COMMENTS,
APPOINTMENT_STATUS_ID, CREATED_DATE, USER_LOGIN, VISIT_TYPE_ID, APPOINTMENT_LOCATION_ID,
HEALTH_PKG_ID,DISP_APPOINTMENT_ID,SRV_ITEM_ID,FULFILLED_APPOINTMENT_ID,FULFILLED_LOCATION_ID,
FULFILLED_LOCATION,IS_LAB_APMT,TP_PKG_ID,TP_ITEM_ID,LAB_TEST_ID,LAB_PROFILE_ID)
VALUES (
:old.APPOINTMENT_ID, :old.PATIENT_ID, :old.REFERED_BY, :old.SPECIALTY_ID,
:old.REASON, :old.CONSULTANT_ID, :old.APPOINTMENT_TYPE_ID, :old.APPOINTMENT_DATE,
:old.APPOINTMENT_TIME, :old.FRONTDESK_COMMENTS, :old.APPOINTMENT_STATUS_ID,
:old.CREATED_DATE, :old.USER_LOGIN, :old.VISIT_TYPE_ID, :old.APPOINTMENT_LOCATION_ID,
:old.HEALTH_PKG_ID,:old.DISP_APPOINTMENT_ID,:old.SRV_ITEM_ID,:old.FULFILLED_APPOINTMENT_ID,
:old.FULFILLED_LOCATION_ID,:old.FULFILLED_LOCATION,:old.IS_LAB_APMT,:old.TP_PKG_ID,:old.TP_ITEM_ID,:old.LAB_TEST_ID,:old.LAB_PROFILE_ ID);
else
delete from EMRAppointmentDetails where APPOINTMENT_ID= :new.APPOINTMENT_ID;
END IF;
END TR_Update_AppointmentDetails;



select * from EMRAppointmentDetailsHistory where APPOINTMENT_ID=1540
select * from EMRAppointmentDetails where APPOINTMENT_ID=1540

update EMRAppointmentDetails set APPOINTMENT_STATUS_ID=11 where APPOINTMENT_ID=1540

the errors are like this


Error starting at line 1 in command:
update EMRAppointmentDetails set APPOINTMENT_STATUS_ID=11 where APPOINTMENT_ID=1540
Error report:
SQL Error: ORA-04091: table EZEMRXCLINICALDEV.EMRAPPOINTMENTDETAILS is mutating, trigger/function may not see it
ORA-06512: at "EZEMRXCLINICALDEV.TR_Update_AppointmentDetails", line 19
ORA-04088: error during execution of trigger 'EZEMRXCLINICALDEV.TR_Update_AppointmentDetails'
Re: ora-04091 table mutating error with trigger [message #382195 is a reply to message #382193] Wed, 21 January 2009 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
delete from EMRAppointmentDetails where APPOINTMENT_ID= :new.APPOINTMENT_ID;


You're trying issue a delete for the row you're updating from an update trigger. That's NEVER going to work.
Re: ora-04091 table mutating error with trigger [message #382196 is a reply to message #382193] Wed, 21 January 2009 05:49 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Rajashekhar

Pls Format the post before posting. With Respect to the problem

"You can't perform DML on the table which caused the trigger to fire".

i.e "EMRAPPOINTMENTDETAILS" is the table on which the trigger is created and in the trigger body you are performing "delete"(DML)
on the same table.

[Updated on: Wed, 21 January 2009 05:52]

Report message to a moderator

Re: ora-04091 table mutating error with trigger [message #382200 is a reply to message #382196] Wed, 21 January 2009 06:03 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can i go with like this
if updating then
insert into ......
if deleting then....
insert into ......


will it work if i modify the above code
Re: ora-04091 table mutating error with trigger [message #382201 is a reply to message #382193] Wed, 21 January 2009 06:05 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably, why don't you try it and see?
Re: ora-04091 table mutating error with trigger [message #382202 is a reply to message #382201] Wed, 21 January 2009 06:07 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
before going to do this i have to disable the trigger?
after compiling it and i have to enable the trigger?
later i have to execute this?


Re: ora-04091 table mutating error with trigger [message #382206 is a reply to message #382202] Wed, 21 January 2009 06:26 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
even after doing like this too iam getting the same error.

f updating then
insert into ......
if deleting then....
insert into ......

is there no other approach to handle it?
Re: ora-04091 table mutating error with trigger [message #382207 is a reply to message #382206] Wed, 21 January 2009 06:31 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Rajashekar,

check this link :

http://www.dba-oracle.com/oracle_tips_rittman_discoverer_db.htm

[Updated on: Wed, 21 January 2009 06:36] by Moderator

Report message to a moderator

Re: ora-04091 table mutating error with trigger [message #382211 is a reply to message #382207] Wed, 21 January 2009 06:43 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
iam getting like this
deadlock detected while waiting for resource
i have tried that with following scenario


CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t1;

INSERT INTO t2
VALUES
(i);
COMMIT;
END;
/
Re: ora-04091 table mutating error with trigger [message #382215 is a reply to message #382211] Wed, 21 January 2009 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you stupidly use a feature you don't understand you finally get what you deserve.

Regards
Michel
Re: ora-04091 table mutating error with trigger [message #382221 is a reply to message #382193] Wed, 21 January 2009 07:04 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
OK Let's back up a few steps here.
We don't know what you're trying to accomplish, we don't know your table structures or the relationships between them.
We don't know why you thought autonomous_transaction was a good idea.

We don't you explain to use in words what it is you're trying achieve and we might be able to help you.

While you're at it please read http://www.orafaq.com/forum/t/88153/0/, especially the how to format your post section and use code tags in future.
Previous Topic: ORA-24761: transaction rolled back
Next Topic: Need to get null values!
Goto Forum:
  


Current Time: Mon Dec 05 12:45:34 CST 2016

Total time taken to generate the page: 0.09873 seconds