0

I'm inserting values into a view which has an INSTEAD OF trigger to insert/update various other tables. The final line of the trigger is 

SELECT SCOPE_IDENTITY()

which means that when I select SCOPE_IDENTITY() myself after the insert so I can use the just inserted identity value I get a NULL value.

If I understand it correctly, this is because there is no identity value associated with the SCOPE_IDENTITY() that was just called.

Is there a way to get the SELECT value from the trigger? Changing the trigger is not really an option due to other dependencies on its current behaviour.

I could do it with a temp table and executing the insert as dynamic SQL but that seems like a horribly hacky way to do it:

DECLARE @scopeid INT
CREATE TABLE #temp(id int)
INSERT INTO #temp EXEC ('INSERT INTO myView(somecolumns) VALUES (somevalues)')
SELECT @scopeid = TOP 1 id FROM #temp
DROP TABLE #temp

Surely there's a neater way than this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jaloopa
  • 722
  • 1
  • 6
  • 21
  • 1
    Check this [link](http://stackoverflow.com/questions/908257/instead-of-trigger-in-sql-server-loses-scope-identity) – jambis Nov 07 '13 at 19:27
  • That link suggests using @@IDENTITY, but wouldn't that cause a race condition if something else happens to be inserting to a different table at the same time? – Jaloopa Nov 08 '13 at 11:32
  • There's another solution under the link I gave to use OUTPUT clause. Have you tried that? – jambis Nov 15 '13 at 08:37

0 Answers0