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

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

user44826
1#
user44826 Published in September 20, 2018, 4:47 pm

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.

• Can you post your table definition and an example of what you mean? I can't follow this. – Sean Lange Feb 13 at 22:52
• @SeanLange - I appreciate your interest. But the more I think about it, I don't think what I want is possible. (To update the results of the OUTPUT clause from inside the trigger.) I am realizing that I would be better off just having the application generate the Key using the sequence and then passing it in as part of the insert. – Vaccano Feb 13 at 23:01
• Downvote reason: "how can I modify my INSTEAD OF INSERT trigger" - To answer that we will need to see the trigger you have now... You have way too much reputation to not know we need a Minimal, Complete, and Verifiable example... – Zohar Peled Feb 14 at 7:23
StackExchange.ready(function(){\$.get('/posts/48776837/ivc/af20');}); StackExchange.ready(function () { StackExchange.responsiveness.addSwitcher(); }) (function(i, s, o, g, r, a, m) { i['GoogleAnalyticsObject'] = r; i[r] = i[r] || function() { (i[r].q = i[r].q || []).push(arguments) }, i[r].l = 1 * new Date(); a = s.createElement(o), m = s.getElementsByTagName(o)[0]; a.async = 1; a.src = g; m.parentNode.insertBefore(a, m); })(window, document, 'script', 'https://www.google-analytics.com/analytics.js', 'ga'); StackExchange.ready(function () { StackExchange.ga.init({ sendTitles: true, tracker: window.ga, trackingCodes: [ 'UA-108242619-1' ] }); StackExchange.ga.setDimension('dimension2', '|sql-server|tsql|sql-server-2012|triggers|'); StackExchange.ga.setDimension('dimension3', 'Questions/Show'); StackExchange.ga.trackPageView(); }); /**/ var _qevents = _qevents || [], _comscore = _comscore || []; (function() { var ssl = 'https:' == document.location.protocol, s = document.getElementsByTagName('script')[0], qc = document.createElement('script'); qc.async = true; qc.src = (ssl ? 'https://secure' : 'http://edge') + '.quantserve.com/quant.js'; s.parentNode.insertBefore(qc, s); _qevents.push({ qacct: "p-c1rF4kxgLUzNc" }); /**/ var sc = document.createElement('script'); sc.async = true; sc.src = (ssl ? 'https://sb' : 'http://b') + '.scorecardresearch.com/beacon.js'; s.parentNode.insertBefore(sc, s); _comscore.push({ c1: "2", c2: "17440561" }); })();
 You need to login account before you can post.
Processed in 0.361483 second(s) , Gzip On .