How to Create Triggers in MySQL Database

I started using triggers almost by accident. I needed to keep an audit_log table in sync with every change made to a salaries table, and rather than trusting every application developer to remember to write that logging code in every single code path, I moved the responsibility into the database itself with a trigger. Once I saw how reliably that worked — no code path could accidentally skip it — triggers became one of my favorite tools for enforcing data integrity rules that absolutely must never be bypassed.

What a Trigger Is

A trigger is a named block of SQL code that MySQL automatically executes in response to a specific event — INSERT, UPDATE, or DELETE — on a specific table, either right before (BEFORE) or right after (AFTER) that event occurs. Triggers run inside the same transaction as the statement that fired them, so if the trigger fails, the original statement fails too.

Why Use Triggers

• Enforcing business rules the database must guarantee regardless of which application or script touches the data.
• Automatically maintaining audit trails or history tables.
• Keeping denormalized summary columns in sync (like a running total) without relying on application code discipline.
• Validating or normalizing data before it’s written, beyond what a CHECK constraint can express.

Basic Trigger Syntax

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

Every trigger in MySQL is a row-level trigger — it fires once per affected row, not once per statement. There’s no statement-level trigger option in MySQL, unlike some other database systems.

Example 1: Audit Logging with an AFTER UPDATE Trigger

CREATE TABLE salaries (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10,2)
);

CREATE TABLE salary_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at DATETIME
);

DELIMITER $$

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON salaries
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
        VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
    END IF;
END$$

DELIMITER ;

I switch the statement delimiter to $$ before defining the trigger, because the trigger body itself contains semicolons, and MySQL’s default client would otherwise interpret the first semicolon inside the body as ending the whole CREATE TRIGGER statement prematurely.

Now, whenever a salary changes:

INSERT INTO salaries VALUES (1, 50000.00);
UPDATE salaries SET salary = 55000.00 WHERE employee_id = 1;

SELECT * FROM salary_audit;

Output:

+----------+-------------+------------+------------+---------------------+
| audit_id | employee_id | old_salary | new_salary | changed_at           |
+----------+-------------+------------+------------+----------------------+
|        1 |           1 |   50000.00 |   55000.00 | 2026-07-30 10:15:02  |
+----------+-------------+------------+------------+----------------------+

Notice OLD refers to the row’s values before the change, and NEW refers to the row’s values after the change — both are available in UPDATE triggers. INSERT triggers only have NEW; DELETE triggers only have OLD.

Example 2: Validating Data with a BEFORE INSERT Trigger

DELIMITER $$

CREATE TRIGGER trg_validate_salary
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END$$

DELIMITER ;

INSERT INTO salaries VALUES (2, -500.00);

Output:

ERROR 1644 (45000): Salary cannot be negative

SIGNAL is how I raise a custom error from inside a trigger, complete with a message that’s far more useful to whoever’s debugging than a generic constraint violation. This BEFORE INSERT pattern is my go-to for enforcing business rules that a plain CHECK constraint can’t express (like cross-table validation).

Example 3: Maintaining a Running Total with AFTER INSERT and AFTER DELETE

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    quantity INT,
    unit_price DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    total DECIMAL(10,2) DEFAULT 0
);

DELIMITER $$

CREATE TRIGGER trg_update_order_total_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total = total + (NEW.quantity * NEW.unit_price)
    WHERE order_id = NEW.order_id;
END$$

CREATE TRIGGER trg_update_order_total_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total = total - (OLD.quantity * OLD.unit_price)
    WHERE order_id = OLD.order_id;
END$$

DELIMITER ;

This keeps orders.total automatically in sync every time a line item is added or removed, without any application code needing to remember to recalculate it.

BEFORE vs. AFTER Triggers

A BEFORE INSERT trigger can actually change what gets stored:

DELIMITER $$

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

DELIMITER ;

This guarantees every email stored is lowercase and trimmed, regardless of what the application sent.

Trigger Execution Flow

flowchart TD
    A[INSERT/UPDATE/DELETE statement issued] --> B{BEFORE trigger exists?}
    B -->|Yes| C[Execute BEFORE trigger logic]
    C --> D[Row written/modified in table]
    B -->|No| D
    D --> E{AFTER trigger exists?}
    E -->|Yes| F[Execute AFTER trigger logic]
    E -->|No| G[Statement complete]
    F --> G

Managing Triggers

-- List triggers on the current database
SHOW TRIGGERS;

-- Inspect a specific trigger's definition
SHOW CREATE TRIGGER trg_salary_audit;

-- Remove a trigger
DROP TRIGGER IF EXISTS trg_salary_audit;

MySQL allows only one trigger per table per event per timing (i.e., only one BEFORE INSERT, only one AFTER INSERT, and so on) unless you’re on MySQL 5.7.2+, which added support for multiple triggers on the same event/timing combination, ordered with FOLLOWS/PRECEDES.

CREATE TRIGGER trg_second_audit
AFTER UPDATE ON salaries
FOR EACH ROW
FOLLOWS trg_salary_audit
BEGIN
    -- additional logic
END;

Real-World DBA Scenarios

• Compliance audit trails: financial and healthcare systems I’ve worked on rely on AFTER triggers to guarantee every change to sensitive tables is logged, independent of which application or ad-hoc script made the change.
• Soft-delete enforcement: a BEFORE DELETE trigger that redirects deletes into an is_deleted flag update instead, for tables where hard deletes are disallowed by policy.
• Cache invalidation signals: an AFTER UPDATE trigger that writes to a lightweight cache_invalidation_queue table, which a background worker polls to know which cached objects need refreshing.
• Data consistency across denormalized tables: keeping a summary/reporting table in sync in real time without waiting for a nightly ETL job.

Common Pitfalls

• Performance overhead: every trigger adds work to every affected INSERT/UPDATE/DELETE. A trigger doing an expensive lookup or writing to multiple tables can noticeably slow down high-throughput write paths — I always benchmark before and after adding a trigger to a hot table.
• Hidden logic: triggers execute invisibly from the perspective of whoever wrote the original INSERT/UPDATE statement. I document every trigger clearly and keep the team aware of their existence, because “mystery” side effects that only show up in an audit table are a common source of confused debugging sessions.
• Recursive trigger risk: a trigger on table A that updates table B, which itself has a trigger that updates table A, can create infinite loops or unexpected cascades. MySQL doesn’t automatically prevent this — you need to design carefully.
• Cannot call routines that use their own transaction control: triggers can’t include COMMIT, ROLLBACK, or other transaction-control statements — they must operate within the transaction of the triggering statement.

Security Considerations

• Triggers execute with the privileges of the definer by default (DEFINER clause), which means a trigger can perform actions the invoking user might not have direct privileges for — I review DEFINER carefully to avoid accidental privilege escalation.
• Since triggers can’t be bypassed by application code, they’re genuinely useful as a last line of defense for data integrity rules, but they shouldn’t be the only validation layer — defense in depth still applies.

Troubleshooting Table

FAQs

Can a trigger call a stored procedure? Yes, a trigger can call a stored procedure, though the procedure cannot include explicit transaction-control statements like COMMIT/ROLLBACK.

Do triggers fire on multi-row statements? Yes — since triggers are row-level, an UPDATE affecting 500 rows fires the trigger 500 times, once per row.

Can I disable a trigger temporarily without dropping it? MySQL doesn’t have a native “disable trigger” command — the common workaround is dropping and recreating it, or adding a session variable check inside the trigger body to conditionally skip its logic.

Do triggers fire during a data import via LOAD DATA? Yes, LOAD DATA INFILE does fire triggers by default, unlike some other bulk-load mechanisms in other databases.

Can a trigger prevent a statement from executing? Yes — a BEFORE trigger that raises a SIGNAL with an error state stops the triggering statement (and any surrounding transaction, depending on error handling) from completing.

Interview Questions

1. What’s the difference between a BEFORE trigger and an AFTER trigger?
2. Can a MySQL trigger include COMMIT or ROLLBACK? Why or why not?
3. How would you use a trigger to enforce a business rule that a CHECK constraint can’t express?
4. What happens if a trigger’s logic causes an error partway through an UPDATE affecting 100 rows?
5. How does MySQL determine execution order when multiple triggers exist for the same event and timing?
6. What are OLD and NEW, and which are available for INSERT, UPDATE, and DELETE triggers respectively?
7. What risks does the DEFINER clause on a trigger introduce?

Optimization Tips

• Keep trigger logic minimal and fast — avoid expensive subqueries or cross-table joins inside a trigger body on high-write tables.
• Index any columns a trigger uses in its own internal WHERE clauses, exactly as you would for application queries.
• Batch-test trigger performance under realistic concurrent load before deploying to production, since row-level firing on bulk operations can multiply overhead quickly.
• Consider whether a scheduled event or asynchronous job might be more appropriate than a trigger for non-time-critical side effects, to avoid slowing down the primary write path.

Summary and Key Takeaways

Triggers let the database itself enforce rules and side effects that must never be skipped, regardless of which application, script, or careless developer touches the table directly. BEFORE triggers are for validating or normalizing incoming data; AFTER triggers are for auditing, cascading updates, and side effects once a change is confirmed. The tradeoff I always weigh is reliability versus performance and visibility — triggers guarantee consistency but add invisible overhead to every write and can surprise developers who don’t know they exist, so I document them thoroughly and keep their logic as lean as possible.

References

• MySQL 8.0 Reference Manual — Triggers
• MySQL 8.0 Reference Manual — CREATE TRIGGER Syntax
• MySQL 8.0 Reference Manual — Trigger Syntax and Examples
• MySQL 8.0 Reference Manual — SIGNAL Statement