MySQL BEFORE INSERT Trigger

Master MySQL BEFORE INSERT triggers for validating and transforming new row data before storage. Covers SIGNAL for rejecting invalid inserts, SET NEW.column for normalization, the difference from CHECK constraints, and worked examples with age validation and name capitalization.

A BEFORE INSERT trigger fires before a new row enters the table — your last chance to validate or modify the data on its way in. Two big use cases:

• Validation — if the row violates a business rule, raise an error with SIGNAL to abort the INSERT.
• Transformation — adjust column values via SET NEW.col = ... to derive defaults, normalize formats, or compute fields.

Notes:

• Only NEW is available — OLD doesn't exist for INSERTs (no previous version of the row to refer to).
• NEW.column_name can be both read and assigned with SET NEW.column_name = expression.
• To abort the INSERT, raise an error with SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...'. The triggering INSERT and any pending transaction work get rolled back.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    age         INT,
    join_date   DATE
);

Enforce a business rule that employees must be at least 18. The trigger checks NEW.age and uses SIGNAL to abort the INSERT if the rule is violated:

DROP TRIGGER IF EXISTS before_insert_employee;

DELIMITER $$

CREATE TRIGGER before_insert_employee
    BEFORE INSERT ON employees
    FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Employee must be at least 18 years old';
    END IF;
END$$

DELIMITER ;

Try inserting a 16-year-old:

INSERT INTO employees (first_name, last_name, age, join_date)
VALUES ('John', 'Doe', 16, '2024-04-13');

The INSERT was aborted — no row appears in employees. Now try a valid age:

INSERT INTO employees (first_name, last_name, age, join_date)
VALUES ('Steven', 'Raj', 20, '2025-04-17');

SELECT * FROM employees;

BEFORE INSERT triggers can modify the new row. Normalize names to a canonical case and supply a default join date if the caller didn't:

DROP TRIGGER IF EXISTS before_insert_employee;

DELIMITER $$

CREATE TRIGGER before_insert_employee
    BEFORE INSERT ON employees
    FOR EACH ROW
BEGIN
    -- Validate
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Employee must be at least 18 years old';
    END IF;

    -- Normalize: capitalize names
    SET NEW.first_name = CONCAT(UPPER(LEFT(NEW.first_name, 1)),
                                LOWER(SUBSTRING(NEW.first_name, 2)));
    SET NEW.last_name  = CONCAT(UPPER(LEFT(NEW.last_name, 1)),
                                LOWER(SUBSTRING(NEW.last_name, 2)));

    -- Default the join date to today if not supplied
    IF NEW.join_date IS NULL THEN
        SET NEW.join_date = CURDATE();
    END IF;
END$$

DELIMITER ;

INSERT INTO employees (first_name, last_name, age) VALUES ('jANE', 'sMITH', 25);

SELECT * FROM employees ORDER BY id;

Notice 'jANE' became 'Jane' and the missing join_date got defaulted to today's date — the trigger transformed the data on its way in.

1. Use DROP TRIGGER IF EXISTS at the top of every deployment script.
2. Validate first, transform second. If validation fails, the SIGNAL stops the trigger before any unnecessary work.
3. Use SQLSTATE '45000' as the convention for trigger-raised validation errors.
4. Provide actionable messages in MESSAGE_TEXT — "age must be at least 18" beats "validation failed".
5. Don't query the same table the trigger is attached to — MySQL prohibits it (would cause infinite recursion).
6. Keep trigger bodies short. Per-row overhead multiplies with INSERTs that affect many rows.
7. For column transformation only, prefer generated columns when you can: full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)). Triggers are needed when the transformation depends on logic too complex for a generated column.

• BEFORE INSERT triggers fire just before a new row is written, giving you the chance to validate or transform it.
• Only NEW is available — there's no OLD on INSERT.
• You can read and assign to NEW.column in BEFORE triggers; assigning changes what gets stored.
• Use SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...' to abort the INSERT with a meaningful error.
• For simple value validation, prefer CHECK constraints (8.0.16+); reach for triggers when the logic is too complex for CHECK.
• Trigger bodies fire once per affected row — keep them fast on bulk inserts.