问题描述
我正在使用两个SQL Server，一个是SQL Server 2000，另一个是SQL Server 2005。
I'm using two SQL Server, one is SQL Server 2000 and the other one is SQL Server 2005.
SQL Server 2000中的table1更新/插入/删除后，我必须更新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!
推荐答案
如果您想要复制数据，而不仅仅是设置不同的内容，您应该查看SQL复制，因为它可以更好地管理事物。例如，它将异步执行更新。
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.
如果您必须同步进行更新，或者只是认为它更简单，或者需要将整个操作包装为一个事务，则将逻辑放在清洁程序中。您可以从2000到2005创建一个链接服务器，并从那里将表引用为SERVER.DATABASE.SCHEMA.TABLE。或者，您可以在远程服务器上执行存储过程以执行插入/更新/删除操作。
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.
如果您不希望进行SQL复制，则可以考虑编写插入操作通过触发器将/ update /从2000年删除到同一数据库内的单独表中。然后有一个单独的作业，将这些更改写入2005，然后将其从表中删除。这意味着您可以批处理更改，意味着可以更快地完成对原始表的更新，并且可以更好地处理两台服务器之间的连接丢失。但是，您不能保证将更新实际应用于2005服务器，并且必须执行的sql作业的复杂性更高。所以这是一个权衡。一旦开始编写和维护这种逻辑，您就会意识到这就是MS编写复制内容的原因，因此您不必这样做。
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.
这篇关于触发更新另一个SQL Server上的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！