Post reply

Is it possible to have a SQL Server Trigger that calls a Stored Proc in Oracle?

  • August 19, 2004 at 9:37 am

    #86330

    I want to create a trigger in SQL Server that fires when a record is added to a table that, in turn, could call an Oracle stored procedure to perform a function in the Oracle database.  I think I can do this by calling a external custom application using xp_cmdshell in the trigger, but is it possible to perform this with the Oracle database joined as a linked server to MS SQL Server?  If so, how would you accomplish it?

    Thanks,

    Steve

  • August 19, 2004 at 12:22 pm

    #519862

    Sure you can.  You need to have the appropriate Oracle ODBC driver installed on your server, configure your Linked Server and make sure that RPC is checked on the Server Options tab.

    Now you should be able to make a call to the Oracle Stored Procedure.  Something like:

     EXEC LinkedServerName..SchemaName.ProcedureName

     

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply