Re: How to get the ID of the last inserted row

From: Richard Kuhler <noone_at_nowhere.com>
Date: Tue, 30 Sep 2003 17:50:40 GMT
Message-ID: <Qnjeb.15439$T46.12609@twister.socal.rr.com>

Daniel Morgan wrote:

> Richard Kuhler wrote:
> 

>> Florian Marinoiu wrote:
>> <snip>
>>
>>> Is it an equivalent for @@Identity?
>>
>> select mysequence.currval from dual
>>
>> However, I wouldn't recommend you use it primarily because it makes an
>> unsafe assumption about what the last use of the sequence really was
>> (that's true in SQL Server to). This also involves a context switch
>> which will slow the process down. Note: this is a little different
>> than @@identity since it's specific to the sequence you reference
>> (i.e. every sequence selected from has a different currval).
>>
>> Sybrand's suggestion to use the returning clause on the insert is a
>> much better solution if you can use it. Even if you can't, I'd look
>> for a different way to figure out what the last row inserted was.
>>
>> Richard Kuhler
>>

> You are correct aboutthe context switch but what is the unsafe 
> assumption? If you understand what it is returning how can you
> get back anything other than what you want?

I was referring to the possibility that a trigger could select another value from the sequence between the time the value for the insert is selected and your currval select. In that situation you won't get the value you're probably expecting.

Richard Kuhler Received on Tue Sep 30 2003 - 12:50:40 CDT