sqlserver跨实例实时同步数据——触发器（SQLNCLI10无法启动分布式事务）

方法1：

比如：想在更改A服务器中数据库A1中A2表的同时，将数据同步到B服务器中数据库B2中B2中，可采取在A2表上建立触发器，在A2表进行数据DDL时调取B2上的存储过程通过传参（如索引、唯一键等方式进行数据识别）...嗯...实现办法嘛...看自己个人的SQL能力吧

以下为方法2：

https://www.cnblogs.com/songr/p/4129006.html

最近碰到一个需求

A,B 2个SQL SERVER 2008数据库，分别在不同的服务器上。当A库的TableA 插入一条数据的时候，将B库中TableB 中对应的记录更新状态。

原计划是写一个服务来定时同步，但用户要的紧，就写了一个触发器+链接服务器来实现。中间碰到一些问题这里做一下总结

 模拟数据

1. 要模拟2台服务器，我使用的虚拟机.来模拟2个服务器

2. 1号服务器上建立A数据库和TableA

create database A go use A go CREATE TABLE TableA (     id int,     MapId int )

1. 2号服务器上建立B数据库和TableB，并插入一些测试数据

create database B GO USE B GO CREATE TABLE TableB ( MapId int, State int ) GO   INSERT INTO TableB VALUES (1,0) INSERT INTO TableB VALUES (2,0) INSERT INTO TableB VALUES (3,0) INSERT INTO TableB VALUES (4,0) INSERT INTO TableB VALUES (5,0)

1. 其中TableA 的 Mapid 对应 TableB的 MapID, 需求就是，当TABLEA中INSERT 一条记录的时候。将TableB中 MapID 对应的记录的State 改成 1

实现

1. 给在1号服务器上建立链接服务器，链接到2号服务器

exec sp_addlinkedserver 'Server1', '', 'SQLOLEDB', '192.168.50.1' exec sp_addlinkedsrvlogin 'Server1', 'false', NULL, 'sa', 'mamahao'

1. 给1号服务器的A库下的TableA 增加触发器

CREATE TRIGGER tg_insert_TableA ON tableA AFTER INSERT AS BEGIN DECLARE @MAPId INT SELECT @MAPId = MapID FROM INSERTED UPDATE SERVER1.B.dbo.TableB SET STATE = 1 WHERE MAPID = @MAPId END

 

1. 测试，给TableA 插入数据

INSERT INTO tableA VALUES (1,1)

但是1号服务器报错

分析

1. 起初以为是MSDTC 没有启动，但都检查服务器启动成功。更重要的是，在A库中执行下面的语句

   UPDATE SERVER1.B.dbo.TABLEB SET STATE=100 WHERE MAPID = 2

   是没有问题，可见分布式服务都正常。

2. 打上断点，跟踪发现，进入了触发器。但是执行Update语句就失败。

   

3. 反复测试之后，发现

   

   只要带上了 BEGIN TRAN …COMMIT 就报错

4. 使用 分布式事务

   

   也不行。

5. 感觉只有隐式事务的时候才成功。想到这个2个同步不一定要严格的事务关系。并且

   A服务器的INSERT 一定要成功，B服务器的更新偶尔失败没关系。

解决

1. 既然只有隐式的事务可以成功。那么我先确保TABLEA 的INSERT 事务成功再去更新·TABLEB

   于是对触发器做出如下修改

ALTER TRIGGER tg_insert_TableA ON tableA AFTER INSERT AS BEGIN DECLARE @MAPId INT SELECT @MAPId = MapID FROM INSERTED COMMIT UPDATE SERVER1.B.dbo.TableB SET STATE = 1 WHERE MAPID = @MAPId BEGIN TRAN END

 

1. 测试验证

INSERT INTO tableA VALUES (1, 1) PRINT '当前事物' + CONVERT(VARCHAR, @@TRANCOUNT)

 

发现执行成功。

        而且@@Trancout 在这这个回话中是0值。说明事务都提交了。

反思

• 虽然数据是更新成功达到目的，但是为什么这样写触发器不会错，事务可以提交？ 我做了另外一个试验，总结完成再记录一篇随笔。
• 有没有其他的办法，可以满足分布式事务提交？ 有空要仔细研究研究。
• 这种触发器的方案，看起来是解决了问题，但是以后维护会不会不记得有触发器这个东西？