Home » SQL & PL/SQL » SQL & PL/SQL » error Ora-04079 and inline query in triggers (merged) AND converting sqlserver code to oracle (merge
error Ora-04079 and inline query in triggers (merged) AND converting sqlserver code to oracle (merge [message #377468] Tue, 23 December 2008 03:19 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hai all.

iam executing the below trigger and can i do like this and by testing it and iam getting error -ora -04079--invalid trigger specification

CREATE OR REPLACE TRIGGER "TR_Update_EMRPatientsMaster"
AFTER UPDATE OF LOCATION_ID, MODIFIED_DATE ON EMRPatientsMaster
AND EXISTS (SELECT * FROM EMRTransferredPatients WHERE PATIENT_ID IN (SELECT PATIENT_ID FROM Deleted) AND STATUS = 1)
FOR EACH ROW
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 )
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);

END TR_Update_EMRPatientsMaster


REPLY ME WHAT HAPPENING EXACTLY

Re: getting error 0ra-04079 [message #377471 is a reply to message #377468] Tue, 23 December 2008 03:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You've tried to make up some new syntax for triggers, and it's (suprisingly) not working.

Here's how to create a trigger: CREATE TRIGGER

The line that starts 'AND EXISTS' is the problem - there is no place for such a line in the create trigger syntax. You need to move it into the trigger body and make it part of an IF statement.
Re: getting error 0ra-04079 [message #377473 is a reply to message #377471] Tue, 23 December 2008 03:30 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
THEN HOW TO PROCEED THAT IN SQL SERVER WE ARE USING EXISTS CONDITION FOR TRIGGERS AMD WHWT E HAVE TO DO IT IN ORACLE
Re: getting error 0ra-04079 [message #377474 is a reply to message #377471] Tue, 23 December 2008 03:32 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
THE SAME THING IAM DOING IT IN SQL SERVER AS FOLLOWED


CREATE TRIGGER TR_Update_EMRPatientsMaster

ON EMRPatientsMaster

FOR UPDATE

AS

IF (UPDATE(LOCATION_ID) OR UPDATE(MODIFIED_DATE)) AND

EXISTS (SELECT * FROM EMRTransferredPatients WHERE PATIENT_ID IN (SELECT PATIENT_ID FROM Deleted) AND STATUS = 1)

--if ((substring(columns_updated(),1,Cool & 255) = substring(columns_updated(),1,Cool) or Not ((substring(columns_updated(),2,Cool | 0) <> 1 or (substring(columns_updated(),2,Cool | 0) <> 5)

BEGIN

INSERT EMRPatientsMasterHistory

SELECT

PATIENT_ID, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_DOB,

PATIENT_HOMEPHONE, PATIENT_GENDER, RACE_ETHNICITY_ID,

MARITAL_STATUS, LOCATION_ID, PATIENT_TYPE, MODIFIED_DATE

FROM Deleted

END

Re: getting error 0ra-04079 [message #377476 is a reply to message #377473] Tue, 23 December 2008 03:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

THEN HOW TO PROCEED THAT IN SQL SERVER WE ARE USING EXISTS CONDITION FOR TRIGGERS AMD WHWT E HAVE TO DO IT IN ORACLE


By any chance is your caps lock stuck to your keyboard so tightly ?

Regards

Raj
Re: getting error 0ra-04079 [message #377478 is a reply to message #377476] Tue, 23 December 2008 03:40 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
IAM GETTIN GIT IN SQL WAT ABOUT ORACLE
Re: getting error 0ra-04079 [message #377479 is a reply to message #377478] Tue, 23 December 2008 03:48 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You've already been told what to do. If you are unable to code basic IF-conditions, please ask a programmer to assist you or hire someone with basic programming skills.
Re: getting error 0ra-04079 [message #377485 is a reply to message #377474] Tue, 23 December 2008 04:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You may be suprised to learn that SQL Server and Oracle are quite different products.

What does this line do:
SELECT * FROM EMRTransferredPatients WHERE PATIENT_ID IN (SELECT PATIENT_ID FROM Deleted) AND STATUS = 1

I'm guessing that DELETED isn't the name of a database table (or if it is, it's one of the worst named tables I've seen this year).

Is this a check to see if the current patient's record has been deleted, or is it something else?
Re: getting error 0ra-04079 [message #377494 is a reply to message #377468] Tue, 23 December 2008 04:28 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
using after statement trigger and using
if inserting then,
if updating then,
if deleting then would be suffice on incorporating conditions
yours
dr.s.raghunathan
Re: getting error 0ra-04079 [message #377497 is a reply to message #377494] Tue, 23 December 2008 04:41 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i didn't get you what i have to place the query after the table name to work it properly.iam unable to get it.is there any other wat to work on it.
WRITING INLINE QUERY [message #377503 is a reply to message #377468] Tue, 23 December 2008 04:57 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
HI,

CAN I WRITE AN INLINE QUERY AFTER THE TABLE NAME AND BEFORE
THE FOR EACH ROW CONDITION IN A TRIGGER.IS IT POSSIBLE?



EX:


CREATE OR REPLACE TRIGGER "TR_Update_EMRPatientsMaster"

AFTER UPDATE OF LOCATION_ID, MODIFIED_DATE ON EMRPatientsMaster

AND (SELECT * FROM EMRTransferredPatients WHERE PATIENT_ID IN (SELECT PATIENT_ID FROM Deleted) AND STATUS = 1)

FOR EACH ROW

Re: WRITING INLINE QUERY [message #377518 is a reply to message #377503] Tue, 23 December 2008 05:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No.

The conditions in the header section of a trigger may only reference the columns of the row being inserted/updated/deleted.

The best you can do is to add a command at the beginning of the trigger (after the BEGIN) to execute your query and to then conditionally run the rest of the trigger.

Ross Leishman
converting sqlserver code to oracle [message #377546 is a reply to message #377468] Tue, 23 December 2008 07:42 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi can any one help me how to convert the following sql code into oracle code immediately



CREATE TRIGGER TR_Update_EMRPatientsMaster

ON EMRPatientsMaster

FOR UPDATE

AS

IF (UPDATE(LOCATION_ID))

Begin

IF EXISTS(SELECT * FROM EMRTransferredPatients WHERE PATIENT_ID IN (SELECT PATIENT_ID FROM Deleted) AND STATUS = 1)

Begin

INSERT EMRPatientsMasterHistory

SELECT PATIENT_ID, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_DOB,

PATIENT_HOMEPHONE, PATIENT_GENDER, RACE_ETHNICITY_ID,

MARITAL_STATUS, LOCATION_ID, PATIENT_TYPE, MODIFIED_DATE

FROM Deleted

END

END

ELSE IF (UPDATE(MODIFIED_DATE))

Begin

INSERT EMRPatientsMasterHistory

SELECT PATIENT_ID, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_DOB,

PATIENT_HOMEPHONE, PATIENT_GENDER, RACE_ETHNICITY_ID,

MARITAL_STATUS, LOCATION_ID, PATIENT_TYPE, MODIFIED_DATE

FROM Deleted

END



Re: converting sqlserver code to oracle [message #377547 is a reply to message #377546] Tue, 23 December 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, but not immediately.
Re: converting sqlserver code to oracle [message #377548 is a reply to message #377546] Tue, 23 December 2008 07:48 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I Hope this can help.

http://www.oracle.com/technology/tech/migration/workbench/files/mig_rel10104.html

Thanks
Trivendra
Re: converting sqlserver code to oracle [message #377549 is a reply to message #377547] Tue, 23 December 2008 07:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ok thank you i will wait
Re: converting sqlserver code to oracle [message #377567 is a reply to message #377549] Tue, 23 December 2008 09:17 Go to previous message
Michel Cadot
Messages: 64144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Previous Topic: How can I use Full Outer Join More Than Two Table.
Next Topic: CAN I INSERT DATA INTO THE TABLE USING NO DATA FOUND EXCEPTION
Goto Forum:
  


Current Time: Fri Dec 09 11:24:27 CST 2016

Total time taken to generate the page: 0.12338 seconds