Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to increment ID with every insert
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.deReceived on Mon Oct 08 2001 - 09:05:57 CDT