触发器通过连接服务器更新数据

触发器通过连接服务器更新数据

最近碰到一个需求

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值。说明事务都提交了。

反思

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