Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Mutating solved now deadlock error (Oracle 10g, XP)
Trigger Mutating solved now deadlock error [message #398708] Fri, 17 April 2009 03:53 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

i want to update a field D_UPDTDATE of table TEST if it is updated how to write a trigger ;when i wrote a trigger its giving mutating trigger error to solve i used
PRAGMA AUTONOMOUS TRANSACTION but now error is regarding deadlock



CREATE TABLE  test
(
  N_JOBID         NUMBER(10),
  N_APRVBYID      NUMBER(5),
  D_APPRVDATE     DATE,
  D_ENTRYDAT      DATE       DEFAULT sysdate,
  D_UPDTDATE      DATE
)


CREATE OR REPLACE TRIGGER trg_test
after update ON test for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update test set D_UPDTDATE=sysdate ;
commit;
end trigger;



How to solve this .Please Help me Thanks in Advance
Re: Trigger Mutating solved now deadlock error [message #398711 is a reply to message #398708] Fri, 17 April 2009 03:57 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Looking at your trigger you do not need autonomous transaction. It should have been only like this


CREATE OR REPLACE TRIGGER trg_test
before update ON test for each row
begin
 :new.D_UPDTDATE := sysdate ;
end trigger;





[Updated on: Fri, 17 April 2009 04:00]

Report message to a moderator

Re: Trigger Mutating solved now deadlock error [message #398714 is a reply to message #398708] Fri, 17 April 2009 04:02 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
PRAGMA AUTONOMOUS TRANSACTION is never a solution to mutating table. It just swaps one problem for another - as you've just found out.

Suggest you read this: http://asktom.oracle.com/tkyte/Mutate/index.html

I suspect you want a before row trigger that just sets the new value of D_UPDTDATE to sysdate. You don't need an update statement at all.
Re: Trigger Mutating solved now deadlock error [message #398736 is a reply to message #398714] Fri, 17 April 2009 05:56 Go to previous message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Thanks cookiemonster it works FINE .THANKS
Previous Topic: Passing array having the columns name as its elements
Next Topic: Passing array of records as the input parameter to store procedure
Goto Forum:
  


Current Time: Fri Dec 09 23:27:33 CST 2016

Total time taken to generate the page: 0.07642 seconds