error Ora-04079 and inline query in triggers (merged) AND converting sqlserver code to oracle (merge [message #377468] |
Tue, 23 December 2008 03:19  |
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   |
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 #377474 is a reply to message #377471] |
Tue, 23 December 2008 03:32   |
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, & 255) = substring(columns_updated(),1, ) or Not ((substring(columns_updated(),2, | 0) <> 1 or (substring(columns_updated(),2, | 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   |
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 #377485 is a reply to message #377474] |
Tue, 23 December 2008 04:00   |
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?
|
|
|
|
|
WRITING INLINE QUERY [message #377503 is a reply to message #377468] |
Tue, 23 December 2008 04:57   |
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   |
rleishman
Messages: 3728 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   |
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 #377567 is a reply to message #377549] |
Tue, 23 December 2008 09:17  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|