Introduction to MySQL Triggers

A trigger is a stored program that MySQL automatically executes in response to a specific event on a table. Triggers are used to enforce business rules, maintain audit logs, synchronize related data, and prevent invalid data from being stored.

When Triggers Fire

Triggers are associated with three DML events: INSERT, UPDATE, and DELETE. For each event, you can define a BEFORE trigger (fires before the row change) or an AFTER trigger (fires after the row change). This gives you six possible trigger combinations per table.

Key Concepts

NEW: A pseudo-row representing the new values (available in INSERT and UPDATE triggers)
OLD: A pseudo-row representing the old values (available in UPDATE and DELETE triggers)
BEFORE triggers: Can modify NEW values before they are written
AFTER triggers: Commonly used for audit logging and cascade updates

Example

DELIMITER //

CREATE TRIGGER before_product_price_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
END //

DELIMITER ;

SQL

Full Editor

Pro Tip

Use BEFORE triggers to validate or transform data before it is written; use AFTER triggers for side effects like logging.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned