Oracle Auto Increment ID

Oracle does not support the auto increment automatically. We need to create sequence and trigger before.

Sequence creation:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

example:

CREATE SEQUENCE book_sequence
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

Trigger Creation:

CREATE OR REPLACE TRIGGER <trigger name> BEFORE INSERT ON <table name> REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT <sequence name>.nextval INTO :NEW.<id from table> FROM dual; END;

example:

CREATE OR REPLACE TRIGGER "BOOK_TRIGGER" BEFORE INSERT ON book REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT book_sequence.nextval INTO :NEW.BOOKID FROM dual; END;

That’s all hopefully it’s helpful..

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s