Using OUTPUT/INTO within instead of insert trigger invalidates 'inserted' table

I have a problem using a table with an in开发者_开发百科stead of insert trigger.

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

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?

Thanks.

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;

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).

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

from OUTPUT Clause (Transact-SQL)

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation.

..so it's impossible for the trigger to add anything to the output results (and in the case of instead-of you may get output even if the trigger didn't insert anything!)

in your case since all you need is the inserted id you could use a sequence, eg:

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: you'll get back whatever value you specified for MyBit even if the trigger changes it before inserting!