如何正确触发插入到链接的SQL Server? [英] How to properly trigger an insert to a linked sql server?

问题描述

我在两个不同的sql服务器中有一个相同的表(一个是SqlServer 2000，另一个是SqlServer 2000).

I have the same table in two different sql servers (one is SqlServer 2000 and the other 2008).

我正在使用SQL Server Management Studio.

I'm using sql server management studio.

我希望每次在SqlServer 2000表(表_1)中的表上进行插入时，都会发生触发器，并且该记录也将插入到SqlServer 2008表(也表_1)中的同一表中.

I want that each time an insert is made on a table in the SqlServer 2000 table (Table_1) a trigger occurs and the record would also be inserted to the same table in the SqlServer 2008 table (also Table_1).

将sql server 2008定义为链接服务器，并且可以使用sql server management studio从2000 db连接中对2008 db运行查询并执行插入操作.

the sql server 2008 is defined as a linked server, and it is possible to run queries and perform inserts on the 2008 db from the 2000 db connection using sql server management studio.

触发器定义如下:

ALTER TRIGGER [dbo].[trgrTable] 
   ON  [dbo].[Table_1]
   AFTER INSERT
AS 
BEGIN

 INSERT INTO [TLVSQL].[AVI_DEV].[dbo].[Table_1](ID, Value)
      SELECT INSERTED.ID AS ID, INSERTED.Value AS Value
      FROM INSERTED

END

[TLVSQL].[AVI_DEV]是2008数据库名称.

the [TLVSQL].[AVI_DEV] is the 2008 db name.

但是每次我在2000表上执行插入操作时，都会收到一条消息，提示插入失败，原因是"sqloledb无法启动分布式事务链接服务器...".

But every time i perform an insert on the 2000 table i get a message that the insert failed to commit due to "sqloledb was unable to begin a distributed transaction linked server ...".

链接服务器的安全委托定义明确，我将安全凭证明确设置为db_owner的用户/密码.

The security delegation of the linked server is well defined, i explicitly set the security credentials to a user/password of a db_owner.

我在做什么错?还有另一种方法可以执行我的要求吗?

What am i doing wrong ? Is there another way of doing what i ask ?

谢谢.

推荐答案

执行从触发器插入链接服务器上的表的操作-错误的决定. 这将极大地影响源表([dbo].[Table_1])中的插入性能. 还有一个分布式事务，并配置服务器 支持分布式交易-噩梦.

Perform inserts from a trigger into a table on linked server - bad decision. This will great affects on insert performance in the source table ([dbo].[Table_1]) and also there is a distributed transaction, and configuring servers for support distributed transactions - nightmare.

一种可能的解决方案是:

One possible solution is:

1. 在源服务器上，您可以创建一个同步队列表.例如:

1. On the source server you can create a synchronization queue table. For example:

CREATE TABLE dbo.SyncQueue
(
    QueueId INT IDENTITY(1,1),        
    KeyForSync INT, -- Primary key value of record in dbo.SourceTable
    SyncStatus INT  -- statuses can be: 0 - New, 1 - Synchronized, 2 - Error
)

suppose you source table is

CREATE TABLE dbo.SourceTable
(
        Key INT, -- primary key of the table
        Data varchar(xxx)
)

dbo.SourceTable上的Triger可以快速将其插入dbo.SyncQueue记录中，您需要同步的键

Triger on dbo.SourceTable can quickly insert into dbo.SyncQueue record Key that you need synchronize

这篇关于如何正确触发插入到链接的SQL Server?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！