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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use DTS and have the trigger call the DTS package...
the DTS package can manipulate the oracle database
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.
>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.
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.