Updating an oracle database from sql server db trigger

[nzcam]

Is there a way to update an oracle database from a sql server 2005 database trigger ( or similar).

Eg, When an insert or update on a table in a sql server 2005 db occurs a trigger (or something) fires and updates an oracle database.

 

[mrdenny]

You can do this without a problem. Setup the Oracle drivers and tnsnames on the SQL Server then setup a linked server to the Oracle server. You can then access the objects on the Oracle server via the three part name converted to the four part format.

update OracleServer..Schema.Object a
   SET a.Column = inserted.Column2
FROM inserted
WHERE a.Key = inserted.Key

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog