How to Create an Auto Increment Field Using Sequence

Let's say we have a table called "test" with two columns, id and testdata. (This is just a dumb quick example, so I won't bother to specify any constraints on id.) 

create table test (id number, testdata varchar2(255)); 

2. Next we'll create a sequence to use for the id numbers in our test table. 

create sequence 

test_seq start with 1 
increment by 1 
nomaxvalue; 

You could change "start with 1" to any number you want to begin with (e.g. if you already have 213 entries in a table and you want to begin using this for your 214th entry, replace with "start with 214"). The "increment by 1" clause is the default, so you could omit it. You could also replace it with "increment by n" if you want it to skip n-1 numbers between id numbers. The "nomaxvalue" tells it to keep incrementing forever as opposed to resetting at some point. i (I'm sure Oracle has some limitation on how big it can get, but I don't know what that limit is). 

3. Now we're ready to create the trigger that will automatically insert the next number from the sequence into the id column. 


create trigger test_trigger
 before insert on test

 for each row 
begin 

select test_seq.nextval into :new.id from dual; 

end;

You've demonstrated an implementation using triggers. This is not necessary, since instead it can be included as part of the INSERT statement. Using your example, my INSERT statement would be:

    
insert into test values(test_seq.nextval, 'Vamsi');