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

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.

share|improve this question
  • 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

active oldest votes

Your Answer

StackExchange.ifUsing("editor", function () { StackExchange.using("externalEditor", function () { StackExchange.using("snippets", function () { StackExchange.snippets.init(); }); }); }, "code-snippets"); StackExchange.ready(function() { var channelOptions = { tags: "".split(" "), id: "1" }; initTagRenderer("".split(" "), "".split(" "), channelOptions); StackExchange.using("externalEditor", function() { // Have to fire editor after snippets, if snippets enabled if (StackExchange.settings.snippets.snippetsEnabled) { StackExchange.using("snippets", function() { createEditor(); }); } else { createEditor(); } }); function createEditor() { StackExchange.prepareEditor({ heartbeatType: 'answer', convertImagesToLinks: true, noModals: false, showLowRepImageUploadWarning: true, reputationToPostImages: 10, bindNavPrevention: true, postfix: "", onDemand: true, discardSelector: ".discard-answer" ,immediatelyShowMarkdownHelp:true }); } });
StackExchange.ready( function () { StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f48776837%2fupdate-output-results-when-used-on-a-view-with-an-instead-of-insert-trigger%23new-answer', 'question_page'); } );

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged sql-server tsql sql-server-2012 triggers or ask your own question.

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.

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

© 2016 Powered by mzan.com design MATCHINFO