Home » SQL & PL/SQL » SQL & PL/SQL » equivalent of oracle (oracle 10g)
equivalent of oracle [message #421997] Sat, 12 September 2009 21:56 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #422000 is a reply to message #421997] Sat, 12 September 2009 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> i tried writing the above one like this in oracle.
So?
What is trigger supposed to do?
What specific problem do you have doing the same in PL/SQL?
Re: equivalent of oracle [message #422001 is a reply to message #422000] Sat, 12 September 2009 22:22 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
iam not sure about instead of insert in sql server as for in oracle?
Re: equivalent of oracle [message #422002 is a reply to message #421997] Sat, 12 September 2009 22:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #422004 is a reply to message #421997] Sat, 12 September 2009 22:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If appointment_status_id=9
THEN
    INSERT INTO EMRAppDetailsSuppHistory ....
ELSE
    INSERT INTO EMRAppointmentDetailsHistory  ....
END IF;
Re: equivalent of oracle [message #422064 is a reply to message #422004] Mon, 14 September 2009 03:25 Go to previous messageGo to next message
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.
Re: equivalent of oracle [message #422163 is a reply to message #422064] Mon, 14 September 2009 22:33 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can ypu give me the steps in doing so
Re: equivalent of oracle [message #422169 is a reply to message #421997] Mon, 14 September 2009 23:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Do some googling and read about it. 30 minutes with google (or your other favorite search engine) later you will have it worked out. Once you have a solution post it for us.

Kevin

[Updated on: Mon, 14 September 2009 23:06]

Report message to a moderator

Re: equivalent of oracle [message #422343 is a reply to message #422163] Tue, 15 September 2009 09:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here is the syntax for CREATE VIEW

There are links in the article telling you how to create Instead Of triggers.
Re: equivalent of oracle [message #422387 is a reply to message #422163] Wed, 16 September 2009 00:21 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://www.psoug.org/reference/instead_of_trigger.html
An example

[Updated on: Wed, 16 September 2009 00:27]

Report message to a moderator

Previous Topic: how to take values by user
Next Topic: Bulk Collect for Record type
Goto Forum:
  


Current Time: Mon Nov 04 04:26:46 CST 2024