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

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 01 Oct 2003 19:27:36 +0100
Message-ID: <j96mnv05o38ro5i56digi930487uv0g2v9@4ax.com>

On Tue, 30 Sep 2003 22:01:43 -0700, Daniel Morgan <damorgan_at_x.washington.edu> 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.
>>>>
>>> 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.
>
>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?

What goes wrong is that it no longer answers the original question; how to get the primary key value you've just assigned by way of a sequence. If recursive SQL (i.e. triggers) use the same sequence again, the sequence gets incremented further, so currval at the end of the statement is not the same as currval when it was used to create the row.

SQL> create table t (id number not null primary key);

Table created.

SQL> create sequence s;

Sequence created.

SQL> create or replace trigger trig
  2 before insert on t for each row
  3 declare
  4 v t.id%type;
  5 begin
  6 select s.nextval into v from dual;   7 end;
  8 /

Trigger created.

SQL> declare
2 v_id t.id%type;
3 begin

  4      insert into t (id) values (s.nextval)
  5          returning id into v_id; 
  6      dbms_output.put_line('returned: ' || v_id);
  7      
  8      select s.currval into v_id from dual;
  9      dbms_output.put_line('s.currval:' || v_id);

10 end;
11 /
returned: 1
s.currval:2

PL/SQL procedure successfully completed.

SQL> select * from t;

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Received on Wed Oct 01 2003 - 13:27:36 CDT