OraFAQ Forum: SQL & PL/SQL » Using Trigger in Oracle DB to update a table in SQL Server DB

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using Trigger in Oracle DB to update a table in SQL Server DB [message #665105]

Tue, 22 August 2017 01:31

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.

Report message to a moderator

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

John Watson
Messages: 9000
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.

Report message to a moderator

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

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

Frank Naude
Messages: 4596
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.

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 14 16:12:02 CDT 2026