SQL INSERT触发器调用存储过程后UPDATE失效、ent_Id为NULL问题求助

问题根因分析 #

• inserted表多值读取错误
  你当前的写法直接通过SELECT src FROM inserted这类语句给变量赋值，仅在单次往[dbo].[TT_Personnel_A_Creer]插入1条数据时才有可能正常工作，若出现批量插入多条数据的场景，变量只会取到inserted表最后一条记录的值，甚至出现赋值混乱，导致后续存储过程调用参数错误、@per_Id取值异常，自然UPDATE操作和ent_Id查询都会失败。
• GO语句不能在触发器内使用
  GO是SQL Server的客户端批处理分隔符，不属于T-SQL语法，触发器是一个完整的T-SQL执行单元，内部不允许出现GO。你单独抽离逻辑加GO可以正常运行，是因为分批次执行时，存储过程的写入操作在第一个批次完成后对第二个批次可见，而触发器属于同一个执行上下文，不存在批次分割的可能。
• 存储过程返回值逻辑异常
  需确认[dbo].[createPersonne]的返回逻辑：只有存储过程通过RETURN @per_Id的方式返回值，才可以用EXEC @per_Id = 存储过程的方式接收；如果per_Id是存储过程的输出参数，你当前的接收方式是错误的，会导致@per_Id为NULL，后续所有依赖@per_Id的操作都会失效。
• 事务可见性问题
  如果存储过程内部使用了独立事务、异步写入或延迟提交逻辑，在触发器的当前事务上下文中，存储过程写入的Personne、Entite记录还未提交，自然查询不到。单独执行加GO的逻辑时，前一个批处理的事务已经提交，后续查询就可以读到数据。

解决方案 #

1. 改写触发器适配批量插入场景 #

触发器必须兼容批量插入的场景，不能默认每次仅插入1条记录，参考改写后的代码：

ALTER TRIGGER [dbo].[bf_insert_tt_personnel_a_creer] ON [dbo].[TT_Personnel_A_Creer]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON; -- 必须添加，避免返回多余行数影响触发语句的执行结果
    -- 声明常量
    DECLARE @usr        VARCHAR(6) = 'DSI';
    DECLARE @adr_id     INT = 2;
    DECLARE @per_Image  VARBINARY(MAX) = 0x0;

    -- 声明循环处理用变量
    DECLARE @src VARCHAR(3), @matricule VARCHAR(8), @nom VARCHAR(50), @nom_matr VARCHAR(50),
            @prenom VARCHAR(50), @date_naissance DATE, @email VARCHAR(200), @sexe INT,
            @per_Id INT, @ent_Id INT;

    -- 用游标遍历inserted所有记录
    DECLARE insert_cursor CURSOR FOR
        SELECT src, matricule, nom, nom_patronymique, prenom, date_naissance, email,
               CASE WHEN sexe = 'M' THEN 1 ELSE 2 END AS sexe
        FROM inserted;
    
    OPEN insert_cursor;
    FETCH NEXT FROM insert_cursor INTO @src, @matricule, @nom, @nom_matr, @prenom, @date_naissance, @email, @sexe;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用存储过程，注意原代码中@per_Nom_Matr传值为@prenom，疑似笔误，可按实际业务修正
        EXEC @per_Id = [dbo].[createPersonne]
                    @per_Nom = @nom,
                    @per_Prenom = @prenom,
                    @per_Nom_Matr = @nom_matr,
                    @per_Url = '',
                    @per_Ddn = @date_naissance,
                    @per_Mob = '',
                    @cvt_Id = @sexe,
                    @catpax_Id = 2,
                    @per_Mail = @email,
                    @usr_Login = @usr,
                    @per_Mail_NPAI = 1,
                    @per_Mail_Desab = 1,
                    @per_Societe = '',
                    @adrId = @adr_id,
                    @macroEntChef = 0,
                    @per_Image = @per_Image;
        
        -- 校验@per_Id合法性后再执行后续操作
        IF @per_Id IS NOT NULL AND @per_Id > 0
        BEGIN
            -- 更新Personne表字段
            UPDATE Personne 
            SET
                per_matricule = CASE @src WHEN 'PE5' THEN @matricule ELSE per_matricule END,
                per_Id_Rh_Externe = CASE @src WHEN 'RHE' THEN @matricule ELSE per_Id_Rh_Externe END
            WHERE per_Id = @per_Id;

            -- 查询ent_Id，添加WITH(NOLOCK)避免事务隔离级别导致读不到未提交数据，若业务禁止脏读可移除
            SELECT @ent_Id = ent_Id FROM Entite WITH (NOLOCK) WHERE per_Id = @per_Id;
            -- 后续需要使用ent_Id的逻辑可在此处添加
        END

        FETCH NEXT FROM insert_cursor INTO @src, @matricule, @nom, @nom_matr, @prenom, @date_naissance, @email, @sexe;
    END

    CLOSE insert_cursor;
    DEALLOCATE insert_cursor;
END;

2. 校验存储过程返回逻辑 #

如果存储过程是通过输出参数返回per_Id，将调用方式调整为如下格式（参数名按存储过程实际定义修改）：

DECLARE @out_per_Id INT;
EXEC [dbo].[createPersonne]
    -- 其他参数保持不变
    @per_Id = @out_per_Id OUTPUT;
SET @per_Id = @out_per_Id;

同时确认存储过程内部没有使用异步写入、独立事务延迟提交的逻辑，若确实需要使用独立事务，确保存储过程执行完成前所有写入操作已经提交。

3. 排查关联触发器 #

检查[dbo].[TT_Personnel_A_Creer]、Personne、Entite表是否有其他INSTEAD OF类型的触发器，拦截了你的写入操作导致更新失效。

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