Database Answers Header Rhuddlan Castle, North Wales
Oracle Sequence for Auto-Increment
Home Ask a Question Careers Data Models FAQs SQL Scripts Search Site Map  
Welcome to Barry's Book of Useful Scripts
Example Number 1 ...
    
create sequence product_seq start with 1 increment 1
/
create or replace trigger product_insert before insert for each row begin
select productseq.nextval
into :new.product_id
from dual;
end;
/

Example Number 2 ...
How to create an autoincrement field in a table with a sequence ...

SQLWKS> create table bob(a number , b varchar2(21));
Statement processed.

First create a sequence
SQLWKS> create sequence x ;
Statement processed.

Then create the trigger.
create trigger y before insert on bob
for each row
when (new.a is null)
begin
 select x.nextval into :new.a from dual;
end;
/

Example Number 3 ...
    First create a sequence:
 
create sequence emp_no_seq;
 
By default it increments by 1 starting at 0.
Use its values when inserting data into the table:
 
insert into t_emp values (emp_no_seq.nexval, 'Joe Black');

[ Home Page | Ask Us a Question | Contact Us | The Life of a DBA | FAQs | Search | Site Map ]

© IceBreaker WebDesigns 2001