search

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.

close
search

Hi, guest!

settings

menu