BEFORE UPDATE triggers in MySQL

Introduction

A BEFORE UPDATE trigger lets you run some actions automatically just before a row in a table is changed. It helps you check or change data before saving.

You want to check if new data meets rules before updating a record.

You want to automatically change or fix data before it is saved.

You want to keep a log of old and new values before changes happen.

You want to prevent certain updates based on conditions.

You want to update related data automatically when a record changes.

Syntax

MySQL

CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
  -- statements to run before update
END;

The trigger runs once for each row that is updated.

Use NEW.column_name to access the new value being set.

Examples

This trigger sets age to 0 if someone tries to update it to a negative number.

MySQL

CREATE TRIGGER check_age_before_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.age < 0 THEN
    SET NEW.age = 0;
  END IF;
END;

This trigger updates the 'updated_at' column to the current time before any update.

MySQL

CREATE TRIGGER update_timestamp_before_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

Sample Program

This example creates a products table and a BEFORE UPDATE trigger that sets price to 0 if a negative price is given and updates the last_modified timestamp. Then it tries to update price to -5, but the trigger changes it to 0.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  last_modified TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price < 0 THEN
    SET NEW.price = 0;
  END IF;
  SET NEW.last_modified = NOW();
END$$
DELIMITER ;

INSERT INTO products VALUES (1, 'Pen', 1.50, NOW());

UPDATE products SET price = -5 WHERE id = 1;

SELECT * FROM products WHERE id = 1;

Important Notes

Remember to use DELIMITER to define triggers in MySQL because triggers contain multiple statements.

BEFORE UPDATE triggers can modify the NEW values before they are saved.

You cannot change OLD values in triggers; they represent the current stored data.

Summary

BEFORE UPDATE triggers run before a row is updated in a table.

They let you check or change data before saving.

Use NEW to access or modify the new data values.