使用AFTER INSERT触发器无法获取FileTable计算值返回0问题求解

根因说明 #

• 通过SMB文件共享向FileTable写入文件时，SQL Server的执行逻辑分为两步：
  1. 客户端发起新建文件请求时，SQL Server立即插入一条空记录，触发AFTER INSERT触发器，此时file_stream字段未填充内容，系统计算字段cached_file_size也为0
  2. 客户端完成全部内容写入、关闭文件句柄后，SQL Server才会更新该记录的file_stream、cached_file_size等元数据，该更新操作不会触发INSERT触发器，因此原有逻辑永远无法拿到正确的文件大小

解决方案 #

推荐改用AFTER UPDATE触发器，仅当cached_file_size更新为有效值时处理记录，同时修改为集合操作适配多文件并发上传场景，示例代码如下：

ALTER TRIGGER [dbo].[FileTable_Update_Trigger] 
ON [dbo].[Files]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- 仅当cached_file_size字段被更新时才执行后续逻辑
    IF NOT UPDATE(cached_file_size)
        RETURN;

    -- 集合式插入，支持批量处理多条记录
    INSERT INTO [FileProperties] (stream_id, [name], filepath, file_type, cached_file_size, DateAdded, UserID)
    SELECT 
        ins.stream_id,
        ins.name,
        ins.file_stream.GetFileNamespacePath(),
        ins.file_type,
        ins.cached_file_size,
        CURRENT_TIMESTAMP,
        1
    FROM inserted ins
    -- 避免重复插入已处理过的记录
    LEFT JOIN [FileProperties] fp ON ins.stream_id = fp.stream_id
    WHERE fp.stream_id IS NULL AND ins.cached_file_size > 0;
END

如果业务场景必须使用INSERT触发器，可以选择轮询方案：定时跑作业筛选cached_file_size > 0且未同步到FileProperties的FileTable记录，批量插入即可。

原有代码的额外缺陷 #

原有触发器采用标量变量赋值的方式，仅能处理单次插入1条文件的场景，如果同时上传多个文件，inserted表存在多条记录时，只会处理最后1条记录，存在数据丢失风险，建议统一使用集合式操作处理inserted表。

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