Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to increment ID with every insert

Re: How to increment ID with every insert

From: Rüdiger J. Schulz <johannes.schulz_at_web.de>
Date: 08 Oct 2001 16:05:57 +0200
Message-ID: <3bc1b2cb$1@netnews.web.de>


you have to build a sequence:

now, there are 2 ways:

P 1.: with a trigger

create or replace trigger trg_test
before insert on newtable
for each row
begin

   select test_id.nextval into :new.id from dual; end;

if you now fire the insert-statement:

   INSERT INTO NEWTABLE (NAME,DESC) VALUES ('sally','girl');    INSERT INTO NEWTABLE (NAME,DESC) VALUES ('charly','man');    COMMIT; and then

   SELECT * FROM newtable;
then you get:

   id name desc
   1 sally girl
   2 charly man

this is the way you typically set an "auto-value" like sql-server...

P 2.: in the insert-statement

after you have create the sequence (named: test_id), you can write:

   INSERT INTO NEWTABLE (ID, NAME,DESC)       VALUES (test_id.nextval, 'sally', 'girl');    COMMIT; each time you get "test_id.nextval" the sequence is incrementing by 1

hope this helps
Rüdiger
(eMail: rjs_at_berlin.de)

ngsteve_at_my-deja.com (Steve) wrote:
>I would like to be able to have a primary key that starts at 1 and
>increases by 1 everytime a new record is inserted.(ID must be unique)
>Example first record would have ID=1 then next insert will have 2 and
>so on.....
>A table NEWTABLE with three columns ID,NAME,DESC
>INSERT INTO NEWTABLE (NAME,DESC) VALUES ("sally","girl")
>In Microsoft SQL Server, I would go to design table and
>specify say ID as primary key and identity seed 1 and identity
>increment 1
>How to do this in Oracle?How to do this in Oracle DBA studio?
>Any help would be much appreciated.

-- 
__________________________________________________________
News suchen, lesen, schreiben mit http://newsgroups.web.de
Received on Mon Oct 08 2001 - 09:05:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US