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

Richard Kuhler wrote:

> 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
>

Of course you will. You always get back the last value from your own session. Oracle is not a single-user database.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)