如何通过服务器1表的after insert触发器实现跨服务器表数据插入及报错处理

报错原因 #

该报错是由于创建触发器时ON子句指定的表名加入了不必要的链接服务器前缀[server1]导致：

• 触发器必须创建在当前连接的服务器（即server1）的目标表上，无需额外添加服务器名前缀
• SQL Server中本地数据库对象的命名最多支持[数据库名].[架构名].[表名]三层结构，添加服务器前缀后变成4层结构，超出了最多2个前缀的限制，因此触发报错。

修正后代码 #

-- 执行前先切换到server1对应的业务数据库
USE [db]
GO

CREATE TRIGGER trigger_name ON [schema].[table1]
FOR INSERT 
AS

INSERT INTO [server_2].[db2].[schema].[table1](
PLANTID
,PLANTNAME
,PLANTLOCATION
,COMPANYNAME
,DISPLAY_STATUS
,VERSION
,DEPARTMENT
,PURPOSE
,PERFORMEDBY
)
SELECT 
PLANTID
,PLANTNAME
,PLANTLOCATION
,COMPANYNAME
,DISPLAY_STATUS
,VERSION
,DEPARTMENT
,PURPOSE
,PERFORMEDBY
FROM INSERTED
ORDER BY plmstrauid
GO

额外注意事项 #

• 执行代码前需确认server1上已经正确配置了指向server2的链接服务器，且链接使用的账号对[server_2].[db2].[schema].[table1]拥有INSERT权限
• 建议开启分布式事务协调器（DTC）服务，避免跨服务器插入失败时出现两端数据不一致的问题
• 若单批次插入数据量较大，触发器方式会显著拉低源表的写入性能，高并发大数据量场景建议改用CDC、SQL Server复制订阅等专用数据同步方案实现跨服务器同步

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