Hello,
I'm using two SQL Server, one is SQL Server 2000 and the other one is SQL Server 2005.
When the table1 in SQL Server 2000 gets updated/inserted/deleted, I have to update another table in SQL Server 2005. Is it possible to achieve that in a trigger? If not, what would be the possible options?
Thank you, have a nice day!
Yes, you can do this using a linked server and an trigger on the database that is being updated.
SO inside your trigger you would do something like this.
UPDATE linkedserver.Database1.dbo.myTable
SET ...
WHERE ---
with your values for the set and WHERE.
Now, th eonly thing would be how the linked server is setup between 2000 and 2005, you would have to try that out first.
If you're wanting to replicate the data, not just set something differently, you should look at SQL Replication as it'll manage things a lot better. eg it will do the updates asynchronously.
If you have to do them synchronously, or you just decide it's simpler or you need the whole operation wrapped as a single transaction, I'd put the logic in a procedure for cleanliness. You can create a linked server from 2000 to 2005 and refer to the table from there as SERVER.DATABASE.SCHEMA.TABLE. Alternatively you could execute a stored procedure on the remote server to do the insert/update/delete.
If you don't want SQL replication you might instead consider writing the insert/update/deletes from 2000 into a separate table within the same database, via the trigger. Then have a separate job that writes these changes to 2005 then removes them from the table. This would mean you could batch up the changes, mean updates to the original table would be done quicker, would deal better with lost connectivity between the two servers. However, you have less guarantee that the updates would actually be applied to the 2005 server, and you have the added complexity of a sql job that must be run. So it's a tradeoff. And once you start writing and maintaining this sort of logic you realise that's why MS wrote replication stuff, so you don't have to.
It is possible to use linked server and a trigger but I have only bad experiences from this.
Why not use triggers?
Two-way sync with triggers is tricky, because the triggers will fire each other. You will have to control this somehow, for example with special values. Otherwise, you will get strange locking errors.
You will need to set up MSDTC (Distributed Transaction Coordinator) between linked servers
DBMS can't help you very much with linked servers. it is much harder to debug SQL. Bad queries usually just hang and timeout when there is type mismatch etc.
Transactions with multiple writes in the trigger OR in the query launching the trigger cause deadlocks easily. I would use triggers only to very simple updates (one INSERT/UPDATE/DELETE statement) and even then make sure that deadlocks cannot occur. I remember one integration that I had to rewrite completely when a legacy app caused deadlocks with a trigger.
Alternatives
There are at least two questions to answer:
If the schemas match, replication should be ideal for both one-way and two-way synch.
If the schemas are different, like usually is the case with application integration (EAI), you might consider:
I don't have much experience with EAI tools but comparing SSIS to custom .NET solutions I can only say that you will save a lot of time if you can get the job done with SSIS.
Only if SSIS does not work or is not available (SQL Express) I would try programming a Windows service, WCF service etc.
I have the same question to ask, but I'm using both MSSQL 2000 Server.
When the table1 in MSSQL 2000 gets updated/inserted/deleted, I have to update another table in MSSQL 2000. How is it possible to achieve that ?
Thanks in advance