MySQL BEFORE INSERT Trigger

Introduction

A BEFORE INSERT trigger in MySQL is a stored program that executes before a new record is inserted into a table. It is commonly used for:

Validating input data before insertion.
Automatically modifying values (e.g., setting default values).
Preventing duplicate entries.
Generating custom primary keys.

Syntax

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic (SQL statements)
END;

trigger_name → Name of the trigger.
BEFORE INSERT → Executes before an INSERT operation.
table_name → Table on which the trigger is created.
FOR EACH ROW → The trigger runs for each inserted row.
Inside the BEGIN...END block → Place SQL logic.

Example Use Cases

1. Automatically Set Default Values

Assume we have an employees table where:

The created_at column should be set to the current timestamp if not provided.

Table Structure

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    created_at DATETIME
);

Create a BEFORE INSERT Trigger

DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END;
//

DELIMITER ;

Test the Trigger

INSERT INTO employees (name, department) VALUES ('John Doe', 'IT');

SELECT * FROM employees;

✅ Output:

+----+----------+------------+---------------------+
| id | name     | department | created_at         |
+----+----------+------------+---------------------+
|  1 | John Doe | IT         | 2025-01-31 14:20:00 |
+----+----------+------------+---------------------+

The created_at column is automatically populated with NOW().

2. Preventing Duplicate Entries

Suppose we want to ensure that no duplicate email values are inserted.

Modify Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

Create Trigger

DELIMITER //

CREATE TRIGGER before_user_insert
BEFORE INSERT
ON users
FOR EACH ROW
BEGIN
    DECLARE user_count INT;
    
    -- Check if the email already exists
    SELECT COUNT(*) INTO user_count FROM users WHERE email = NEW.email;
    
    -- Raise an error if email exists
    IF user_count > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Duplicate email not allowed!';
    END IF;
END;
//

DELIMITER ;

Test the Trigger

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
-- Successful insertion

INSERT INTO users (name, email) VALUES ('Bob', 'alice@example.com');
-- ERROR: Duplicate email not allowed!

✅ The trigger prevents inserting duplicate emails.

3. Auto-Generating a Custom Primary Key

If a table does not use AUTO_INCREMENT, we can create a custom ID format.

Table Structure

CREATE TABLE orders (
    order_id VARCHAR(10) PRIMARY KEY,
    customer_name VARCHAR(100)
);

Trigger to Generate Custom Order ID (`ORD-0001`, `ORD-0002`, etc.)

DELIMITER //

CREATE TRIGGER before_order_insert
BEFORE INSERT
ON orders
FOR EACH ROW
BEGIN
    DECLARE max_id INT;
    
    -- Get the last numeric order_id value
    SELECT IFNULL(MAX(CAST(SUBSTRING(order_id, 5) AS UNSIGNED)), 0) INTO max_id FROM orders;
    
    -- Generate new order_id
    SET NEW.order_id = CONCAT('ORD-', LPAD(max_id + 1, 4, '0'));
END;
//

DELIMITER ;

Test the Trigger

INSERT INTO orders (customer_name) VALUES ('David');
INSERT INTO orders (customer_name) VALUES ('Emma');
SELECT * FROM orders;

✅ Output:

+----------+---------------+
| order_id | customer_name |
+----------+---------------+
| ORD-0001 | David         |
| ORD-0002 | Emma          |
+----------+---------------+

The order_id is automatically generated with a custom format.

Key Points

✔ Runs before an INSERT operation on a table.
✔ Can modify or validate data before insertion.
✔ Useful for setting default values, preventing duplicates, and auto-generating keys.
✔ Use NEW.column_name to reference the new row's values.
✔ Can raise custom errors using SIGNAL SQLSTATE.

Would you like an example for a specific use case? 🚀

Souy Soeng

MySQL BEFORE INSERT Trigger