auto incrementing a field in a table

[trogen]

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.

[marist89]

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

[trogen]

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

[marcio.68]

Use SEQUENCE.

Example :

CREATE SEQUENCE TEST
INCREMENT BY 1
START WITH 1
/


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

[trogen]

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

[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.

[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