在内部使用OUTPUT / INTO而不是插入触发器会使“插入”表无效 [英] Using OUTPUT/INTO within instead of insert trigger invalidates 'inserted' table

问题描述

我在使用带有触发器而不是插入触发器的表时遇到问题。

I have a problem using a table with an instead of insert trigger.

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

The table I created contains an identity column. I need to use an instead of insert trigger on this table. I also need to see the value of the newly inserted identity from within my trigger which requires the use of OUTPUT/INTO within the trigger. The problem is then that clients that perform INSERTs cannot see the inserted values.

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

For example, I create a simple table:

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

下一步，我创建一个简单的代替触发器：

Next I create a simple instead of trigger:

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;

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

Lastly, I attempt to perform an insert and retrieve the inserted values:

DECLARE @tbl TABLE (myID INT) 

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

 SELECT * from @tbl

问题是我得到的全部为零。我可以看到该行已正确插入表中。我也知道，如果我从触发器中删除OUTPUT / INTO，这个问题就消失了。

The issue is all I ever get back is zero. I can see the row was correctly inserted into the table. I also know that if I remove the OUTPUT/INTO from within the trigger this problem goes away.

对我在做什么错有任何想法吗？还是我想做的事不可行？

Any thoughts as to what I'm doing wrong? Or is how I want to do things not feasible?

谢谢。

推荐答案

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

You haven't specified what your 'clients' are, but if they are a .Net or similar app, you simply need to drop the INTO portion of your output. Your clients can't see the results because they are not being returned in the result set.

您的触发器应如下所示：

Your trigger then should look like this:

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;

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

This causes inserts to behave like a select query as far as the client is concerned - you can iterate over the results to get the identity value (or other special values like Timestamp).

这是LinqToSql通过而不是插入触发器支持标识列的方式。

This is how LinqToSql supports identity columns with instead of insert triggers.

这篇关于在内部使用OUTPUT / INTO而不是插入触发器会使“插入”表无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！