SQL Server事务复制场景下Update触发器Deleted表为空问题排查

问题场景 #

数据库A的表t通过事务复制同步到数据库B的同名表t，在B.t上创建了如下FOR INSERT, UPDATE触发器：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER TRIGGER [dbo].[synchro_trigger]
ON [dbo].[t]
FOR INSERT, UPDATE
AS
BEGIN
    -- update case
    IF EXISTS ( SELECT 0 FROM Deleted )
    ...
END

• 执行单字段更新语句update A.t set field1='1' where id=?时，数据同步正常，触发器中SELECT 0 FROM Deleted能查询到数据，逻辑正常执行
• 执行全字段更新语句update A.t set field1='1', field2='1', ... where id=?（部分字段值未变更）时，数据同步成功，但触发器中Deleted表为空，导致UPDATE分支逻辑未执行

原因分析 #

这是SQL Server事务复制的行级跟踪优化机制导致的：
事务复制默认仅跟踪并同步实际发生值变更的字段。当执行全字段更新但部分字段值与原数据一致时，复制代理在同步到B库时，不会生成完整的旧行数据到Deleted表——因为复制操作仅传递了变更字段的信息，而非整行的新旧值对比。此时触发器虽被触发，但Deleted表没有数据，因为复制的更新操作被优化为仅同步变更字段，未保留完整旧行记录。

解决方案 #

方案1：修改复制同步规则，强制复制所有列 #

通过配置发布属性，让复制同步整行的新旧值，而非仅变更字段：

• 打开SQL Server Management Studio，找到对应的发布
• 右键选择「属性」，进入「文章」页签，选中表t
• 点击「属性」，在「快照」选项卡中勾选「复制所有列」，保存设置
• 重新生成快照并初始化订阅，确保新规则生效

方案2：调整触发器判断逻辑，不依赖Deleted表 #

放弃用EXISTS (SELECT 0 FROM Deleted)判断UPDATE操作，改为结合INSERTED表与原表主键关联的方式：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER TRIGGER [dbo].[synchro_trigger]
ON [dbo].[t]
FOR INSERT, UPDATE
AS
BEGIN
    -- 判断UPDATE：INSERTED存在且原表有对应主键记录（排除INSERT场景）
    IF EXISTS(SELECT 1 FROM INSERTED i 
              INNER JOIN dbo.t ON i.id = t.id
              WHERE NOT EXISTS(SELECT i.* EXCEPT SELECT t.*))
    BEGIN
        -- 原UPDATE分支逻辑
    END
    ELSE
    BEGIN
        -- INSERT分支逻辑
    END
END

或者更简洁的主键判断（适用于主键唯一的场景）：

IF EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM dbo.t WHERE id IN (SELECT id FROM INSERTED))

方案3：检查复制代理优化设置 #

确认订阅的同步类型为「完整」，而非仅同步变更列：

• 找到对应订阅，右键选择「属性」
• 在「同步」相关选项中，确保未启用「仅同步变更字段」的优化项

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