SQL Server跨库（含链接服务器）触发器复制数据时遇'Transaction context in use by another session'错误的排查与解决

这个错误我之前处理过类似的场景，核心问题出在触发器递归触发和分布式事务上下文冲突上，咱们一步步拆解：

错误原因 #

1. 递归/嵌套触发循环
   你在A库的触发器会往B和链接服务器的C插入数据，但如果B/C库上也存在同名的Insertdata触发器，那么当数据插入B时，B的触发器会被激活，尝试再次同步数据回A和C——这就形成了循环触发。而跨链接服务器的操作会自动升级为分布式事务，嵌套的事务上下文会互相抢占资源，最终抛出Transaction context in use by another session错误。

2. 游标逻辑包含当前数据库
   你的游标查询里包含了当前触发的数据库（比如A），触发器会尝试往A自己的data表插入数据，这又会再次触发A的触发器，进一步加剧了递归问题，哪怕你用DB_NAME()做了判断，也架不住嵌套事务的上下文冲突。

3. 批量插入场景未处理
   你的代码里用select inserted.id from inserted假设每次只有一条记录插入，但如果是批量插入，这个语句会直接报错，同时拼接字符串的方式也存在SQL注入风险。

解决办法 #

针对这些问题，我给你几个具体的修复步骤：

1. 阻止嵌套/递归触发 #

在触发器开头加入嵌套级别判断，当触发器是被其他触发器调用触发时，直接退出执行：

IF @@NESTLEVEL > 1
    RETURN;

@@NESTLEVEL会返回当前触发器的嵌套调用层数，第一层触发时是1，嵌套触发时会大于1，这样就能避免循环触发。

2. 排除当前数据库同步 #

修改游标查询，过滤掉当前操作的数据库，不要把数据同步回触发源库，从根源上避免递归：

DECLARE @CurrentDB NVARCHAR(128) = DB_NAME();

DECLARE db_cursor CURSOR LOCAL FOR
SELECT t.name, t.islocal 
FROM (
    SELECT name, '1' AS islocal 
    FROM master.sys.databases 
    WHERE name IN ('A','B','C') 
      AND name <> @CurrentDB -- 排除当前库
    UNION ALL
    SELECT name, '0' AS islocal 
    FROM LinkedServer.master.sys.databases 
    WHERE name IN ('A','B','C') 
      AND name <> @CurrentDB -- 排除当前库
) t;

3. 兼容批量插入并优化SQL安全 #

把原来的单条值拼接改为INSERT...SELECT的方式，既支持批量插入，又能避免SQL注入风险：

SET @query = N'INSERT INTO ' + @DB_Name + N'.dbo.data(id, name, surname)
               SELECT i.id, i.name, i.surname
               FROM inserted i
               WHERE NOT EXISTS (
                   SELECT 1 
                   FROM ' + @DB_Name + N'.dbo.data d 
                   WHERE d.id = i.id
               )';
EXEC sp_executesql @query; -- 用sp_executesql替代EXEC，更安全且支持参数化

4. 统一触发器策略 #

建议你只在一个数据库（比如A）创建这个触发器，负责同步数据到B和链接服务器的C，这样就不会出现多库触发器互相触发的问题。如果必须在多库创建触发器，一定要确保每个触发器只同步到其他两个库，并且严格通过@@NESTLEVEL和DB_NAME()做判断。

5. 分布式事务配置（可选） #

如果你的场景必须依赖分布式事务，需要确保SQL Server所在服务器的MSDTC（分布式事务协调器）服务已启用，并且链接服务器的配置允许分布式事务（在链接服务器属性里勾选“启用分布式事务”）。不过尽量避免在触发器里使用分布式事务，因为它会带来性能和可靠性的额外开销。

修改后的完整触发器代码 #

DROP TRIGGER IF EXISTS Insertdata;
USE A;
GO
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
SET QUOTED_IDENTIFIER OFF;
GO
CREATE TRIGGER Insertdata ON DATA WITH ENCRYPTION
FOR INSERT
AS
SET NOCOUNT ON;

-- 阻止嵌套/递归触发
IF @@NESTLEVEL > 1
    RETURN;

DECLARE @CurrentDB NVARCHAR(128) = DB_NAME();
DECLARE @DB_Name VARCHAR(100);
DECLARE @Local VARCHAR(1);
DECLARE @query NVARCHAR(4000); -- 使用NVARCHAR支持Unicode字符

DECLARE db_cursor CURSOR LOCAL FOR
SELECT t.name, t.islocal 
FROM (
    -- 同步到其他本地库
    SELECT name, '1' AS islocal 
    FROM master.sys.databases 
    WHERE name IN ('A','B','C') 
      AND name <> @CurrentDB
    UNION ALL
    -- 同步到链接服务器的其他库
    SELECT name, '0' AS islocal 
    FROM LinkedServer.master.sys.databases 
    WHERE name IN ('A','B','C') 
      AND name <> @CurrentDB
) t;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DB_Name, @Local;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Local = '0'
    BEGIN
        SET @DB_Name = 'LinkedServer.' + @DB_Name;
    END

    -- 批量插入+存在性判断
    SET @query = N'INSERT INTO ' + @DB_Name + N'.dbo.data(id, name, surname)
                   SELECT i.id, i.name, i.surname
                   FROM inserted i
                   WHERE NOT EXISTS (
                       SELECT 1 
                       FROM ' + @DB_Name + N'.dbo.data d 
                       WHERE d.id = i.id
                   )';
    EXEC sp_executesql @query;

    FETCH NEXT FROM db_cursor INTO @DB_Name, @Local;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
SET ANSI_NULLS OFF;
SET ANSI_WARNINGS OFF;
SET QUOTED_IDENTIFIER OFF;
GO

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