Home Update OUTPUT results when used on a view with an INSTEAD OF INSERT trigger
Reply: 0

Update OUTPUT results when used on a view with an INSTEAD OF INSERT trigger

Vaccano
1#
Vaccano Published in 2018-02-13 22:37:07Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299599 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO