BEFORE INSERT triggers in MySQL

Introduction

BEFORE INSERT triggers let you run some checks or changes right before new data is added to a table. This helps keep data clean and correct automatically.

You want to check or change data before it is saved, like making sure a username is lowercase.

You want to add a timestamp or default value automatically before saving a new row.

You want to prevent bad or duplicate data from being inserted.

You want to log or audit data before it is stored.

You want to modify data fields based on some rules before saving.

Syntax

MySQL

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic here
END

The trigger runs once for each new row being inserted.

Use NEW.column_name to access or change the data being inserted.

Examples

This trigger changes the username to lowercase before saving it.

MySQL

CREATE TRIGGER lowercase_username
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.username = LOWER(NEW.username);
END

This trigger sets the current date and time automatically when a new order is inserted.

MySQL

CREATE TRIGGER set_created_at
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END

This trigger stops the insert if the email field is empty.

MySQL

CREATE TRIGGER prevent_empty_email
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  IF NEW.email = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email cannot be empty';
  END IF;
END

Sample Program

This example creates a users table and a BEFORE INSERT trigger that makes usernames lowercase and sets the creation time automatically. Then it inserts a user with uppercase letters and shows the stored data.

MySQL

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50),
  created_at DATETIME
);

DELIMITER $$
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.username = LOWER(NEW.username);
  SET NEW.created_at = NOW();
END$$
DELIMITER ;

INSERT INTO users (username) VALUES ('Alice');
SELECT * FROM users;

Important Notes

Remember to change the delimiter when creating triggers in MySQL to avoid errors.

You can modify NEW.column values but not OLD.column values in BEFORE INSERT triggers.

Use SIGNAL to stop an insert with a custom error message if needed.

Summary

BEFORE INSERT triggers run before new data is saved to a table.

They let you check, change, or stop data from being inserted.

Use NEW to access or modify the new row's data.