How do I get a non-null SCOPE_IDENTITY when using INSTEAD OF triggers in SQL Server 2008?

Question

Possible Duplicate:
Instead of trigger in SQL Server - looses SCOPE_IDENTITY?

On SQL Server 2008, I have an INSTEAD OF trigger on a view. The SCOPE_IDENTITY() after the trigger is results in null. This causes problems with the libraries we're using. How can I control SCOPE_IDENTITY from within my trigger?

This is absolutely needed by Entity Framework in order to get back the ID value of the last inserted row.

Thanks!

Answer 1

You don't - it's SCOPE_IDENTITY() 'cause it returns the identity within the current scope - the trigger is executed in a different scope, so you can't hope to get your hands on the identity with SCOPE_IDENTITY(). @@IDENTITY is what you're after - if the framework can't cope with this then you're out of luck.