When I inserted on tables that exists instead of triggers on those. @@Identity , IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get last identity of inserted row
With an INSTEAD_OF trigger it means no insert occurred yet. You cannot know the identity since it wasn't yet generated. Sneaking the value from metadata is possible (
INSTEAD_OF triggers are extremely seldom required and a serious code smell. Are you sure you need it? Can't you do the work with a regular AFTER trigger?
|show 7 more comments|
In your instead of trigger, you definitely can get the inserted value... but not until after you've performed the insert.
Now clean up:
As an aside, you should never, ever, ever be using