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

Posted by Dan on Stack Overflow See other posts from Stack Overflow or by Dan
Published on 2010-03-20T05:12:09Z Indexed on 2010/03/20 5:21 UTC
Read the original article Hit count: 311

Filed under:
|
|
|
|

I have a problem using a table with an instead 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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about trigger