Re: Getting the identity of the row just inserted

From: Michael Krolewski <donotreply_at_interbulletin.bogus>
Date: Sat, 17 Mar 2001 14:19:45 +0000
Message-ID: <3AB37281.7A9817D5@interbulletin.com>

Kathinka Diehl <kathinka_at_rrr.de> wrote in article <m7i1bt4d2rhfnopm190rrgc3rvdl3k6aeg_at_4ax.com> :
>Einar Magnus Råberg <einarmr_at_tihlde.org> wrote:
>
>> OK, I'm using Oracle 7.
>>
>> Could you give me som examples?
>
>DECLARE
> v_variable number(5)
>BEGIN
> v_variable := your_sequence.next;
>
> insert into your_table values
> (v_variable, ...);
>
> do want you want to do with v_variable;
>END;
>
>Regards, Kathinka
>
>--
>Oracle 7.3.4.5 * MS SQLServer 7 * Access2000 * Windows NT * sigh

In Oracle 8i, you have the 'returning' clause, which allows you to insert the row, allow a trigger to populate the sequence number and return to you the value of the sequence inserted.

The big advantage besides reducing some of the cycle time is that get the current value back. If you look at the sequence number in a separate step, you can run into an error state if someone else also inserts a record at the same time. By using an insert trigger, you remove the need to programmatically remember to set values. In this case, the sequence is the only number that needs to be set. It still is better form/style to move the sequence setting to the trigger.

The above method is fool proof -- You have grabbed your own sequence, then inserted the record. No conflict can occur.

---

Submitted via WebNewsReader of http://www.interbulletin.com Received on Sat Mar 17 2001 - 08:19:45 CST