Home » SQL & PL/SQL » SQL & PL/SQL » Using Trigger in Oracle DB to update a table in SQL Server DB (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Using Trigger in Oracle DB to update a table in SQL Server DB [message #665105] Tue, 22 August 2017 01:31 Go to next message
queeny
Messages: 20
Registered: July 2011
Location: india
Junior Member
Hi,

I am exploring options to invoke a trigger from Oracle DB which updates a table in the SQL Server Database.

For example, i have a trigger T1 defined on a table T1, which upon invoking should update a table T2 in SQL Server DB.

Please suggest if this is possible or not.
Re: Using Trigger in Oracle DB to update a table in SQL Server DB [message #665107 is a reply to message #665105] Tue, 22 August 2017 02:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This sounds feasible, but I would be concerned about transactional integrity. Can you implement a proper two phase commit for a transaction distributed between an Oracle database and a SQL Server database?
I would usually not do this sort of integration with a trigger directly, but rather via an advanced queue. Have your trigger put a message on the queue, and process the messages asynchronously with a scheduled job. That will give you much more control.
Re: Using Trigger in Oracle DB to update a table in SQL Server DB [message #665108 is a reply to message #665105] Tue, 22 August 2017 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We hope you will feedback in this topic unlike in your previous ones.

Re: Using Trigger in Oracle DB to update a table in SQL Server DB [message #665114 is a reply to message #665108] Tue, 22 August 2017 03:45 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Should be easy if you have an Oracle Gateway running (not so easy to configure, but doable).
You reference the remote table across a DB link from your trigger.
Re: Using Trigger in Oracle DB to update a table in SQL Server DB [message #665123 is a reply to message #665107] Tue, 22 August 2017 05:59 Go to previous messageGo to next message
queeny
Messages: 20
Registered: July 2011
Location: india
Junior Member
Thanks Watson for the response.

We already have the queue system in place but i am looking for an online refresh. i meant immediate refresh without waiting for a job to take care.

Let me check with the ODBC Connection itself.
Re: Using Trigger in Oracle DB to update a table in SQL Server DB [message #665124 is a reply to message #665123] Tue, 22 August 2017 07:43 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I doubt ODBC does 2 phase commit between Oracle and SQL Server.

[Updated on: Tue, 22 August 2017 07:52]

Report message to a moderator

Previous Topic: File compressing is not happening
Next Topic: Applying function on column having multiple datatypes
Goto Forum:
  


Current Time: Fri Mar 29 02:40:16 CDT 2024