触发器通过连接服务器更新数据
最近碰到一个需求
A,B 2个SQL SERVER 2008数据库,分别在不同的服务器上。当A库的TableA 插入一条数据的时候,将B库中TableB 中对应的记录更新状态。
原计划是写一个服务来定时同步,但用户要的紧,就写了一个触发器+链接服务器来实现。中间碰到一些问题这里做一下总结
模拟数据
-
要模拟2台服务器,我使用的虚拟机.来模拟2个服务器
-
1号服务器上建立A数据库和TableA
create database A go use A go CREATE TABLE TableA ( id int, MapId int ) |
-
2号服务器上建立B数据库和TableB,并插入一些测试数据
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) |
-
其中TableA 的 Mapid 对应 TableB的 MapID, 需求就是,当TABLEA中INSERT 一条记录的时候。将TableB中 MapID 对应的记录的State 改成 1
实现
- 给在1号服务器上建立链接服务器,链接到2号服务器
exec sp_addlinkedserver 'Server1', '', 'SQLOLEDB', '192.168.50.1'
exec sp_addlinkedsrvlogin 'Server1', 'false', NULL, 'sa', 'mamahao' |
- 给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 |
- 测试,给TableA 插入数据
INSERT INTO tableA VALUES (1,1) |
但是1号服务器报错
分析
-
起初以为是MSDTC 没有启动,但都检查服务器启动成功。更重要的是,在A库中执行下面的语句
UPDATE SERVER1.B.dbo.TABLEB SET STATE=100 WHERE MAPID = 2
是没有问题,可见分布式服务都正常。
-
打上断点,跟踪发现,进入了触发器。但是执行Update语句就失败。
-
反复测试之后,发现
只要带上了 BEGIN TRAN …COMMIT 就报错
-
使用 分布式事务
也不行。
-
感觉只有隐式事务的时候才成功。想到这个2个同步不一定要严格的事务关系。并且
A服务器的INSERT 一定要成功,B服务器的更新偶尔失败没关系。
解决
-
既然只有隐式的事务可以成功。那么我先确保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 |
- 测试验证
INSERT INTO tableA VALUES (1, 1)
PRINT '当前事物' + CONVERT(VARCHAR, @@TRANCOUNT) |
发现执行成功。
而且@@Trancout 在这这个回话中是0值。说明事务都提交了。
反思
- 虽然数据是更新成功达到目的,但是为什么这样写触发器不会错,事务可以提交? 我做了另外一个试验,总结完成再记录一篇随笔。
- 有没有其他的办法,可以满足分布式事务提交? 有空要仔细研究研究。
- 这种触发器的方案,看起来是解决了问题,但是以后维护会不会不记得有触发器这个东西?