MySQL Triggers MySQL · Triggers · Introduction

MySQL Trigger Introduction

Comprehensive introduction to MySQL triggers — what they are, row-level vs statement-level (MySQL only supports row-level), the six event/timing slots (BEFORE/AFTER × INSERT/UPDATE/DELETE), OLD and NEW pseudo-rows, the trade-offs of using triggers, MySQL-specific restrictions, and FOLLOWS/PRECEDES for ordering multiple triggers.

What is a Trigger?

A trigger is a stored program that runs automatically in response to changes on a table — specifically INSERT, UPDATE, or DELETE events. Unlike stored procedures (which you call explicitly with CALL) or stored functions (which you invoke inside expressions), triggers fire by themselves whenever the matching event happens, regardless of who or what caused the change.

Conceptually, a trigger is the database saying: "every time someone does X to this table, also do Y." That makes it very different from procedures and functions — it's an event handler attached to a table, not something an application code path explicitly invokes.

Row-Level vs Statement-Level

The SQL standard recognises two granularities of triggers:

Type	Fires	Use case
Row-level	Once for each row the statement affects	Per-row logic — auditing each change, validating each new row, deriving column values
Statement-level	Once for the whole statement, regardless of row count	Aggregate logic — "this UPDATE happened", logging the user/timestamp once per operation

⚠️ MySQL only supports row-level triggers. If a single UPDATE affects 100 rows, your trigger fires 100 times. Statement-level triggers (FOR EACH STATEMENT in the SQL standard, supported by PostgreSQL and others) don't exist in MySQL. Plan accordingly: heavy work inside a trigger gets multiplied by row count.

When Triggers Fire — BEFORE vs AFTER

Each trigger is attached to one of six event slots on a table — three event types (INSERT, UPDATE, DELETE) × two timings (BEFORE, AFTER):

Slot	Common use
`BEFORE INSERT`	Validate or modify new row data before it lands; reject bad inputs via `SIGNAL`
`AFTER INSERT`	Audit logging, derived-table updates, notifications
`BEFORE UPDATE`	Validate the new value, modify it, or compare to OLD; cancel via SIGNAL
`AFTER UPDATE`	Audit changes, recompute summary tables, sync materialized data
`BEFORE DELETE`	Archive the row to a "deleted" table; SIGNAL to block the deletion
`AFTER DELETE`	Cascade cleanup, log the deletion

The two timings have different powers:

	BEFORE triggers	AFTER triggers
Modify NEW values?	Yes — last chance to change the row	No — the row is already in the table
Cancel the operation?	Yes — via `SIGNAL`	Yes, but the operation already partially completed; usually rolled back via outer transaction
See the final stored row?	No — values are still being prepared	Yes — NEW reflects what's actually been written

OLD and NEW Pseudo-Rows

Inside a trigger body, two special row references give you access to the "before" and "after" data:

Event	`OLD` available?	`NEW` available?
INSERT	No	Yes — the row about to be / just inserted
UPDATE	Yes — the row's previous values	Yes — the row's new values
DELETE	Yes — the row about to be / just removed	No

Reference columns as OLD.column_name and NEW.column_name. In a BEFORE trigger you can assign to NEW.column to modify what gets stored — that's the canonical "fix data on the way in" pattern.

What Triggers Are Good For

Auditing changes. Every UPDATE writes a row to an audit log automatically — applications can't forget, and rogue queries can't bypass it.
Maintaining derived data. Sum tables, denormalized views, summary counts — kept in sync without application code remembering to touch them.
Enforcing complex constraints. Beyond what CHECK, FOREIGN KEY, and UNIQUE can express. "An employee can't have a salary higher than their manager's" — that's a trigger.
Defaulting computed columns. A BEFORE INSERT trigger that fills in NEW.full_name = CONCAT(NEW.first_name, ' ', NEW.last_name).
Cross-table cascading logic. Beyond what foreign-key cascades cover.

What Triggers Are Bad For

Triggers solve real problems but introduce real costs. Use them deliberately:

Invisible side effects. Run an UPDATE, watch other tables change unexpectedly. New developers reading the schema don't see triggers without specifically looking for them.
Hard to debug. No step debugger; failures appear as confusing errors mid-statement; EXPLAIN doesn't show trigger work.
Performance multiplier. A 100,000-row UPDATE fires the trigger 100,000 times. Each invocation has overhead even for trivial bodies.
Heavy bulk operations get heavier. Loading 10 million rows? Every INSERT trigger fires 10 million times. Disable triggers for bulk loads, or skip them entirely.
Replication can get tricky with non-deterministic triggers — same caveats as stored functions and the binary log.
Vendor lock-in. Trigger syntax differs significantly between MySQL, PostgreSQL, SQL Server, and Oracle. Migrating is a rewrite.
Limited scope of validation. NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints are simpler, faster, and clearer than trigger-based validation. Reach for them first.

💡 The modern stance: Use triggers for things they're genuinely good at — auditing, derived data, complex constraints — and resist using them as a substitute for application logic. The fact that you can put business rules in triggers doesn't always mean you should. Application-layer logic is testable, versioned alongside your code, and visible in pull requests.

Restrictions on MySQL Triggers

Triggers can't do everything. The notable limitations:

Restriction	Why
Cannot modify the same table that fired the trigger	Would cause infinite recursion; MySQL refuses
Cannot use `START TRANSACTION`, `COMMIT`, `ROLLBACK`	Triggers run inside the calling statement's transaction
Cannot return result sets via `SELECT` without `INTO`	The calling statement is the one returning data
Cannot call procedures that return result sets	Same reason — no place for a result set
Cannot use dynamic SQL (`PREPARE` / `EXECUTE`)	Disallowed inside triggers
Triggers don't fire for foreign-key cascades or `TRUNCATE`	FK cascades and TRUNCATE bypass the row-level INSERT/UPDATE/DELETE path
Triggers don't fire for changes via the `LOAD DATA INFILE` with `CONCURRENT` or replicated row events	Various special paths skip triggers — verify on your version

Trigger Order — FOLLOWS and PRECEDES

You can have multiple triggers on the same table for the same event/timing — for instance, two BEFORE INSERT triggers on employees. By default they run in creation order, but you can control execution order explicitly:

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name FOR EACH ROW {FOLLOWS | PRECEDES} other_trigger_name BEGIN ... END;

FOLLOWS — this trigger runs after the named one
PRECEDES — this trigger runs before the named one

Useful when you have separate teams or features adding triggers to the same table — you can stipulate ordering rather than relying on the accident of which CREATE ran first.

What's Next

The pages that follow walk through the practical mechanics:

Create Trigger — full CREATE TRIGGER syntax, OLD/NEW availability, listing and dropping triggers
Trigger Before Insert — validation patterns with SIGNAL
Trigger Before Update — auditing, transforming new values, comparing OLD to NEW
Trigger Before Delete — archiving rows before they're removed
Trigger After Insert — post-insert logging and downstream sync
Trigger After Update — change tracking with full before/after history
Trigger After Delete — post-delete cleanup and notifications

Conclusion

A trigger is a stored program that fires automatically in response to INSERT, UPDATE, or DELETE events on a specific table.
MySQL supports only row-level triggers — they fire once per affected row, not once per statement. Plan trigger work around this multiplier.
Six event/timing combinations: BEFORE/AFTER × INSERT/UPDATE/DELETE.
BEFORE triggers can modify NEW values and reject the operation; AFTER triggers see the final stored row but can't change it.
OLD and NEW pseudo-rows give access to the data — OLD available on UPDATE and DELETE, NEW available on INSERT and UPDATE.
Triggers are good for auditing, derived data, and complex constraints; bad as a substitute for application logic, and they multiply per-row in MySQL.
Restrictions: can't modify the same table, can't use transactions, can't use dynamic SQL, don't fire for cascades or TRUNCATE.
Multiple triggers on the same event/timing can be ordered with FOLLOWS / PRECEDES.