Home » SQL & PL/SQL » SQL & PL/SQL » How to implement Trigger (Oracle 10g, Windows XP)
How to implement Trigger [message #444652] Tue, 23 February 2010 04:14 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I have a table unit_mast and pur_unit_mast .When i update S_unitname field in unit_mast ; i want to update the field S_punitname in table pur_unit_mast and if S_punitname field of table pur_unit_mast is updated i want to update the S_unitname field in unit_mast ;for these operations i wrote triggers , But here the deadlock error occurs How can i solve this . Codes are attached Below .Please have a look ,Thanks in advance


CREATE TABLE UNIT_MAST
(
  N_UNITID     NUMBER(3),
  S_UNITNAME   VARCHAR2(15) ,
  S_UNITSHORT  VARCHAR2(5) 
);

CREATE TABLE PUR_UNIT_MAST
(
  N_PUNITID     NUMBER(3),
  S_PUNITNAME   VARCHAR2(15) ,
  S_PUNITSHORT  VARCHAR2(5) 
);

CREATE OR REPLACE TRIGGER TR_INS
AFTER  UPDATE ON UNIT_MAST FOR EACH ROW
  
BEGIN 
 
 UPDATE PUR_UNIT_MAST SET  S_punitname =:NEW.N_UNITNAME  
where N_PUNITID =NVL(:NEW.N_UNITID,:OLD.N_UNITID);

END ;
/


CREATE OR REPLACE TRIGGER TR_INS_PUR
AFTER  UPDATE ON PUR_UNIT_MAST FOR EACH ROW
  
BEGIN 
 
 UPDATE  UNIT_MAST SET  unitname =:NEW.N_PUNITNAME  
where N_UNITID =NVL(:NEW.N_PUNITID,:OLD.N_PUNITID);

END ;
/




[Updated on: Tue, 23 February 2010 05:16] by Moderator

Report message to a moderator

Re: How to implement Trigger [message #444653 is a reply to message #444652] Tue, 23 February 2010 04:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One option is to have your triggers check to see if the value in the other table is different to the local value, and only perform the update if the values are different.

Re: How to implement Trigger [message #444656 is a reply to message #444652] Tue, 23 February 2010 05:18 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a proper design.
Use deferred constraints and a procedure to update, do not use triggers for that.

Regards
Michel
Previous Topic: total count
Next Topic: sequence into individual records
Goto Forum:
  


Current Time: Mon Sep 26 02:32:43 CDT 2016

Total time taken to generate the page: 0.08512 seconds