使用SQL Server链接服务器时更新触发器失效问题求助

1. 先修正触发器的逻辑错误 #

你的触发器中直接查询myLocalDB.dbo.localTable会获取整个表的lastTime（如果表有多行，返回结果随机），不符合触发器的设计意图——应该获取本次更新操作中被修改记录的lastTime，正确做法是使用触发器内置的inserted表：

CREATE TRIGGER [dbo].[update_trg]
ON [myLocalDB].[dbo].[localTable]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- 处理单行/多行更新，关联inserted表获取最新的lastTime
    UPDATE remote
    SET remote.lastTime = ins.lastTime
    FROM [192.168.3.102].[myRemoteDB].[dbo].[remoteTable] remote
    CROSS JOIN inserted ins
    WHERE remote.id = 1;
END

如果localTable和remoteTable通过id关联，需把CROSS JOIN改为JOIN并匹配id，避免错误更新。

2. 排查分布式事务（DTC）的配置问题 #

即使启用了DTC，以下配置缺失也会导致报错或卡住：

• 两台机器的DTC安全设置：
  打开「组件服务」→「计算机」→「我的电脑」→「分布式事务协调器」→「本地DTC」→右键属性→「安全」标签：
  • 勾选「允许网络DTC访问」
  • 勾选「允许远程客户端」「允许远程管理」
  • 勾选「允许入站」「允许出站」
  • 安全设置选择「不要求验证」（测试环境优先，生产环境可按需调整为「要求相互验证」）
• 防火墙放行DTC端口：
  • 必须开放TCP 135端口（DTC的RPC端口）
  • 若不想用动态端口，可在DTC属性的「端口」标签设置固定端口，再在防火墙开放该端口
• 链接服务器的RPC设置：
  在SSMS中找到链接服务器→右键属性→「服务器选项」：
  • 确保「RPC」和「RPC Out」都设为「True」
  • 尝试将「Remote Proc Transaction Promotion」设为「False」，避免自动将本地事务提升为分布式事务（部分场景可绕过DTC限制）

3. 绕过分布式事务的替代方案 #

如果DTC配置始终无法解决问题，可尝试避免触发分布式事务，改用远程执行的方式：

方法1：参数化远程存储过程调用 #

CREATE TRIGGER [dbo].[update_trg]
ON [myLocalDB].[dbo].[localTable]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- 假设单行更新，多行需循环处理
    DECLARE @lastTime DATETIME;
    SELECT @lastTime = lastTime FROM inserted;

    -- 调用远程服务器的sp_executesql执行参数化更新
    EXEC [192.168.3.102].[myRemoteDB].[dbo].sp_executesql 
        N'UPDATE [myRemoteDB].[dbo].[remoteTable] SET lastTime = @lastTime WHERE id = 1',
        N'@lastTime DATETIME',
        @lastTime = @lastTime;
END

方法2：OPENQUERY执行远程语句 #

CREATE TRIGGER [dbo].[update_trg]
ON [myLocalDB].[dbo].[localTable]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @lastTime DATETIME, @sql NVARCHAR(MAX);
    SELECT @lastTime = lastTime FROM inserted;

    -- 构造参数化远程更新语句（避免SQL注入）
    SET @sql = N'UPDATE [myRemoteDB].[dbo].[remoteTable] SET lastTime = ''' + 
               CONVERT(VARCHAR(20), @lastTime, 120) + ''' WHERE id = 1';
    
    -- 通过OPENQUERY在远程服务器执行
    EXEC OPENQUERY([192.168.3.102], @sql);
END

4. 测试DTC连通性 #

使用微软官方的dtcping工具测试两台服务器的DTC通信：

• 在两台机器上分别运行dtcping.exe
• 输入对方机器的主机名或IP，查看是否能成功建立DTC连接，根据报错信息定位问题（如权限、防火墙、DTC配置）

内容的提问来源于stack exchange，提问作者Farhad Aliyev