Home » SQL & PL/SQL » SQL & PL/SQL » condition required in trigger (oracle 10g)
condition required in trigger [message #421148] Fri, 04 September 2009 10:07 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
CREATE OR REPLACE TRIGGER TR_Delete_EMRTrmtPlanAlert
AFTER DELETE ON EMRTrmtPlanAlertDetails
FOR EACH ROW
BEGIN
     IF INSERTING THEN
     :OLD.STATUS=1 THEN 
      INSERT INTO EMRTrmtPlanAlertDetailsHistory(TRTMT_PLAN_ALERT_ID,PATIENT_ID,TREATMENT_PLAN_ID,ALERT_DATE_TIME,STATUS)
      VALUES 
            (:old.TRTMT_PLAN_ALERT_ID,:old.PATIENT_ID,:old.TREATMENT_PLAN_ID,:old.ALERT_DATE_TIME,:old.STATUS);
END TR_Delete_EMRTrmtPlanAlert;
/




hi i need small help as column status=1 then only the insert sholud happen into the EMRTrmtPlanAlertDetailsHistory table.how to give the condition then in above trigger
Re: condition required in trigger [message #421150 is a reply to message #421148] Fri, 04 September 2009 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>IF INSERTING THEN
>:OLD.STATUS=1 THEN

Not valid syntax; please fix it.
Re: condition required in trigger [message #421152 is a reply to message #421150] Fri, 04 September 2009 10:17 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
CREATE OR REPLACE TRIGGER TR_Delete_EMRTrmtPlanAlert
AFTER DELETE ON EMRTrmtPlanAlertDetails
FOR EACH ROW
DECLARE
PSTATUS  NUMBER(2);
BEGIN
   SELECT STATUS INTO PSTATUS FROM EMRTrmtPlanAlertDetails;
   IF  PSTATUS=1 THEN 
    BEGIN
       INSERT INTO EMRTrmtPlanAlertDetailsHistory(TRTMT_PLAN_ALERT_ID,PATIENT_ID,TREATMENT_PLAN_ID,ALERT_DATE_TIME,STATUS)
         VALUES 
            (:old.TRTMT_PLAN_ALERT_ID,:old.PATIENT_ID,:old.TREATMENT_PLAN_ID,:old.ALERT_DATE_TIME,:old.STATUS);
    END;
END IF;
END TR_Delete_EMRTrmtPlanAlert;
/




i have modified like this?

[Updated on: Fri, 04 September 2009 10:20]

Report message to a moderator

Re: condition required in trigger [message #421153 is a reply to message #421152] Fri, 04 September 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have modified like this?

And then what happened?

Regards
Michel
Re: condition required in trigger [message #421157 is a reply to message #421148] Fri, 04 September 2009 10:45 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I can see you need a simple IF condition - you appear to be overcomplicating the problem.
Re: condition required in trigger [message #421158 is a reply to message #421157] Fri, 04 September 2009 10:54 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi help me in giving condition
Re: condition required in trigger [message #421159 is a reply to message #421148] Fri, 04 September 2009 10:58 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You really need me to tell you how to write a simple IF?
Write it as you stated it in your first post.
Re: condition required in trigger [message #421163 is a reply to message #421159] Fri, 04 September 2009 11:09 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
CREATE OR REPLACE TRIGGER TR_Delete_EMRTrmtPlanAlert
AFTER DELETE ON EMRTrmtPlanAlertDetails
FOR EACH ROW

BEGIN
   IF  :old.STATUS=1 THEN 
 
       INSERT INTO EMRTrmtPlanAlertDetailsHistory(TRTMT_PLAN_ALERT_ID,PATIENT_ID,TREATMENT_PLAN_ID,ALERT_DATE_TIME,STATUS)
         VALUES 
            (:old.TRTMT_PLAN_ALERT_ID,:old.PATIENT_ID,:old.TREATMENT_PLAN_ID,:old.ALERT_DATE_TIME,:old.STATUS);
   END IF;
END TR_Delete_EMRTrmtPlanAlert;
/



THANK YOU
Previous Topic: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Next Topic: Procedure Compiles with warnings if I include an IF statement
Goto Forum:
  


Current Time: Sun Dec 04 22:46:54 CST 2016

Total time taken to generate the page: 0.04268 seconds