How to make column autoincrement in Oracle 9i

[didebuli]

Hi all,
I have table test with fields (ID, Name, Lastname)
ID (number)
Name varchar (15)
Lastname varchar (30)

When I insert record to table I want to be incremented field ID automatically.

Please give me your opinion how can I make ID field autoincrement.

Thanks in advance.

[kpenneti]

Hi,

Better you write after insert trigger. Why i am telling is once you insert the value in to the table the after insert trigger will be fired and id no will be increment.

Hi all,
I have table test with fields (ID, Name, Lastname)
ID (number)
Name varchar (15)
Lastname varchar (30)

When I insert record to table I want to be incremented field ID automatically.

Please give me your opinion how can I make ID field autoincrement.

Thanks in advance.

[didebuli]

Can you write me step-by-step how can I do it.

Thanks in advance.

[chandu031]

Hi all,
I have table test with fields (ID, Name, Lastname)
ID (number)
Name varchar (15)
Lastname varchar (30)

When I insert record to table I want to be incremented field ID automatically.

Please give me your opinion how can I make ID field autoincrement.

Thanks in advance.

Hi,

The only way of implementing an Identity column in Oracle would be to have a
before insert trigger on the table. Here's the syntax for the same:

CREATE OR REPLACE TRIGGER 
TRIG
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
SELECT SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
 
EXCEPTION 
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM());
END;
 

To use this you will have to create a sequence first.

Hope this is helpful.

[didebuli]

Thank you very much