如何在SQL Server触发器中向存储过程传入多条inserted记录？

我想了解如何向SQL Server存储过程传入多条记录。场景是我有一个包含复杂逻辑的触发器，希望将部分逻辑迁移至存储过程中。

简化场景的触发器代码：

CREATE TRIGGER dbo.MyTrigger
 ON dbo.MyTable
 AFTER INSERT
 BEGIN

 -- 省略代码

 UPDATE MyField=1 FROM dbo.MyTable2 T INNER JOIN inserted I ON T.Id = I.RefId

 -- 省略代码

 END

期望实现的效果：

CREATE TRIGGER dbo.MyTrigger
 ON dbo.MyTable
 AFTER INSERT
 BEGIN

 -- 省略代码

 exec dbo.MyStoredProcedure inserted;

 -- 省略代码

 END

设想的存储过程写法：

CREATE PROCEDURE dbo.MyStoredProcedure @inserted ??? AS BEGIN 

UPDATE MyField=1 FROM dbo.MyTable2 T INNER JOIN @inserted I ON T.Id = I.RefId

END

请问这是否可行？@inserted参数应声明为何种类型？还有其他思路吗？

出于性能考虑，我无法使用游标逐条调用存储过程来解决此问题。

1. 使用表值参数（推荐方案） #

完全可行，SQL Server的表值参数就是专门用来传递多条记录的特性，完美匹配你的需求，步骤如下：

第一步：创建自定义表类型

先定义一个和inserted结构匹配的表类型（只需要包含后续操作用到的字段，比如RefId）：

CREATE TYPE dbo.MyTableType AS TABLE
(
    RefId INT -- 字段类型要和MyTable的RefId完全一致，有其他需要的字段也可添加
);

第二步：修改存储过程，使用表值参数

将存储过程的参数声明为刚创建的表类型，注意表值参数默认是只读的：

CREATE PROCEDURE dbo.MyStoredProcedure 
    @inserted dbo.MyTableType READONLY
AS 
BEGIN
    UPDATE T
    SET MyField = 1
    FROM dbo.MyTable2 T
    INNER JOIN @inserted I ON T.Id = I.RefId;
END

第三步：修改触发器，传入表值参数

触发器不能直接传递inserted，需要先将数据存入表变量再传递：

CREATE TRIGGER dbo.MyTrigger
 ON dbo.MyTable
 AFTER INSERT
 BEGIN
    -- 省略代码

    DECLARE @tvp dbo.MyTableType;
    INSERT INTO @tvp (RefId)
    SELECT RefId FROM inserted;

    EXEC dbo.MyStoredProcedure @inserted = @tvp;

    -- 省略代码
 END

2. 其他替代思路 #

临时表方案

如果不想预先创建表类型，可以使用会话级临时表（#TempTable），避免全局临时表的并发问题：

-- 触发器代码
CREATE TRIGGER dbo.MyTrigger
 ON dbo.MyTable
 AFTER INSERT
 BEGIN
    -- 省略代码

    CREATE TABLE #TempInserted (RefId INT);
    INSERT INTO #TempInserted SELECT RefId FROM inserted;

    EXEC dbo.MyStoredProcedure;

    -- 省略代码
 END

-- 存储过程代码
CREATE PROCEDURE dbo.MyStoredProcedure 
AS 
BEGIN
    UPDATE T
    SET MyField = 1
    FROM dbo.MyTable2 T
    INNER JOIN #TempInserted I ON T.Id = I.RefId;
END

注意事项

• 表值参数仅支持SQL Server 2008及以上版本
• 表值参数是只读的，存储过程内无法修改其数据
• 两种方案的性能都远优于游标逐条处理，和直接操作inserted的效率接近

内容的提问来源于stack exchange，提问作者A.C.