Using OUTPUT/INTO within instead of insert trigger invalidates 'inserted' table
- by Dan
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.
