为何受影响行数与预期不符？SQL触发器场景行数异常问询

这是因为你的触发器执行流程中产生了三次独立的行影响计数输出，具体流程如下：

1. 你通过sp_executesql执行的外层INSERT INTO MyTable语句，触发了INSTEAD OF INSERT类型的MyTrigger触发器——SQL Server会跳过原语句的直接插入操作，转而执行触发器内部的逻辑。
2. MyTrigger内部执行的INSERT INTO MyTable操作，又触发了FOR INSERT类型的MyTrigger_2触发器（这类触发器会在数据插入完成后执行）。
3. MyTrigger_2里的INSERT INTO MyTable2操作完成后，输出第一个(1 row affected)。
4. MyTrigger内部的插入操作完成后，输出第二个(1 row affected)。
5. 外层的INSERT语句最终会将MyTrigger内部插入的行数作为自身的执行结果返回，输出第三个(1 row affected)。

简单来说，三个行影响消息分别对应：

• MyTable2表的插入操作
• MyTrigger中对MyTable的插入操作
• 外层INSERT语句返回的计数（实际是触发器内插入的行数）

你的代码定义： #

MyTable表

CREATE TABLE MyTable (
  Column1 VARCHAR(50),
  Column2 INT,
  Column3 INT
);

MyTrigger触发器

ALTER TRIGGER [MyTrigger]
ON [MyTable]
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO [MyTable]
    (Column1, Column2, Column3)
    SELECT
        i.Column1,
        i.Column2,
        CASE
            WHEN i.Column3 < 100 THEN 20
            ELSE i.Column3
        END
    FROM inserted i;
END;

MyTable2表

CREATE TABLE MyTable2 (
  Column1 VARCHAR(50),
  Column2 INT,
  Column3 INT,
  ACTION VARCHAR(20)
);

MyTrigger_2触发器

ALTER TRIGGER [MyTrigger_2]
ON [MyTable]
FOR INSERT
AS
BEGIN
    DECLARE @myAction_Inserted NVARCHAR(50)
    SET     @myAction_Inserted ='INSERTED'
        
    INSERT INTO MyTable2 
    (Column1, Column2, Column3,ACTION)
    SELECT
        i.Column1,
        i.Column2,
        i.Column2,
        @myAction_Inserted 
    FROM inserted i;
END;

执行语句

DECLARE @sql NVARCHAR(MAX) = '';

SET @sql = 'INSERT INTO MyTable (Column1, Column2, Column3) 
VALUES (''Test1'', 10, 105)';

EXEC sp_executesql @sql;

解决方案（可选） #

如果不想看到触发器内部的行影响消息，可以在每个触发器的BEGIN语句后添加SET NOCOUNT ON;，这样触发器内的DML操作不会输出单独的行影响计数，只会保留外层语句的计数：

比如修改MyTrigger：

ALTER TRIGGER [MyTrigger]
ON [MyTable]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON; -- 关闭触发器内的行影响输出
    INSERT INTO [MyTable]
    (Column1, Column2, Column3)
    SELECT
        i.Column1,
        i.Column2,
        CASE
            WHEN i.Column3 < 100 THEN 20
            ELSE i.Column3
        END
    FROM inserted i;
END;

同样给MyTrigger_2加上SET NOCOUNT ON;后，执行外层语句就只会看到1行受影响的消息，符合你最初的预期。

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