SQL Server：SQL CLR存储过程能否捕获外部触发的事务完成事件？

直接给结论：你尝试的这种方式不可行，原因和可行的替代方案我给你详细说明：

为什么原方案不生效？ #

当你的CLR存储过程MySProc执行完毕并返回T-SQL上下文后，CLR运行时会清理该存储过程执行期间创建的所有托管对象——包括你注册的TransactionCompleted事件处理程序。SQL Server的CLR集成机制中，每个存储过程调用的托管上下文是临时的，对象生命周期严格限定在存储过程的执行窗口内。

当你在外部执行rollback tran时，CLR里的事件处理程序早就被垃圾回收机制清理了，自然不会被触发。

可行的替代方案 #

方案1：在T-SQL调用层显式通知CLR处理事务结果 #

如果你的调用场景可控，可以在T-SQL中捕获事务的最终状态（提交/回滚），然后主动调用CLR存储过程处理结果：

BEGIN TRAN
EXEC [dbo].[MySProc]

BEGIN TRY
    COMMIT TRAN
    -- 通知CLR事务已提交
    EXEC [dbo].[MySProc_TransactionCompleted] @IsCommitted = 1
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    -- 通知CLR事务已回滚
    EXEC [dbo].[MySProc_TransactionCompleted] @IsCommitted = 0
END CATCH

对应的CLR存储过程可以写成：

[SqlProcedure]
public static void MySProc_TransactionCompleted(SqlBoolean isCommitted)
{
    // 在这里处理事务完成后的逻辑
    if (isCommitted.IsTrue)
    {
        // 提交后的自定义处理
    }
    else
    {
        // 回滚后的自定义处理
    }
}

这种方式简单直接，但需要调用方配合编写T-SQL逻辑。

方案2：使用SQL Server扩展事件（Extended Events）跟踪事务完成 #

如果需要无侵入式地跟踪所有事务的完成状态，扩展事件是更合适的选择：

1. 创建一个扩展事件会话，跟踪transaction_end事件——该事件会在事务提交或回滚时触发，包含事务ID、结果（提交/中止）等关键信息。
2. 可以将事件输出定向到事件文件或者内存缓冲区，然后编写CLR代码定期读取这些事件数据，处理对应的事务完成逻辑。

示例创建扩展事件会话的T-SQL：

CREATE EVENT SESSION [TrackTransactionEnd] ON SERVER 
ADD EVENT sqlserver.transaction_end(
    ACTION(sqlserver.database_id, sqlserver.transaction_id))
ADD TARGET package0.event_file(SET filename=N'TransactionEndEvents.xel')
WITH (STARTUP_STATE=ON)

你可以编写CLR代码读取.xel文件中的事件数据，解析事务的完成状态并执行自定义逻辑。

方案3：使用系统动态管理视图（DMV）轮询事务状态 #

如果对实时性要求不高，可以在CLR中记录当前事务的ID，然后用后台线程或SQL代理作业定期查询sys.dm_tran_active_transactions等DMV，判断事务是否已完成，并获取结果：

在MySProc中记录事务ID：

[SqlProcedure]
public static void MySProc()
{
    var tranId = Transaction.Current.TransactionInformation.LocalIdentifier;
    // 将tranId存储到自定义跟踪表中
    using (var conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        var cmd = new SqlCommand("INSERT INTO dbo.TrackedTransactions (TransactionId, CreatedTime) VALUES (@tranId, GETDATE())", conn);
        cmd.Parameters.AddWithValue("@tranId", tranId);
        cmd.ExecuteNonQuery();
    }
}

然后编写一个CLR存储过程或者SQL代理作业，定期查询处理：

SELECT 
    tt.TransactionId,
    CASE WHEN tat.transaction_id IS NULL THEN 'Completed' ELSE 'Active' END AS TransactionStatus
FROM dbo.TrackedTransactions tt
LEFT JOIN sys.dm_tran_active_transactions tat ON tt.TransactionId = CAST(tat.transaction_id AS VARCHAR(50))
WHERE tt.IsProcessed = 0

这种方式有一定延迟，但不需要修改调用方的T-SQL逻辑。

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