When I insert into tables using instead of triggers,
SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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?
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