Auto_Increment IDs Using SEQUENCE and TRIGGER in Oracle

A sequence in Oracle SQL is a database object used to auto-increment an ID. In this blog, let us explore the SEQUENCE and TRIGGER method to create an ID with AUTO_INCREMENT on Oracle.

How to use SEQUENCE and TRIGGER on Oracle?

A stored process known as a TRIGGER starts automatically in reaction to particular table events. Before adding a new row, a BEFORE INSERT TRIGGER can be used to assign the subsequent value from a SEQUENCE to an ID column. The trigger uses: NEW.column_name := sequence_name to retrieve the subsequent sequence value. NEXTVAL guarantees sequential and automated ID generation.

Syntax:

-- Creating a table
CREATE TABLE table_name (
    column_name NUMBER PRIMARY KEY,
    other_column_name column_type(size),
    ...
);
-- Create a Sequence
CREATE SEQUENCE sequence_name  
START WITH start_value  
INCREMENT BY increment_value  
[ MINVALUE min_value | NOMINVALUE ]  
[ MAXVALUE max_value | NOMAXVALUE ]  
[ CYCLE | NOCYCLE ]  
[ CACHE cache_size | NOCACHE ];
-- Create a TRIGGER to AUTO-Assign ID
CREATE OR REPLACE TRIGGER trigger_name  
BEFORE INSERT ON table_name  
FOR EACH ROW  
BEGIN  
    IF :NEW.column_name IS NULL THEN  
        SELECT sequence_name.NEXTVAL INTO :NEW.column_name FROM dual;  
    END IF;  
END;
/
-- Insert the data into the table
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
-- Display data from the table

Examples of using SEQUENCE and TRIGGER on Oracle

Now, let’s see some examples for using the SEQUENCE and TRIGGER on Oracle for creating an ID with AUTO_INCREMENT.

Example 1:

-- Create the Employee table
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);
-- Create a sequence for Employee_id
CREATE SEQUENCE employee_seq  
START WITH 1  
INCREMENT BY 1  
NOCACHE  
NOCYCLE;
-- Create a trigger for Employee_id
CREATE OR REPLACE TRIGGER employees_before_insert  
BEFORE INSERT ON Employees  
FOR EACH ROW  
BEGIN  
    IF :NEW.Employee_id IS NULL THEN  
        SELECT employee_seq.NEXTVAL INTO :NEW.Employee_id FROM dual;  
    END IF;  
END;
/
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TCW', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TRA', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('BDA', 75000.75);
-- Displaying the Employee table 
SELECT * FROM Employees;

Output:

Explanation: Here, the trigger automatically assigns the Employee_ID value from Employee_seq.

Example 2:

-- Create the Product table
CREATE TABLE Products (
    Product_id NUMBER PRIMARY KEY,
    Product_name NVARCHAR2(100),
    Price DECIMAL(10,2)
);

-- Create a sequence for Product_id
CREATE SEQUENCE product_seq  
START WITH 100  
INCREMENT BY 5  
NOCACHE  
NOCYCLE;

-- Create a trigger for Product_id
CREATE OR REPLACE TRIGGER products_before_insert  
BEFORE INSERT ON Products  
FOR EACH ROW  
BEGIN  
    IF :NEW.Product_id IS NULL THEN  
        SELECT product_seq.NEXTVAL INTO :NEW.Product_id FROM dual;  
    END IF;  
END;
/

-- Inserting data into the Product table
INSERT INTO Products (Product_name, Price) VALUES ('Laptop', 1200.50);
INSERT INTO Products (Product_name, Price) VALUES ('Smartphone', 800.75);
INSERT INTO Products (Product_name, Price) VALUES ('Headphones', 150.99);

-- Displaying the Products table
SELECT Product_id,Price FROM Products;

Output:

Explanation: Here, the Product_ID Column gets unique values automatically starting from 100, increasing by 5 for each new record

Conclusion

An established and dependable technique for generating auto-incrementing primary key values in Oracle is to use a SEQUENCE and a TRIGGER. It provides control of ID generation with the flexibility to define using sequence properties like START WITH, INCREMENT BY, and CACHE.

This method works well for applications that need human sequence management since it guarantees sequential and unique ID assignments. Even though it requires more preparation than more recent techniques, it is still a reliable way to deal with auto-incrementing IDs in Oracle databases.