How to Use BEFORE INSERT TRIGGER with MAX(ID) for Auto-Incrementing IDs on Oracle

One way to create auto-incrementing primary key values in Oracle without the need for sequences is to use a BEFORE INSERT TRIGGER with MAX(ID). In this blog, let us explore the BEFORE INSERT TRIGGER with MAX(ID) to create an ID with AUTO_INCREMENT on Oracle.

How to use BEFORE INSERT TRIGGER with MAX(ID)on Oracle?

This method involves a BEFORE INSERT trigger that, before inserting a new row, uses MAX(ID) to retrieve the highest existing ID value from the table and increment it by one. By using this method, numerical gaps are avoided, and each new entry is guaranteed to have a unique, sequential ID. Databases without available or chosen sequences can benefit from it.

Syntax:

--Create an Employee table
CREATE TABLE table_name (
    column_name NUMBER PRIMARY KEY,
    other_column_name column_type(size),
    ...
);
--Create a BEFORE INSERT Trigger
CREATE OR REPLACE TRIGGER trigger_name  
BEFORE INSERT ON table_name  
FOR EACH ROW  
BEGIN  
    IF :NEW.column_name IS NULL THEN  
        SELECT COALESCE(MAX(column_name), 0) + 1 INTO :NEW.column_name FROM table_name;
    END IF;  
END;
/
--Insert the data into the table 
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
--To display the data
SELECT * FROM table_name;

Examples of using BEFORE INSERT TRIGGER with MAX(ID) on Oracle

Now, let’s see some examples for using BEFORE INSERT TRIGGER with MAX(ID) on Oracle for creating an ID with AUTO_INCREMENT

Example 1:

-- Create an Employee table 
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);
--Create a BEFORE INSERT TRIGGER
CREATE OR REPLACE TRIGGER employees_before_insert  
BEFORE INSERT ON Employees  
FOR EACH ROW  
BEGIN  
    IF :NEW.Employee_id IS NULL THEN  
        SELECT COALESCE(MAX(Employee_id), 0) + 1 INTO :NEW.Employee_id FROM Employees;
    END IF;  
END;
/
--Insert some values
INSERT INTO Employees (Employee_dept, Salary) VALUES ('BDT', 55000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TRA', 33000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TCW', 75000.00);
--To display the table
SELECT * FROM Employees;

Output:

Explanation: Here, the TRIGGER automatically creates an EMPLOYEE_ID.

Example 2:

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

-- Create a BEFORE INSERT TRIGGER  
CREATE OR REPLACE TRIGGER products_before_insert  
BEFORE INSERT ON Products  
FOR EACH ROW  
BEGIN  
    IF :NEW.Product_id IS NULL THEN  
        SELECT COALESCE(MAX(Product_id), 0) + 1 INTO :NEW.Product_id FROM Products;  
    END IF;  
END;
/

-- Insert some values  
INSERT INTO Products (Product_name, Price) VALUES ('Laptop', 1200.50);  
INSERT INTO Products (Product_name, Price) VALUES ('Smartphone', 850.75);  
INSERT INTO Products (Product_name, Price) VALUES ('Headphones', 150.99);  

-- Display the Product table  
SELECT Product_id,price FROM Products;

Output:

INSERT TRIGGER with MAX(ID) on Oracle Example 2

Explanation: Here, the COALESCE(MAX(Product_id), 0) + 1 ensures that if the table is empty, then the first inserted row gets ID 1. Then the Product_ID is automatically increased based on the existing ID.

Conclusion

You may easily construct auto-incrementing primary key values in Oracle without the need for sequences by utilizing a BEFORE INSERT TRIGGER with MAX(ID). Obtaining the highest existing ID and increasing it before adding a new row guarantees distinct and consecutive ID assignments. In settings where sequences are not desired, this method helps avoid number gaps. The repeated scans of the ID column, however, could cause performance problems in large tables. Additionally, when several transactions try to insert at the same time, concurrency issues may occur. For straightforward auto-increment implementations in Oracle databases, it is still a workable option despite these drawbacks.