MySql - How triggers work

How Triggers Work (Before/After Insert, Update, Delete) in MYSQL

Introduction

Triggers in MySQL are special stored programs that automatically execute in response to certain events on a particular table. They allow you to define automatic behaviors that occur before or after data modification events such as INSERT, UPDATE, or DELETE. Triggers are widely used to enforce business rules, maintain audit trails, replicate data, or validate data integrity beyond what constraints can offer.

This comprehensive guide explains how MySQL triggers work, the types of triggers (BEFORE/AFTER for INSERT, UPDATE, DELETE), their syntax, execution flow, use cases, limitations, and examples. By the end, you will have a solid understanding of creating, managing, and effectively using triggers in your MySQL databases.

What Is a Trigger in MySQL?

A trigger is a database object associated with a table that activates automatically when a specified data modification event occurs on that table. Triggers are procedural code blocks that can perform operations such as checking conditions, modifying other tables, or modifying the row being inserted or updated.

• Triggers are linked to a specific table and event.
• They execute automatically without explicit invocation by users or applications.
• Triggers can be defined to run before or after the triggering event.
• They provide a way to automate complex logic at the database layer.

Types of Triggers in MySQL

MySQL supports triggers on the following events and timing:

Note: MySQL does not support triggers on TRUNCATE or DROP table operations.

How Triggers Work Internally

When an event occurs on a table that has an associated trigger, MySQL invokes the trigger's code automatically in the same transactional context as the triggering statement. This means:

• Triggers execute atomically with the triggering event—if the trigger fails, the triggering statement fails and the transaction can be rolled back.
• Triggers can access the data being inserted, updated, or deleted using special references NEW and OLD.
• BEFORE triggers execute before the database applies the change, allowing modification or validation of the data.
• AFTER triggers execute after the change has been made to the database, useful for auditing or cascading actions.

Accessing Row Data with NEW and OLD

Modifying Data in BEFORE Triggers

In BEFORE INSERT or BEFORE UPDATE triggers, you can modify the NEW row to change the data that will be inserted or updated. This is useful for applying default values, enforcing rules, or data sanitization.

Syntax for Creating Triggers

CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name FOR EACH ROW
BEGIN
    -- trigger logic here
END;

Key points:

• FOR EACH ROW means the trigger fires once for each row affected.
• Triggers cannot be called explicitly; they are invoked automatically.
• Trigger names must be unique per schema.

Detailed Explanation of Trigger Types

BEFORE INSERT Trigger

Fires before a new row is inserted. Allows modification or validation of the data before it is saved.

Example: Setting Default Values

DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.hire_date IS NULL THEN
        SET NEW.hire_date = CURDATE();
    END IF;
END;
//

DELIMITER ;

AFTER INSERT Trigger

Fires after a new row is inserted. Commonly used for logging, auditing, or propagating changes.

Example: Audit Insert

DELIMITER //

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit(employee_id, action, action_time)
    VALUES (NEW.employee_id, 'INSERT', NOW());
END;
//

DELIMITER ;

BEFORE UPDATE Trigger

Fires before an existing row is updated. Allows validation and modification of the new values.

Example: Enforcing Business Rules

DELIMITER //

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;
//

DELIMITER ;

AFTER UPDATE Trigger

Fires after a row is updated. Can be used for auditing or to perform actions dependent on the update.

Example: Log Salary Changes

DELIMITER //

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes(employee_id, old_salary, new_salary, change_date)
        VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
    END IF;
END;
//

DELIMITER ;

BEFORE DELETE Trigger

Fires before a row is deleted. Can be used to prevent deletion or log details.

Example: Prevent Deletion of CEO

DELIMITER //

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.position = 'CEO' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete CEO';
    END IF;
END;
//

DELIMITER ;

AFTER DELETE Trigger

Fires after a row is deleted. Useful for cleanup or audit logging.

Example: Audit Deletions

DELIMITER //

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_deletions(employee_id, deleted_at)
    VALUES (OLD.employee_id, NOW());
END;
//

DELIMITER ;

Trigger Execution Order and Behavior

• If multiple triggers exist for the same event and timing on a table, MySQL executes them in creation order.
• Triggers execute in the same transaction as the triggering statement, ensuring atomicity.
• Recursive or cascading triggers are not directly supported; care must be taken to avoid infinite loops.

Common Use Cases for Triggers

• Auditing: Automatically logging changes to sensitive tables.
• Data Validation: Enforcing rules that cannot be enforced by constraints alone.
• Data Synchronization: Keeping denormalized or summary tables updated.
• Security: Preventing unauthorized or dangerous changes.
• Automated Defaults: Setting or modifying column values dynamically.

Limitations and Considerations

• Triggers cannot modify tables that activate the same trigger (no direct recursion).
• Triggers add overhead to DML operations, so use judiciously for performance.
• Triggers cannot return result sets directly to the client.
• Debugging triggers is more difficult than application code due to automatic invocation.
• MySQL permits only one trigger per event timing per table in versions before 5.7; later versions support multiple triggers.

Best Practices

• Keep trigger logic simple and efficient.
• Use triggers only when necessary—consider application-level logic for complex operations.
• Document triggers clearly to avoid surprises.
• Test triggers thoroughly, especially with concurrent transactions.
• Consider using audit tables and centralized logging for maintainability.

Examples of Complex Trigger Logic

Example: Maintaining a Running Total with Triggers

DELIMITER //

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE customers
    SET total_orders = total_orders + NEW.order_amount
    WHERE customer_id = NEW.customer_id;
END;
//

DELIMITER ;

Example: Enforcing Data Consistency Across Tables

DELIMITER //

CREATE TRIGGER before_inventory_update
BEFORE UPDATE ON inventory
FOR EACH ROW
BEGIN
    IF NEW.stock_quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock quantity cannot be negative';
    END IF;

    IF NEW.stock_quantity <> OLD.stock_quantity THEN
        INSERT INTO inventory_log(product_id, old_qty, new_qty, change_date)
        VALUES (NEW.product_id, OLD.stock_quantity, NEW.stock_quantity, NOW());
    END IF;
END;
//

DELIMITER ;

Managing and Dropping Triggers

To view triggers:

SHOW TRIGGERS;

To drop a trigger:

DROP TRIGGER trigger_name;

MySQL triggers are a powerful feature for automatic execution of SQL logic tied to data changes. Understanding the difference between BEFORE and AFTER triggers, and how they operate on INSERT, UPDATE, and DELETE events, allows you to enforce rules, maintain data integrity, and automate tasks directly in the database layer.

While triggers provide great power, they should be used with care due to their implicit execution and potential performance impact. Always design and test triggers thoughtfully within your application context.