Hi all. Just a quick question for you guys...I did a search on Select @@identity and I'm not sure I understand it. The program I'm migrating over (from sql server to oracle) has "Select @@identity" at the end of some insert statements. I'm just tryin to figure out what this does and what the Oracle equivlant would be? i have read posts(or in this case a blog) like this [link]http://weblog.anthonyeden.com/archives/000054.html[/link] that states it gives you the value of the PK.
I've also read this [link]http://www.kamath.com/tutorials/tut007_identity.asp[/link] stating select @@identity is handy to use as a "connection specific global variable." This all makes sense, however in the code I am examining select @@identity is not assigned to any variable. Basically, my question is if select @@ identity is not assigned to a variable in your code, what use does putting it at the end of a select statement serve? thank you
The 'Select @@Identity' statement after Insert is simply to find out what the the last(max) identity number after the Insert. Identity in Oracle it's the Sequence number. It's the same thing as finding out the last sequence number that was inserted.
You should probably be using SCOPE_IDENTITY() instead of @@IDENTITY. If your insert triggers inserts into other tables, @@IDENTITY will contain the ID for a table inserted into by the trigger, not the table you inserted into. SCOPE_IDENTITY() will be the ID of the row you inserted.