在触发器中使用 OUTPUT/INTO 而不是插入触发器会使“插入”无效。桌子

Question

我在使用带有替代插入触发器的表时遇到问题。

我创建的表包含一个标识列。我需要在此表上使用而不是插入触发器。我还需要从触发器中查看新插入的标识的值，这需要在触发器中使用 OUTPUT/INTO。问题是执行 INSERT 的客户端无法看到插入的值。

例如，我创建一个简单的表：

CREATE TABLE [MyTable](
 [MyID] [int] IDENTITY(1,1) NOT NULL,
 [MyBit] [bit] NOT NULL,
 CONSTRAINT [PK_MyTable_MyID] PRIMARY KEY NONCLUSTERED 
(
 [MyID] ASC
))

接下来我创建一个简单的而不是触发器：

create trigger [trMyTableInsert] on [MyTable] instead of insert
as
BEGIN
 DECLARE @InsertedRows table( MyID int,
                              MyBit bit);

 INSERT INTO [MyTable]
      ([MyBit])
   OUTPUT inserted.MyID,
          inserted.MyBit
   INTO @InsertedRows
   SELECT inserted.MyBit
     FROM inserted;

 -- LOGIC NOT SHOWN HERE THAT USES @InsertedRows
END;

最后，我尝试执行插入并检索插入的值：

DECLARE @tbl TABLE (myID INT) 

insert into MyTable 
 (MyBit)
OUTPUT inserted.MyID
  INTO @tbl
 VALUES (1)

 SELECT * from @tbl

问题是我得到的只是零。我可以看到该行已正确插入到表中。我还知道，如果我从触发器中删除输出/输入，这个问题就会消失。

对我做错了什么有什么想法吗？还是我想做的事情不可行？

谢谢。

Answer 1

您尚未指定您的“客户端”是什么，但如果它们是 .Net 或类似应用程序，您只需删除输出的 INTO 部分即可。您的客户端无法看到结果，因为它们没有在结果集中返回。

然后，您的触发器应如下所示：

create trigger [trMyTableInsert] on [MyTable] instead of insert
as
BEGIN

 INSERT INTO [MyTable]
      ([MyBit])
   OUTPUT inserted.MyID,
          inserted.MyBit
   SELECT inserted.MyBit
     FROM inserted;

 -- LOGIC NOT SHOWN HERE THAT USES @InsertedRows
END;

就客户端而言，这会导致插入的行为类似于选择查询 - 您可以迭代结果以获取标识值（或其他特殊值，如时间戳）。

这就是 LinqToSql 使用而不是插入触发器支持标识列的方式。

Answer 2

来自 OUTPUT 子句 (Transact-SQL)

从 OUTPUT 返回的列反映了 INSERT、UPDATE 或 DELETE 语句完成之后、触发器执行之前的数据。

对于 INSTEAD OF 触发器，即使触发器操作的结果没有发生任何修改，也会生成返回的结果，就像实际发生了 INSERT、UPDATE 或 DELETE 一样。

..所以触发器不可能向输出结果添加任何内容（并且在替代的情况下，即使触发器没有插入任何内容，您也可能会得到输出！）

在您的情况下，因为您需要的只是插入的 id您可以使用序列，例如：

create sequence MySequence as int start with 1

create table MyTable (
    MyId int default (next value for MySequence),
    MyBit bit not null)

create trigger TR_MyTable on MyTable instead of insert as
begin
    insert into MyTable select * from inserted
    ...do stuff...
end

declare @tbl table (MyId int)
insert into MyTable output inserted.MyId into @tbl
values (1)

select * from @tbl
> 1, 1

TAKE CARE：您将返回为 MyBit 指定的任何值，即使触发器在插入之前更改它！