Re: How to create autonumber field in SQL statement?
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