Home » SQL & PL/SQL » SQL & PL/SQL » will this code work properly (oracle10g)
will this code work properly [message #378105] Sun, 28 December 2008 23:15 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hai, iam going to create a trigger where i have to insert values into a table, will the above code works properly,if not please send the exact ones as iam not sure about it.

create or replace trigger TR_Update_EMRPatientsMaster
after update of location_id,modified_date on EMRPatientsMaster
for each row
declare
ppatientid varchar2(20);
begin
ppatientid:=:old.ppatientid;
      if ezexists(select count(*) from EMRTransferredPatients  where patient_id in(ppatientid) and staus=1)>=1) then 
      if location_id then
      begin
           INSERT INTO EMRPatientsMasterHistory (
        PATIENT_ID, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_DOB,
        PATIENT_HOMEPHONE, PATIENT_GENDER, RACE_ETHNICITY_ID,
        MARITAL_STATUS, LOCATION_ID, PATIENT_TYPE, MODIFIED_DATE,REGISTER_ID,ISAPPROXDOB, STATUS)
 VALUES (
 :old.PATIENT_ID, :old.PATIENT_FIRSTNAME, :old.PATIENT_LASTNAME, :old.PATIENT_DOB,
 :old.PATIENT_HOMEPHONE, :old.PATIENT_GENDER, :old.RACE_ETHNICITY_ID,
 :old.MARITAL_STATUS, :old.LOCATION_ID, :old.PATIENT_TYPE, :old.MODIFIED_DATE,
 :old.REGISTER_ID,:old.ISAPPROXDOB,:old.STATUS);
 end;
      else modified_date then
       begin
	   INSERT INTO EMRPatientsMasterHistory (
        PATIENT_ID, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_DOB,
        PATIENT_HOMEPHONE, PATIENT_GENDER, RACE_ETHNICITY_ID,
        MARITAL_STATUS, LOCATION_ID, PATIENT_TYPE, MODIFIED_DATE,REGISTER_ID,ISAPPROXDOB, STATUS)
 VALUES (
 :old.PATIENT_ID, :old.PATIENT_FIRSTNAME, :old.PATIENT_LASTNAME, :old.PATIENT_DOB,
 :old.PATIENT_HOMEPHONE, :old.PATIENT_GENDER, :old.RACE_ETHNICITY_ID,
 :old.MARITAL_STATUS, :old.LOCATION_ID, :old.PATIENT_TYPE, :old.MODIFIED_DATE,
 :old.REGISTER_ID,:old.ISAPPROXDOB,:old.STATUS);
 end;
 end if;
 end;
Re: will this code work properly [message #378119 is a reply to message #378105] Mon, 29 December 2008 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What happened when you tried it? After all, trying it yourself is way faster than asking here, so I take it you already did.
Re: will this code work properly [message #378123 is a reply to message #378105] Mon, 29 December 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous posts:
Michel Cadot wrote on Tue, 23 December 2008 16:17
In the meantime, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Re: will this code work properly [message #378167 is a reply to message #378105] Mon, 29 December 2008 03:41 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
      if ezexists(select count(*) from EMRTransferredPatients  where patient_id in(ppatientid) and staus=1)>=1) then 
      if location_id then
      <statements>
     else modified_date then
      <statements>
 end if;

Before using IF Statement and SQL in PL/SQL, it would be good to study its syntax. It is described in PL/SQL User's Guide and Reference, found (with another Oracle documentation books) e.g. online on http://tahiti.oracle.com/.
Re: will this code work properly [message #378175 is a reply to message #378167] Mon, 29 December 2008 04:02 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

*Brrr* triggers, always gives me the chill
Re: will this code work properly [message #378178 is a reply to message #378167] Mon, 29 December 2008 04:08 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
what are all the things i have to change in tha above code.can i proceed like the above ones?
Re: will this code work properly [message #378249 is a reply to message #378178] Mon, 29 December 2008 08:15 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rajasekhar857 wrote on Mon, 29 December 2008 11:08
what are all the things i have to change in tha above code.can i proceed like the above ones?

What about
Quote:
Before using IF Statement and SQL in PL/SQL, it would be good to study its syntax.
? In addition, what shall "if location_id then" mean? If you read it as English sentence, it makes no sense there is no condition at all. Finally LOCATION_ID does not represent here anything (as the current row values are available using :NEW and :OLD records).
As you did not post, what "works properly" exactly means, it is hard to even guess. But, with the help of documentation and exact requirements, you shall surely figure that yourself.
Previous Topic: stored procedure inside a stored procedure dynamically
Next Topic: How to execute a procedure from trigger?
Goto Forum:
  


Current Time: Fri Dec 09 17:05:01 CST 2016

Total time taken to generate the page: 0.65247 seconds