Create and auto-increment field using “sequence” in Oracle database

Create a database table:

create table Books ( bookID number(38),
title varchar2(50),
author varchar2(50),
pages number(38),
constraint pk_bookID PRIMARY KEY(bookID)
);

Create a sequence:

Create sequence sequence_name start with value
increment by value
minvalue value
maxvalue value;

***** For example ****

Create sequence auto_incr start with 1
increment by 1;

Insert some sample data into the table:

insert into books (bookID,title,author, pages) values(auto_incr.nextval,'To Kill A Mockingbird','Harper Lee', '400');
insert into books (bookID,title,author, pages) values(auto_incr.nextval,'War and Piece','Leo Tolstoy', '1500');
insert into books (bookID,title,author, pages) values(auto_incr.nextval,'Where The Sidewalk Ends','Shel Silverstein', '175');

Ensure data was correctly entered into the table and the bookID field is successfully incrementing:

SELECT * FROM books;

Create a trigger (syntax is for Oracle 11.1 and later:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
:new.primary_key_column := name_of_sequence.nextval;
END;

***** For Example *****

CREATE OR REPLACE TRIGGER bookID_trigger
BEFORE INSERT ON books
FOR EACH ROW
BEGIN
:new.bookID := auto_incr.nextval;
END;

Insert a new record, omitting the bookID.

insert into books (title,author, pages) values('Where The Sidewalk Ends','Shel Silverstein', '175');

Select * to see if the new recored entered correctly (with the next ID number).

Note: In Oracle 12c or later, you can use IDENTITY datatype.