How to Create a BEFORE UPDATE Trigger in MySQL

In MySQL, you create a BEFORE UPDATE trigger using the CREATE TRIGGER statement followed by the trigger name, timing (BEFORE), event (UPDATE), and the table it applies to. Inside the trigger body, you can modify the NEW row values before the update happens.

Syntax

The syntax to create a BEFORE UPDATE trigger in MySQL is:

• CREATE TRIGGER trigger_name: Names your trigger.
• BEFORE UPDATE ON table_name: Specifies the timing and event.
• FOR EACH ROW: Runs the trigger for each row affected.
• BEGIN ... END: Contains the SQL statements to execute.
• NEW.column_name: Refers to the new value of the column being updated.

sql

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

Example

This example creates a BEFORE UPDATE trigger that prevents the salary column from being set below 1000 in the employees table. If a lower value is attempted, it resets the salary to 1000.

sql

DELIMITER $$
CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 1000 THEN
    SET NEW.salary = 1000;
  END IF;
END$$
DELIMITER ;

Output

Trigger created successfully.

Common Pitfalls

• Forgetting to use DELIMITER to change the statement delimiter when creating triggers.
• Trying to modify OLD values instead of NEW in BEFORE UPDATE triggers.
• Not specifying FOR EACH ROW, which is required for row-level triggers.
• Using BEFORE UPDATE triggers on tables without UPDATE privileges.

sql

/* Wrong: Trying to modify OLD value */
DELIMITER $$
CREATE TRIGGER wrong_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  SET OLD.salary = 1000; -- This will cause an error
END$$
DELIMITER ;

Quick Reference

Part	Description
CREATE TRIGGER trigger_name	Defines the trigger and its name
BEFORE UPDATE ON table_name	Sets trigger timing and event
FOR EACH ROW	Runs trigger for each affected row
BEGIN ... END	Contains trigger logic
NEW.column_name	Refers to new row values before update

Key Takeaways

Use CREATE TRIGGER with BEFORE UPDATE to run code before row updates.

Modify NEW.column_name to change values before they are saved.

Always use DELIMITER to define triggers with multiple statements.

FOR EACH ROW is required for row-level triggers.

Avoid modifying OLD values in BEFORE UPDATE triggers.