How to Create a BEFORE INSERT Trigger in MySQL

In MySQL, you create a BEFORE INSERT trigger using the CREATE TRIGGER statement followed by the trigger name, timing (BEFORE), event (INSERT), and the table name. Inside the trigger, you write code to execute before a new row is inserted, often modifying NEW values or enforcing rules.

Syntax

The basic syntax to create a BEFORE INSERT trigger in MySQL is:

• CREATE TRIGGER trigger_name: Names your trigger.
• BEFORE INSERT ON table_name: Specifies the timing and event.
• FOR EACH ROW: Runs the trigger for every inserted row.
• BEGIN ... END: Contains the SQL statements to execute.
• NEW.column_name: Refers to the new row's column values.

sql

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements here
END;

Example

This example creates a BEFORE INSERT trigger that sets the created_at column to the current timestamp if it is not provided during insert.

sql

DELIMITER $$
CREATE TRIGGER set_created_at_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = NOW();
  END IF;
END$$
DELIMITER ;

Output

Trigger created successfully.

Common Pitfalls

Common mistakes when creating BEFORE INSERT triggers include:

• Forgetting to use DELIMITER to change the statement delimiter, causing syntax errors.
• Trying to modify OLD values instead of NEW in BEFORE INSERT triggers.
• Not using FOR EACH ROW, which is required for row-level triggers.
• Writing complex logic that causes infinite loops by triggering the same trigger repeatedly.

sql

/* Wrong: Missing DELIMITER change causes error */
CREATE TRIGGER bad_trigger
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.name = UPPER(NEW.name);
END;

/* Right: Use DELIMITER to avoid errors */
DELIMITER $$
CREATE TRIGGER good_trigger
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.name = UPPER(NEW.name);
END$$
DELIMITER ;

Quick Reference

Part	Description
CREATE TRIGGER trigger_name	Defines the trigger's name
BEFORE INSERT ON table_name	Sets trigger timing and event
FOR EACH ROW	Runs trigger for every inserted row
BEGIN ... END	Contains the trigger's SQL code
NEW.column_name	Access or modify new row's column

Key Takeaways

Use CREATE TRIGGER with BEFORE INSERT to run code before inserting rows.

Modify NEW.column values inside the trigger to change inserted data.

Always use DELIMITER to avoid syntax errors when creating triggers.

FOR EACH ROW is required to apply the trigger to every inserted row.

Avoid modifying OLD values or causing recursive trigger calls.