auto incrementing a field in a table

How can I set a number field in a table to be auto-increment.
Basically the same concept that is used in MS Access, where the field value is automatically incremented when a new row is inserted.
thanks in advance.

You will have to write a before-insert trigger that fetches a value from a sequence before the row gets inserted into the database.

could you provide me with a sample code that do this?

Use SEQUENCE.

Example :

CREATE SEQUENCE TEST
INCREMENT BY 1
START WITH 1
/


Insert Into
(sequence, name, address)
Values
(TEST.NEXT_VALUE,
'Marcio Almeida',
'R Cupa, 139');

01-03-2001, 04:40 PM

trogen

what is the type of sequence in the example?
i keep getting a ORA-00984: column not allowed here.

01-04-2001, 07:06 AM

ShrutiM

The type of column "sequence" should be numeric.
If your actual table.column is a varchar then use
to_char(TEST.NEXT_VALUE) in the insert statement.

01-04-2001, 01:59 PM

ad

The error "ORA-00984: column not allowed here" is coming because you are refering the sequence as next value as NEXT_VALUE. This is wrong. It should be NEXTVAL. You change this and it will work fine.

thanks,
ad

All times are GMT -4. The time now is 12:11 AM.