Create AUTO_INCREMENT Id Without Trigger

so now we won’t Auto_Increment with column. But we will mimic this behavior with triggers. The trigger will automatically be called when an insert query is applied.

First, let’s create another table for better understanding.

create table AutoIncrementWithTrigger(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Now we will create a trigger which will be called Before Insert.

DELIMITER //
CREATE TRIGGER trg_before_insert
BEFORE INSERT ON AutoIncrementWithTrigger
FOR EACH ROW
BEGIN
    DECLARE last_id INT;
    SET last_id = (SELECT COALESCE(MAX(id), 0) FROM AutoIncrementWithTrigger);
    SET NEW.id = last_id + 1;
END;
//
DELIMITER ;

Let’s See what this trigger is doing.

1. BEFORE INSERT – Mentions when the trigger should execute
2. FOR EACH ROW – Tells to execute the trigger for all rows and not for the whole block
3. COALESCE(MAX(id), 0) – It will find the maximum id used in the table and if id is not assigned then it will give 0.
4. NEW.id = last_id+1 – This will assign the incremented id to the newly added row.

Now we will insert the rows to look at the results.

insert into AutoIncrementWithTrigger(name, age) 
values  ("John",20),
        ("Mark",35),
        ("Johnson",40);

Output:

By this, we can use AUTO_INCREMENT to achieve the auto-incrementing primary key. Thus we can use triggers to calculate the id and assign it to new records.

In all tables, we always have a primary key which is used to identify rows uniquely. For that, we generally use integers as a type of column. When adding each row, we assign a new integer to it. Whenever we insert a new row, we don’t want to manually get the last integer and use it in the INSERT query. Instead, we want an option that can automatically generate a new ID every time we try to insert it.