Link to home
Start Free TrialLog in
Avatar of dosdemon2
dosdemon2

asked on

Firing an SQL Server Trigger to Update Oracle?

Hi everyone!

I am working on linking two seperate systems:
1) ASP.NET web application, uses Oracle 9i
2) VB 6 desktop application, uses SQL Server 2000

The question is that can I make a trigger on my SQL Server database, so that whenever a record is inserted in my SQL Server table, it automatically also updates my Oracle database with the new changes? Is this possible? If possible then source code will be highly appreciated.

Second question is that can we do it the other way around? That is: can I make a trigger on my Oracle database, so that whenever a record is inserted in my Oracle table, it automatically also updates my SQL Server database with the new changes?

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As for transactional safety, lluthien's comments are worth considering.  You need to create transactions on both systems in both cases.  Going from Oracle to SQL Server, if the SQL Server transaction fails, the Oracle transaction must either fail or generate an error mark.  Even if the transaction succeeds, a verifying read should probably take place.  In the case of going from SQL Server to Oracle, similar considerations should be made.  

I also understand that some condition mandate the immediacy of update for maximum concurrency between two databases (why else have merge replication between SQL Servers?).  So, it is up to you to make this as bulletproof as possible if you must go down this path, or allow tolerance for concurrency failures between your data servers.
You can use DTS and have the trigger call the DTS package...
the DTS package can manipulate the oracle database
From rbrooker

>You can use DTS and have the trigger call the DTS package...
>the DTS package can manipulate the oracle database

The same problems with transactional integrity apply to this approach, though by using an ActiveX Script this approach closely mirrors how I suggested doing it from the Oracle side.