Create unique id in oracle table using trigger and sequence

In one of my recent projects, I had to insert into a table where one column had to be unique and auto generated. Further I also had to maintain the data so that it was in serial with previously inserted data.

In oracle this can be accomplished by the combined use of Trigger and Sequence. I am going to describe the technique in this post.

Sequence: A sequence can generate unique number. User can limit the minimum and maximum value and also the starting value of the generated number. In other words a sequence is like a unique number generator whose nature can be controlled by a user. One sequence can be maintained per table so that we can provide unique and serial id for each table.

Trigger: A trigger can be fired before insert, update or delete operations in any table. This can be designed to help apply business logic while using data manipulations using insert, update and delete plsql programming.

Create a sequence

CREATE SEQUENCE SEQ_EMPLOYEE INCREMENT BY 1 START WITH 10000 MINVALUE 10000 MAXVALUE 99999 NOCYCLE NOCACHE NOORDER;

Create a Trigger on Employee Table

CREATE OR REPLACE TRIGGER TRIGGER_EMPLOYEE
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
begin
select SEQ_EMPLOYEE.nextval into :new.EMPLOYEE_ID from dual;
end;

Insert into Employee Table

INSERT INTO Employee (Name, Address, Post)
VALUES ('sangam','New Road-10', 'Software Developer')

Note: Since the trigger fetches unique number from sequence and inserts into Employee_id field, we need not to put this column in Insert statement as above.

Happy Oracling!!