如何正确触发对链接的 sql server 的插入?

2022-01-01 00:00:00 sql sql-server-2008 sql-server triggers linked-server

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

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 表 (Table_1) 中的表上进行插入时都会触发一个触发器，并且该记录也将插入到 SqlServer 2008 表(也是 Table_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记录需要同步的Key

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

    • 相关文章