Re: How get SEQUENCE

In article <32DFE032.6982_at_netmail.mnet.uswest.com>, John Verbil <jverbil_at_netmail.mnet.uswest.com> wrote:
>Paolo Dall'Olio wrote:
>>
>> I'd like some suggestions how to get a SEQUENCE (nextval o currval) ...
>>
>> sometimes it's usefull to get it before the INSERT or UPDATE statement
>> ... at the moment I get it from a one-record table (eg DUMMY) with the
>> following:
>> SELECT seq_name.NEXTVAL FROM DUMMY;
>>
>> but I do not like it to much
>
>You're actually pretty close to the "standard" way to do it, which is in
>fact a select from a one-row table, but most of us use a table called
>"DUAL" that Oracle supplies specifically for reasons such as this:
>
>SELECT seq_name.NEXTVAL FROM dual;
>
>There's no reason to create a dummy table of your own.
>
>--
>
>========================================================================
>John Verbil | e-mail: jverbil_at_uswest.com
>U S WEST Information Technologies | voice: (303) 896-0916
>Member of the Technical Staff | fax: (303) 896-7825
>Technical Lead, Forecaster | 1005 17th Street, Room 1340
> | Denver, Colorado 80202
>========================================================================

You may also use trigger to do the job. It works beautifully. For example:

CREATE OR REPLACE TRIGGER triggername_table BEFORE INSERT ON tablename
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT seq_name.NEXTVAL INTO :NEW.ID FROM DUAL; END;
/ Received on Wed Jan 29 1997 - 00:00:00 CST