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

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 01 Oct 2003 18:22:41 GMT
Message-ID: <RXEeb.35634$5z.13632@twister.socal.rr.com>

Daniel Morgan wrote:
> Richard Kuhler wrote:
>

>> Daniel Morgan wrote:
>> <snip>
>>
>>>> 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.
>>
>>
>>
>> I guess I should have been clearer there.  I'm referring to a trigger 
>> firing IN THE SAME SESSION that may use the sequence a second time 
>> within the duration of what the application perceives to be a single 
>> insert.
>>
>>
>> Richard Kuhler

>
> Then it will use CURRVAL. I still don't see any issue. The behavior of a
> sequence, of CURRVAL and NEXTVAL are 100%
> documented and predictable. Have been for years. Read the documentation
> and they do exactly what they are supposed to
> do. So what can go wrong short of making a call to DBMS_RANDOM by mistake?

I'm not suggesting that CURRVAL doesn't do what it should only that the developer is making an assumption that it's is the behavior they want. Ok, let me explain this step by step and see if I can get you to see the problem ...

A table is born (let's call it T)
A sequence is born (let's call it S)
A trigger is born (let's call it TRIG1) to populate the ID column of table T using S.NEXTVAL.
A routine that inserts data into table T is born (let's call it P)
Within procedure P, a line of code to retrieve CURRVAL is added on the line immediately after the insert.

... all is well, there is order in the universe ...

6. A new additional trigger is born (let's call it TRIG2) that inserts an additional row into the table T for each insert statement (no it's not mutating at the statement level) and hence needs to get another value from sequence S using S.NEXTVAL.

... the CURRVAL usage in procedure P is now broken for a logical standpoint. Instead of getting the value it's expecting (the ID of the row that it inserted on the line immediately preceding it) it now gets the ID of the additional row inserted by trigger TRIG2.

Richard Kuhler Received on Wed Oct 01 2003 - 13:22:41 CDT