SELECT SCOPE_IDENTITY is always resulting id=1, what i have done wrong? how to get the currentid of the inserted element.
+1
A:
Are you doing it within the same trasaction? Is the identity actually being incremented in the Database?
Climber104
2010-10-08 14:22:58
yes,the identity is actually being incremented in the Database
user
2010-10-08 14:28:51
A:
A good article on the different methods to retrieve the last inserted.
Spilarix
2010-10-08 14:24:46
+1
A:
SCOPE_IDENTITY does indeed return the identity column of the last inserted row in the scope you are in.
It can be messed up by a few things, within the same scope. In that case IDENT_CURRENT('myTable') will be better.
If not you'll want to post more code. SCOPE_IDENTITY does work as you think, so the problem is likely elsewhere.
Jon Hanna
2010-10-08 14:24:58
How can `SCOPE_IDENTITY()` be messed up by triggers? I thought that was its advantage over `@@IDENTITY`.
Ian Henry
2010-10-08 14:27:47
It shouldn't, but can in some cases. http://cgaskell.wordpress.com/2006/11/15/using-identity-vs-scope_identity-and-further-implications-with-triggers/
Jon Hanna
2010-10-08 14:30:46
Have you verified this for yourself? The comment by angeliii seems to imply some user error on the part of the blog author.
Martin Smith
2010-10-08 14:39:55