← Back to Curriculum

MySQL Triggers

📚 Lesson 6 of 15 ⏱️ 40 min

MySQL Triggers

40 min

MySQL triggers are database objects that automatically execute in response to specific events on tables. Triggers enable you to perform actions automatically when data is inserted, updated, or deleted, without requiring application code changes. Triggers are useful for data validation, auditing, maintaining derived data, and enforcing business rules at the database level. Understanding triggers enables you to build robust, self-maintaining database systems.

Triggers can fire BEFORE or AFTER INSERT, UPDATE, or DELETE operations. BEFORE triggers can modify data before it's saved or prevent operations entirely. AFTER triggers are useful for logging, notifications, or maintaining related data. Triggers execute once per row affected by the operation. Understanding trigger timing enables you to choose the appropriate trigger type for your needs.

Trigger syntax uses CREATE TRIGGER with trigger name, timing (BEFORE/AFTER), event (INSERT/UPDATE/DELETE), table name, and trigger body. Triggers can access OLD and NEW row values using OLD.column_name and NEW.column_name. OLD is available for UPDATE and DELETE, NEW is available for INSERT and UPDATE. Understanding trigger syntax enables you to create effective triggers.

Data validation triggers can enforce business rules that are too complex for CHECK constraints. For example, triggers can validate that related records exist, ensure data consistency across tables, or implement custom validation logic. Validation triggers can raise errors using SIGNAL to prevent invalid data. Understanding validation triggers enables you to enforce data integrity.

Audit triggers automatically log all changes to critical tables. Audit logs typically record who made the change, when it occurred, what changed (old vs new values), and the operation type. Audit triggers enable compliance, debugging, and security analysis. JSON can be used in audit logs to store flexible change data. Understanding audit triggers enables comprehensive change tracking.

Best practices for triggers include keeping trigger logic simple and fast, avoiding triggers that call external services, being careful about trigger chains (triggers that cause other triggers to fire), and testing triggers thoroughly. Triggers should be well-documented and should not have side effects beyond their intended purpose. Understanding best practices enables reliable trigger implementations.

Key Concepts

  • Triggers automatically execute on INSERT, UPDATE, or DELETE operations.
  • Triggers can fire BEFORE or AFTER operations.
  • Triggers can access OLD and NEW row values.
  • Triggers are useful for validation, auditing, and data maintenance.
  • Triggers execute within transactions and can roll back operations.

Learning Objectives

Master

  • Creating triggers for INSERT, UPDATE, and DELETE operations
  • Implementing data validation with triggers
  • Creating audit logs with triggers
  • Understanding trigger timing and execution

Develop

  • Understanding event-driven database programming
  • Designing maintainable trigger-based systems
  • Implementing database-level business logic

Tips

  • Create trigger: CREATE TRIGGER name BEFORE/AFTER INSERT/UPDATE/DELETE ON table FOR EACH ROW BEGIN ... END.
  • Access row values: use OLD.column for old values, NEW.column for new values.
  • Raise errors: SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error message'.
  • Use DELIMITER when creating triggers with multiple statements.

Common Pitfalls

  • Creating trigger chains, causing infinite loops or performance issues.
  • Not handling all operation types, missing some audit events.
  • Making triggers too complex, causing performance problems.
  • Not testing triggers thoroughly, causing unexpected behavior in production.

Summary

  • Triggers enable automatic execution of code on data changes.
  • Triggers can fire BEFORE or AFTER operations.
  • Triggers are useful for validation, auditing, and data maintenance.
  • Understanding triggers enables event-driven database programming.

Exercise

Create triggers for data validation and auditing.

-- Create audit table
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action VARCHAR(20),
    record_id INT,
    old_values JSON,
    new_values JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

-- Trigger to log product updates
CREATE TRIGGER product_update_trigger
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, old_values, new_values)
    VALUES (
        'products',
        'UPDATE',
        NEW.id,
        JSON_OBJECT(
            'name', OLD.name,
            'price', OLD.price,
            'stock', OLD.stock
        ),
        JSON_OBJECT(
            'name', NEW.name,
            'price', NEW.price,
            'stock', NEW.stock
        )
    );
END //

-- Trigger to validate stock before update
CREATE TRIGGER validate_stock_trigger
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Stock cannot be negative';
    END IF;
END //

DELIMITER ;

-- Test the triggers
UPDATE products SET stock = stock - 5 WHERE id = 1;
UPDATE products SET stock = -1 WHERE id = 1; -- This will fail

Exercise Tips

  • Use BEFORE triggers for validation: prevent invalid data from being saved.
  • Use AFTER triggers for logging: log changes after they're committed.
  • Use JSON for audit logs: JSON_OBJECT() to store flexible change data.
  • Test triggers: INSERT/UPDATE/DELETE test data to verify trigger behavior.

Code Editor

Output