How to Create and Manage MySQL Database Triggers

The first trigger I ever wrote in production was an audit log — every time a row in a salaries table changed, I wanted an automatic, tamper-resistant record of the old value, the new value, and who made the change. Doing that reliably in application code meant trusting every single code path that touched that table to remember to log the change. A single AFTER UPDATE trigger made that guarantee at the database level instead, and it’s never once been forgotten since, because it isn’t optional — it fires no matter what touches the table.

That’s the appeal of triggers, and also their danger: they run automatically and invisibly from the perspective of whoever wrote the query that caused them. This guide covers how to use them well.

What a Trigger Is

A trigger is a named block of SQL logic that automatically executes in response to an INSERT, UPDATE, or DELETE event on a specific table, either before or after the event occurs.

graph TD
    A[Application Executes INSERT/UPDATE/DELETE] --> B{Trigger Defined?}
    B -->|Yes - BEFORE| C[BEFORE Trigger Fires]
    C --> D[Actual Row Operation Executes]
    B -->|Yes - AFTER| D
    D --> E{AFTER Trigger Defined?}
    E -->|Yes| F[AFTER Trigger Fires]
    E -->|No| G[Statement Complete]
    F --> G

Trigger Timing and Events

MySQL supports six trigger combinations per table: BEFORE/AFTER combined with INSERT/UPDATE/DELETE.

Basic Trigger Syntax

DELIMITER $$

CREATE TRIGGER before_product_insert
BEFORE INSERT 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 ;

Testing it:

INSERT INTO products (product_name, price) VALUES ('Broken Item', -5.00);

ERROR 1644 (45000): Price cannot be negative

The SIGNAL statement is how you raise a custom error from inside a trigger — a clean way to enforce validation logic that every INSERT must pass through, regardless of which application or script is doing the inserting.

Using NEW and OLD

Inside a trigger, NEW refers to the row being inserted/updated, and OLD refers to the row’s previous state (available for UPDATE and DELETE triggers).

DELIMITER $$

CREATE TRIGGER audit_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit_log (employee_id, old_salary, new_salary, changed_by, changed_at)
        VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_USER(), NOW());
    END IF;
END$$

DELIMITER ;

UPDATE employees SET salary = 85000 WHERE employee_id = 302;
SELECT * FROM salary_audit_log WHERE employee_id = 302;

+-------------+------------+------------+---------------------+---------------------+
| employee_id | old_salary | new_salary | changed_by           | changed_at          |
+-------------+------------+------------+---------------------+---------------------+
| 302         | 72000.00   | 85000.00   | app_user@10.0.0.5    | 2026-07-29 14:22:01 |
+-------------+------------+------------+---------------------+---------------------+

Note: NEW is not available on DELETE triggers, and OLD is not available on INSERT triggers — there’s simply no “previous state” for a new row or “new state” for a deleted one.

Modifying Data Before It’s Stored

DELIMITER $$

CREATE TRIGGER normalize_email_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(TRIM(NEW.email));
END$$

DELIMITER ;

INSERT INTO users (email) VALUES ('  John.Doe@EXAMPLE.com  ');
SELECT email FROM users WHERE user_id = LAST_INSERT_ID();

+---------------------+
| email               |
+---------------------+
| john.doe@example.com|
+---------------------+

Only BEFORE triggers can modify NEW values before they’re written — AFTER triggers can read NEW but changes to it there have no effect on the already-committed row.

Cascading Updates to a Denormalized Table

DELIMITER $$

CREATE TRIGGER update_order_total_after_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = (
        SELECT SUM(quantity * unit_price)
        FROM order_items
        WHERE order_id = NEW.order_id
    )
    WHERE order_id = NEW.order_id;
END$$

DELIMITER ;

This keeps a denormalized total_amount column on orders automatically in sync whenever line items change, avoiding the need to recalculate it in every application code path that touches order items.

Managing Triggers

List triggers:

SHOW TRIGGERS FROM mydatabase;

View a specific trigger’s definition:

SHOW CREATE TRIGGER audit_salary_update;

Drop a trigger:

DROP TRIGGER IF EXISTS audit_salary_update;

MySQL doesn’t support ALTER TRIGGER for changing logic — like functions, you drop and recreate.

Multiple Triggers on the Same Event

Since MySQL 5.7.2+, you can define multiple triggers for the same table/timing/event, and control their execution order:

CREATE TRIGGER audit_trigger_1
BEFORE UPDATE ON employees
FOR EACH ROW
FOLLOWS audit_trigger_0
BEGIN
    -- logic here
END;

FOLLOWS/PRECEDES let you control ordering explicitly, which matters when multiple independent triggers exist on the same table and their execution order affects outcomes.

Real-World Scenario: Enforcing Referential Business Rules

A pattern I’ve used in inventory systems: preventing stock from going negative, which a simple foreign key or CHECK constraint can’t express because it depends on cross-row aggregation.

DELIMITER $$

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

DELIMITER ;

Any application, script, or manual query that tries to push inventory below zero is stopped at the database layer — a guarantee that’s much stronger than “we remember to check this in every service.”

Soft-Delete Archiving with Triggers

DELIMITER $$

CREATE TRIGGER archive_deleted_orders
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_archive 
    SELECT OLD.*, NOW() AS archived_at;
END$$

DELIMITER ;

This means even a hard DELETE against the live table automatically preserves a copy — useful for compliance requirements where data must be retained even if operationally removed from the active table.

Performance Considerations

Triggers run inline with the statement that fires them, inside the same transaction — this means:

• A slow trigger makes every INSERT/UPDATE/DELETE on that table slow, even ones that seem unrelated to the trigger’s specific purpose.
• Triggers that themselves write to other tables (like the audit log examples above) add extra write I/O per row affected — for bulk operations affecting millions of rows, this compounds significantly.
• FOR EACH ROW means the trigger body executes once per affected row, not once per statement — a bulk UPDATE affecting 50,000 rows runs the trigger body 50,000 times.

-- Check trigger overhead with a benchmark comparison
SET profiling = 1;
UPDATE employees SET department = 'Sales' WHERE department = 'Marketing';
SHOW PROFILES;

For very large bulk operations, consider whether disabling a non-critical trigger temporarily (not directly supported in standard MySQL without dropping/recreating, but achievable with a session-level guard variable inside the trigger body) and running an equivalent batch process afterward is more appropriate than paying the per-row cost during the load.

-- Common pattern: session variable guard to bypass trigger during controlled bulk loads
DELIMITER $$

CREATE TRIGGER audit_guarded
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF @skip_audit IS NULL THEN
        INSERT INTO salary_audit_log (employee_id, old_salary, new_salary)
        VALUES (NEW.employee_id, OLD.salary, NEW.salary);
    END IF;
END$$

DELIMITER ;

-- During a controlled bulk import:
SET @skip_audit = 1;
-- bulk operation here
SET @skip_audit = NULL;

Security Considerations

• Triggers execute with the privileges of the trigger’s definer by default — review who created sensitive triggers and whether that grants unintended elevated access to callers.
• Because triggers fire silently from the caller’s perspective, they can obscure what’s actually happening to data during an incident investigation — document trigger logic clearly and keep it discoverable (SHOW TRIGGERS, version-controlled migration scripts) rather than tribal knowledge.
• Avoid embedding sensitive logic (like credential checks) inside triggers where behavior is easy to overlook during a security review — keep authorization logic in the application layer where it’s more visible and testable.

Troubleshooting Common Issues

“Can’t update table in stored function/trigger because it is already used by statement.” This happens when a trigger tries to modify the very table that fired it — MySQL disallows this to prevent infinite recursion. Redesign to use a different table or a deferred process instead.

Trigger silently not firing. Confirm you’re using the exact event/timing combination expected (SHOW TRIGGERS), and remember TRUNCATE TABLE does not fire DELETE triggers, since it’s implemented as a DDL-style operation rather than row-by-row deletion.

Unexpected performance degradation after adding a trigger. Profile with SHOW PROFILES or performance_schema to isolate whether the trigger itself, or a subquery/write inside it, is the bottleneck — often it’s an unindexed lookup inside the trigger body.

Cascading trigger chains causing unexpected behavior. When Trigger A’s actions cause Trigger B to fire on another table, which in turn causes further side effects, debugging gets complex fast. Keep trigger chains shallow and well-documented.

Frequently Asked Questions

Do triggers fire during LOAD DATA INFILE? Yes, INSERT triggers fire per row loaded via LOAD DATA INFILE, which is important to remember for performance planning on large bulk loads.

Does TRUNCATE TABLE fire DELETE triggers? No — TRUNCATE is treated as a DDL operation and bypasses row-level triggers entirely. Use DELETE FROM table instead if trigger-based side effects (like archiving) are required.

Can a trigger call a stored procedure? No, MySQL triggers cannot directly CALL a stored procedure, though they can execute the equivalent logic inline or call a stored function.

How do I see all triggers affecting a specific table? SHOW TRIGGERS FROM database_name LIKE 'table_name'; or query INFORMATION_SCHEMA.TRIGGERS directly for more detail.

Interview Questions

1. What’s the difference between a BEFORE and an AFTER trigger, and when would you use each?
2. Why can’t a trigger modify the same table that caused it to fire?
3. Explain the difference between NEW and OLD and their availability across INSERT/UPDATE/DELETE triggers.
4. Why doesn’t TRUNCATE TABLE fire DELETE triggers, and what are the practical implications of that?
5. How would you design an audit-logging system using triggers, and what are the performance tradeoffs versus application-level logging?
6. How do FOLLOWS and PRECEDES affect multiple triggers defined on the same event?

Summary and Key Takeaways

• Triggers automatically execute logic in response to INSERT, UPDATE, or DELETE, enforcing rules and side effects at the database layer rather than relying on every application code path to remember them.
• NEW and OLD give access to the incoming and previous row state, with availability depending on the trigger’s event type.
• BEFORE triggers can modify incoming data; AFTER triggers are better suited for logging and cascading effects after a change is committed.
• Triggers run inline with the firing statement and execute per row, so they can meaningfully affect bulk operation performance — plan accordingly.
• TRUNCATE TABLE bypasses DELETE triggers entirely, a common source of confusion.
• Use triggers for guarantees that must always hold regardless of which code touches the table; keep them well-documented since their execution is otherwise invisible to whoever wrote the triggering statement.

Triggers are a genuinely powerful tool for guaranteeing consistency, but that power comes with an obligation to document them clearly — nothing is more frustrating during an incident than data changing in ways nobody in the room remembers configuring.

References

• MySQL 8.0 Reference Manual: CREATE TRIGGER Statement
• MySQL 8.0 Reference Manual: Trigger Syntax and Examples
• MySQL 8.0 Reference Manual: TRUNCATE TABLE Statement