Home » SQL & PL/SQL » SQL & PL/SQL » equivalent of oracle (oracle 10g)
equivalent of oracle [message #421997] |
Sat, 12 September 2009 21:56 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
Hi i have written a trigger in sql server where i found difficut to replicate the same in oracle.my sql server trigger is like this.
CREATE TRIGGER TR_INSERT_EMRAppointmentDetailsHist
ON dbo.EMRAppointmentDetailsHistory
INSTEAD OF INSERT
AS
BEGIN
DECLARE @ASTATUS NUMERIC(20,0);
SELECT @ASTATUS = (SELECT APPOINTMENT_STATUS_ID FROM inserted)
IF @ASTATUS <> 9
BEGIN
INSERT INTO EMRAppointmentDetailsHistory (
APPOINTMENT_ID,PATIENT_ID,REFERED_BY,SPECIALTY_ID,REASON,CONSULTANT_ID,
APPOINTMENT_TYPE_ID,APPOINTMENT_DATE,APPOINTMENT_TIME,FRONTDESK_COMMENTS,
APPOINTMENT_STATUS_ID,CREATED_DATE,USER_LOGIN,VISIT_TYPE_ID,APPOINTMENT_LOCATION_ID,
HEALTH_PKG_ID,DISP_APPOINTMENT_ID,SRV_ITEM_ID,FULFILLED_APPOINTMENT_ID,
FULFILLED_LOCATION_ID,FULFILLED_LOCATION,IS_LAB_APMT,TP_PKG_ID,TP_ITEM_ID,LAB_TEST_ID,
LAB_PROFILE_ID,LAST_UPDATED_DATE,LAST_UPDATED_USER,IS_TP_APPOINTMENT,BILL_NUMBER )
SELECT
APPOINTMENT_ID,PATIENT_ID,REFERED_BY,SPECIALTY_ID,REASON,CONSULTANT_ID,
APPOINTMENT_TYPE_ID,APPOINTMENT_DATE,APPOINTMENT_TIME,FRONTDESK_COMMENTS,
APPOINTMENT_STATUS_ID,CREATED_DATE,USER_LOGIN,VISIT_TYPE_ID,APPOINTMENT_LOCATION_ID,
HEALTH_PKG_ID,DISP_APPOINTMENT_ID,SRV_ITEM_ID,FULFILLED_APPOINTMENT_ID,
FULFILLED_LOCATION_ID,FULFILLED_LOCATION,IS_LAB_APMT,TP_PKG_ID,TP_ITEM_ID,LAB_TEST_ID,
LAB_PROFILE_ID,LAST_UPDATED_DATE,LAST_UPDATED_USER,IS_TP_APPOINTMENT,BILL_NUMBER
FROM inserted
END
ELSE
BEGIN
INSERT INTO EMRAppDetailsSuppHistory (
APPOINTMENT_ID,PATIENT_ID,REFERED_BY,SPECIALTY_ID,REASON,CONSULTANT_ID,
APPOINTMENT_TYPE_ID,APPOINTMENT_DATE,APPOINTMENT_TIME,FRONTDESK_COMMENTS,
APPOINTMENT_STATUS_ID,CREATED_DATE,USER_LOGIN,VISIT_TYPE_ID,APPOINTMENT_LOCATION_ID,
HEALTH_PKG_ID,DISP_APPOINTMENT_ID,SRV_ITEM_ID,FULFILLED_APPOINTMENT_ID,
FULFILLED_LOCATION_ID,FULFILLED_LOCATION,IS_LAB_APMT,TP_PKG_ID,TP_ITEM_ID,LAB_TEST_ID,
LAB_PROFILE_ID,LAST_UPDATED_DATE,LAST_UPDATED_USER,IS_TP_APPOINTMENT,BILL_NUMBER )
SELECT
APPOINTMENT_ID,PATIENT_ID,REFERED_BY,SPECIALTY_ID,REASON,CONSULTANT_ID,
APPOINTMENT_TYPE_ID,APPOINTMENT_DATE,APPOINTMENT_TIME,FRONTDESK_COMMENTS,
APPOINTMENT_STATUS_ID,CREATED_DATE,USER_LOGIN,VISIT_TYPE_ID,APPOINTMENT_LOCATION_ID,
HEALTH_PKG_ID,DISP_APPOINTMENT_ID,SRV_ITEM_ID,FULFILLED_APPOINTMENT_ID,
FULFILLED_LOCATION_ID,FULFILLED_LOCATION,IS_LAB_APMT,TP_PKG_ID,TP_ITEM_ID,LAB_TEST_ID,
LAB_PROFILE_ID,LAST_UPDATED_DATE,LAST_UPDATED_USER,IS_TP_APPOINTMENT,BILL_NUMBER
FROM inserted
END
END
|
|
|
Re: equivalent of oracle [message #421998 is a reply to message #421997] |
Sat, 12 September 2009 22:04 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
> i have written a trigger in sql server
or inherited the trigger & don't understand it
>where i found difficut to replicate the same in oracle.
So I want somebody to do my job for me.
How would an independent observer decide any proposed solution is correct?
Post DDL for tables.
Post DML for test data.
Post expected/desired results
Post detailed explanation how & why test data gets transformed into expected/desired results.
[Updated on: Sat, 12 September 2009 22:04] Report message to a moderator
|
|
|
Re: equivalent of oracle [message #421999 is a reply to message #421998] |
Sat, 12 September 2009 22:09 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
Hi Blackswan sorry for my post but i tried writing the above one like this in oracle.
CREATE OR REPLACE TRIGGER TR_INSERT_EMRAppointmentDetailsHist
BEFORE INSERT ON EMRAppointmentDetailsHistory
FOR EACH ROW
AS
BEGIN
IF :new.APPOINTMENT_STATUS_ID <> 9 THEN
INSERT INTO EMRAppointmentDetailsHistory (
APPOINTMENT_ID,PATIENT_ID,REFERED_BY,SPECIALTY_ID,REASON,CONSULTANT_ID,
APPOINTMENT_TYPE_ID,APPOINTMENT_DATE,APPOINTMENT_TIME,FRONTDESK_COMMENTS,
APPOINTMENT_STATUS_ID,CREATED_DATE,USER_LOGIN,VISIT_TYPE_ID,APPOINTMENT_LOCATION_ID,
HEALTH_PKG_ID,DISP_APPOINTMENT_ID,SRV_ITEM_ID,FULFILLED_APPOINTMENT_ID,
FULFILLED_LOCATION_ID,FULFILLED_LOCATION,IS_LAB_APMT,TP_PKG_ID,TP_ITEM_ID,LAB_TEST_ID,
LAB_PROFILE_ID,LAST_UPDATED_DATE,LAST_UPDATED_USER,IS_TP_APPOINTMENT,BILL_NUMBER)
VALUES
(:old.APPOINTMENT_ID,:old.PATIENT_ID,:old.REFERED_BY,:old.SPECIALTY_ID,:old.REASON,CONSULTANT_ID,
:old.APPOINTMENT_TYPE_ID,:old.APPOINTMENT_DATE,:old.APPOINTMENT_TIME,:old.FRONTDESK_COMMENTS,
:old.APPOINTMENT_STATUS_ID,:old.CREATED_DATE,:old.USER_LOGIN,:old.VISIT_TYPE_ID,:old.APPOINTMENT_LOCATION_ID,
:old.HEALTH_PKG_ID,:old.DISP_APPOINTMENT_ID,:old.SRV_ITEM_ID,:old.FULFILLED_APPOINTMENT_ID,
:old.FULFILLED_LOCATION_ID,:old.FULFILLED_LOCATION,:old.IS_LAB_APMT,:old.TP_PKG_ID,:old.TP_ITEM_ID,:old.LAB_TEST_ID,
:old.LAB_PROFILE_ID,:old.LAST_UPDATED_DATE,:old.LAST_UPDATED_USER,:old.IS_TP_APPOINTMENT,:old.BILL_NUMBER)
ELSE
INSERT INTO EMRAppDetailsSuppHistory (
APPOINTMENT_ID,PATIENT_ID,REFERED_BY,SPECIALTY_ID,REASON,CONSULTANT_ID,
APPOINTMENT_TYPE_ID,APPOINTMENT_DATE,APPOINTMENT_TIME,FRONTDESK_COMMENTS,
APPOINTMENT_STATUS_ID,CREATED_DATE,USER_LOGIN,VISIT_TYPE_ID,APPOINTMENT_LOCATION_ID,
HEALTH_PKG_ID,DISP_APPOINTMENT_ID,SRV_ITEM_ID,FULFILLED_APPOINTMENT_ID,
FULFILLED_LOCATION_ID,FULFILLED_LOCATION,IS_LAB_APMT,TP_PKG_ID,TP_ITEM_ID,LAB_TEST_ID,
LAB_PROFILE_ID,LAST_UPDATED_DATE,LAST_UPDATED_USER,IS_TP_APPOINTMENT,BILL_NUMBER )
VALUES
(:old.APPOINTMENT_ID,:old.PATIENT_ID,:old.REFERED_BY,:old.SPECIALTY_ID,:old.REASON,:old.CONSULTANT_ID,
:old.APPOINTMENT_TYPE_ID,:old.APPOINTMENT_DATE,:old.APPOINTMENT_TIME,:old.FRONTDESK_COMMENTS,
:old.APPOINTMENT_STATUS_ID,:old.CREATED_DATE,:old.USER_LOGIN,:old.VISIT_TYPE_ID,:old.APPOINTMENT_LOCATION_ID,
:old.HEALTH_PKG_ID,:old.DISP_APPOINTMENT_ID,:old.SRV_ITEM_ID,:old.FULFILLED_APPOINTMENT_ID,
:old.FULFILLED_LOCATION_ID,:old.FULFILLED_LOCATION,:old.IS_LAB_APMT,TP_PKG_ID,:old.TP_ITEM_ID,LAB_TEST_ID,
:old.LAB_PROFILE_ID,:old.LAST_UPDATED_DATE,:old.LAST_UPDATED_USER,:old.IS_TP_APPOINTMENT,BILL_NUMBER)
END IF;
END;
/
[Updated on: Sat, 12 September 2009 22:10] Report message to a moderator
|
|
|
|
|
Re: equivalent of oracle [message #422002 is a reply to message #421997] |
Sat, 12 September 2009 22:27 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
How would an independent observer decide any proposed solution is correct?
Have you provided sufficient details to allow any reader of this thread to successfully compile your trigger or their own?
If you have not done so, how much assistance can be provided?
>iam not sure about instead of insert in sql server as for in oracle?
What is URL that documents "INSTEAD OF INSERT" as part of trigger?
[Updated on: Sat, 12 September 2009 22:28] Report message to a moderator
|
|
|
Re: equivalent of oracle [message #422003 is a reply to message #422002] |
Sat, 12 September 2009 22:53 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
Mu purpose is when iam inserting values with appointment_status_id=9 into the table EMRAppointmentDetailsHistory it should not insert into that table and particular value should insert into the EMRAppDetailsSuppHistory table.apart for value as 9 it can insert into the EMRAppointmentDetailsHistory table.that i need a trigger
|
|
|
|
Re: equivalent of oracle [message #422064 is a reply to message #422004] |
Mon, 14 September 2009 03:25 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The problem is that from a trigger on Table A, there is no easy way to prevent a record from inserting into Table A that doesn't involve raising an exception.
Your best solution might be to create a View on this table, and then write an INSTEAD OF INSERT trigger on that view that does the logic that BlackSwan suggested.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Nov 04 04:26:46 CST 2024
|