I have a view that combines two tables. When I do an insert into the view, the trigger gets the primary key/clustered index value using
NEXT VALUE FOR MySequence.
The trigger then inserts into the two tables using the generated key and the values in the
Inserted table. It all works just fine.
The problem comes in when an application that uses the view does an insert with an output clause. Something like this:
DECLARE @InsertedIds table (MyViewId int, ValueForTheFirstTable int)
INSERT INTO MyView (ValueForTheFirstTable, ValueForTheSecondTable )
OUTPUT Inserted.MyViewId, Inserted.ValueForTheFirstTable into @InsertedIds
VALUES ('FirstTableValue', 'SecondTableValue')
ValueForTheFirstTable does return the correct value. But the MyViewId (the key) is null. (And I really expect it to be anything else because it does not know the key that I generated.)
My question is, how can I modify my
INSTEAD OF INSERT trigger to make it so that the
OUTPUT clause will contain my generated Sequence number value?
I know value, I just need a way to feed it back to SQL Server so it can provide it to any OUTPUT clauses.