Re: How to create autonumber field in SQL statement?

From: andrija <ar35644_at_fer.hr>
Date: Fri, 30 Nov 2001 11:31:59 +0100
Message-ID: <9u7n71$2fk0$1_at_as201.hinet.hr>


"Rudy Kappert" <rudy_at_kappert.com> wrote in message news:3c075b47.8915950_at_news.workbenelux.nl...
> I have to create an auto number field in SQL for oracle (I bet this
> has been asked many times here, but I can''t find a answer.).. Can you
> give me a simple example how to create a table with an autonumber
> field?
>
> Thanks!
>
> Rudy

This is done using the sequences. You create the sequence, and then you can use in your SQL:

insert into mytable (autofield, x,y) values (mysequence.nextval, 10,20);

Even better way is to create trigger on the table which inserts the next value from the sequence into table, something like this:

CREATE OR REPLACE TRIGGER "MYSCHEMA"."TR_NAME" BEFORE INSERT ON "MYSCHEMA"."MYTABLE" FOR EACH ROW begin

select mysequence.nextval into :new.autofield from dual;

end; Received on Fri Nov 30 2001 - 11:31:59 CET

Original text of this message