BEFORE INSERT TRIGGER with CUSTOM LOGIC in Oracle

In Oracle, a BEFORE INSERT TRIGGER with custom logic is a powerful way to generate AUTO_INCREMENT IDs dynamically. In this blog, let us explore the BEFORE INSERT TRIGGER with CUSTOM LOGIC method to create an ID with AUTO_INCREMENT on Oracle

How to use BEFORE INSERT TRIGGER with CUSTOM LOGIC on Oracle?

This method allows developers to define custom rules for ID generation before new records are inserted into a table. By using a SEQUENCE inside the trigger, IDs can be assigned automatically without explicitly specifying them in the INSERT statement. While ensuring different and sequential values, this approach allows flexibility for additional logic, such as conditional ID assignment. It is especially helpful in situations when business requirements govern the generation of IDs.

Syntax:

CREATE SEQUENCE sequence_name
START WITH initial_value  
INCREMENT BY step_value  
[optional parameters];
CREATE TABLE table_name (
    column1 NUMBER PRIMARY KEY,  
    column2 column_type(size),
    ...
);
--Create BEFORE INSERT TRIGGER
CREATE OR REPLACE TRIGGER trigger_name  
BEFORE INSERT ON table_name  
FOR EACH ROW  
BEGIN  
    IF :NEW.column1 IS NULL THEN  
        SELECT COALESCE(MAX(column1), sequence_name.NEXTVAL) + 1  
        INTO :NEW.column1  
        FROM table_name;
    END IF;  
END;
/
--Insert the data 
INSERT INTO table_name (column2, column3, ...)  
VALUES (value2, value3, ...);
--Display the table 
SELECT * FROM table_name;

Examples of using BEFORE INSERT TRIGGER with CUSTOM LOGIC on Oracle

Now, let’s see some examples for using BEFORE INSERT TRIGGER with CUSTOM LOGIC on Oracle for creating an ID with AUTO_INCREMENT.

Example 1:

-- Step 1: Create a Sequence for Employee ID
CREATE SEQUENCE employee_seq
START WITH 1 
INCREMENT BY 1 
NOCYCLE NOCACHE;

-- Step 2: Create the Employees Table
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

-- Step 3: Create a BEFORE INSERT Trigger for Auto-Increment
CREATE OR REPLACE TRIGGER employees_before_insert  
BEFORE INSERT ON Employees  
FOR EACH ROW  
DECLARE
    v_max_id NUMBER;
BEGIN  
    -- Check the max Employee_id in case the sequence is behind
    SELECT COALESCE(MAX(Employee_id), 0) INTO v_max_id FROM Employees;
    -- Assign the next ID (greater than max ID or sequence nextval)
    :NEW.Employee_id := GREATEST(v_max_id + 1, employee_seq.NEXTVAL);
END;
/

-- Step 4: Insert Data (Trigger will auto-generate Employee_id)
INSERT INTO Employees (Employee_dept, Salary) VALUES ('HR', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('Finance', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('IT', 75000.75);

-- Step 5: Display Data
SELECT * FROM Employees;

Output:

Using BEFORE INSERT TRIGGER with CUSTOM LOGIC on Oracle

Explanation: A SEQUENCE is used to generate numeric IDs. A BEFORE INSERT TRIGGER sets the Employee_id

Master Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Example 2:

-- Step 1: Create a Sequence for Customer ID
CREATE SEQUENCE customer_seq
START WITH 1000 
INCREMENT BY 1 
NOCYCLE NOCACHE;

-- Step 2: Create the Customer Table
CREATE TABLE Customers (
    Customer_id NUMBER PRIMARY KEY,
    Customer_name NVARCHAR2(100),
    Join_date DATE DEFAULT SYSDATE
);

-- Step 3: Create a BEFORE INSERT Trigger for Auto-Increment
CREATE OR REPLACE TRIGGER customers_before_insert  
BEFORE INSERT ON Customers  
FOR EACH ROW  
DECLARE
    v_max_id NUMBER;
BEGIN  
    -- Ensure the sequence is ahead of the existing max ID
    SELECT COALESCE(MAX(Customer_id), 999) INTO v_max_id FROM Customers;
    -- Assign the next ID (ensuring it’s always increasing)
    :NEW.Customer_id := GREATEST(v_max_id + 1, customer_seq.NEXTVAL);
END;
/ 

-- Step 4: Insert Data (Trigger will auto-generate Customer_id)
INSERT INTO Customers (Customer_name) VALUES ('John Doe');
INSERT INTO Customers (Customer_name) VALUES ('Alice Smith');
INSERT INTO Customers (Customer_name) VALUES ('Robert Brown');

-- Step 5: Display Data
SELECT Customer_id FROM Customers;

Output:

Explanation: Here, the :NEW.Customer_id := GREATEST(v_max_id + 1, customer_seq.NEXTVAL) Statement ensures that the Customer_id is automatically increased starting from 1000, by incrementing 1 for every newly inserted record.

Conclusion

Using a BEFORE INSERT TRIGGER with custom logic is a flexible and reliable way to implement AUTO_INCREMENT functionality in Oracle. ID management is improved by dynamically assigning the next available ID before insertion. Even though it requires extra preparation, this is a popular and effective way to automatically increase primary keys in Oracle databases.