MySQL Triggers Explained with Practical Examples

Triggers fire automatically in response to INSERT, UPDATE, or DELETE on a table — useful for enforcing rules or maintaining derived data without relying on application code to remember to do it.

-- Automatically maintain an audit log whenever a price changes
CREATE TRIGGER price_change_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, changed_at)
        VALUES (NEW.id, OLD.price, NEW.price, NOW());
    END IF;
END;

-- Prevent negative stock at the database level
CREATE TRIGGER prevent_negative_stock
BEFORE UPDATE ON inventory
FOR EACH ROW
BEGIN
    IF NEW.quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock cannot go negative';
    END IF;
END;

Triggers are powerful but easy to overuse — logic hidden in triggers is invisible to anyone just reading the application code, which can turn debugging into an unpleasant surprise. Reserve them for genuine data-integrity rules, not general business logic.

MySQL Triggers Explained with Practical Examples

Recommended

Categories