Triggers run automatically when rows change; events run on a schedule. Both let the database do housekeeping without any application code. Used sparingly, they are very effective. Used everywhere, they hide behaviour that nobody remembers. This lesson shows the good uses.
How triggers work
A trigger is attached to one table and fires for each row affected by an INSERT, UPDATE or DELETE, either BEFORE or AFTER the row is written. MySQL only has row-level triggers; there is no “once per statement” trigger.
Inside the trigger body, two pseudo-rows describe the change:
| Event | OLD | NEW |
|---|---|---|
INSERT | not available | The row being inserted |
UPDATE | The row before the change | The row after the change |
DELETE | The row being deleted | not available |
In a BEFORE trigger you can modify NEW with SET NEW.col = ... to change what gets stored. In an AFTER trigger the row is already written, so NEW is read-only.
BEFORE triggers: normalise and validate
Titles typed on phones often carry stray spaces. Clean them before they are stored, whoever inserts the note:
CREATE TRIGGER notes_bi_trim
BEFORE INSERT ON notes
FOR EACH ROW
SET NEW.title = TRIM(NEW.title);
A one-statement body needs no BEGIN ... END and no DELIMITER change. For several statements, use the delimiter technique from lesson 15. A BEFORE trigger can also reject a row with SIGNAL:
DELIMITER //
CREATE TRIGGER notes_bu_guard
BEFORE UPDATE ON notes
FOR EACH ROW
BEGIN
SET NEW.title = TRIM(NEW.title);
IF NEW.user_id <> OLD.user_id AND NEW.pinned THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unpin a note before moving it to another user';
END IF;
END //
DELIMITER ;
Simple single-row rules are usually better as CHECK constraints (lesson 12): they are declarative and visible in SHOW CREATE TABLE. Use triggers for rules a check cannot express, like comparing the old and new values above.
AFTER triggers: an audit log
Orbit’s support team wants to know when note titles change and when notes are deleted. Create a log table and two triggers:
CREATE TABLE note_audit (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
note_id INT UNSIGNED NOT NULL,
action ENUM('update', 'delete') NOT NULL,
old_title VARCHAR(200),
new_title VARCHAR(200),
changed_by VARCHAR(288) NOT NULL,
changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE TRIGGER notes_au_audit
AFTER UPDATE ON notes
FOR EACH ROW
BEGIN
IF NOT (NEW.title <=> OLD.title) THEN
INSERT INTO note_audit (note_id, action, old_title, new_title, changed_by)
VALUES (NEW.id, 'update', OLD.title, NEW.title, USER());
END IF;
END //
CREATE TRIGGER notes_ad_audit
AFTER DELETE ON notes
FOR EACH ROW
INSERT INTO note_audit (note_id, action, old_title, changed_by) VALUES (OLD.id, 'delete', OLD.title, USER()) //
DELIMITER ;
The <=> comparison from lesson 7 handles NULLs, and only real title changes are logged. USER() records the account that ran the statement; inside a trigger, CURRENT_USER() would return the trigger’s definer instead. Try it: rename a note, then rename it back.
UPDATE notes SET title = ' Groceries ' WHERE id = 1;
UPDATE notes SET title = 'Shopping list' WHERE id = 1;
UPDATE notes SET pinned = TRUE WHERE id = 1; -- no title change, not logged
SELECT note_id, action, old_title, new_title FROM note_audit ORDER BY id;
+---------+--------+---------------+---------------+
| note_id | action | old_title | new_title |
+---------+--------+---------------+---------------+
| 1 | update | Shopping list | Groceries |
| 1 | update | Groceries | Shopping list |
+---------+--------+---------------+---------------+
2 rows in set (0.00 sec)
The BEFORE UPDATE trigger trimmed ' Groceries ' before the AFTER UPDATE trigger saw it. The trigger’s writes are part of the same transaction as the triggering statement: if the UPDATE is rolled back, so is the audit row, and if the trigger fails, the statement fails.
Several triggers on one event
You can have more than one trigger for the same table, timing and event. They run in creation order unless you say otherwise:
CREATE TRIGGER notes_bi_default_body
BEFORE INSERT ON notes
FOR EACH ROW
FOLLOWS notes_bi_trim
SET NEW.body = IFNULL(NEW.body, '');
FOLLOWS and PRECEDES name an existing trigger to order against.
Limits and gotchas
- Foreign key cascades do not fire triggers. When deleting a user cascades to their notes,
notes_ad_auditdoes not run for those notes. If you need to audit them, add aBEFORE DELETEtrigger onusersas well, or delete the notes explicitly first. - Triggers cannot use
START TRANSACTION,COMMITorROLLBACK, cannot return result sets and cannot modify the table they are defined on (except throughNEWin aBEFOREtrigger). - They run for every row: a trigger that does a slow query turns a 10,000-row
UPDATEinto 10,000 slow queries. TRUNCATEdoes not fireDELETEtriggers.- They are easy to forget. Name them clearly (
table_timing_event_purpose, as above), document them, and keep their logic small.
Managing triggers:
SHOW TRIGGERS FROM orbit;
SHOW CREATE TRIGGER notes_au_audit\G
DROP TRIGGER IF EXISTS notes_bi_default_body;
There is no ALTER TRIGGER: drop and recreate. Since MySQL 8.0.29 you can write CREATE TRIGGER IF NOT EXISTS.
Scheduled events
The event scheduler runs SQL at given times, like cron inside MySQL. It is on by default since MySQL 8.0:
SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
Orbit keeps audit rows for 90 days. A daily event removes older ones in batches:
CREATE EVENT purge_note_audit
ON SCHEDULE EVERY 1 DAY
STARTS '2026-11-12 03:00:00'
COMMENT 'Keep 90 days of note audit history'
DO
DELETE FROM note_audit
WHERE changed_at < NOW() - INTERVAL 90 DAY
LIMIT 10000;
One-time events use AT, and drop themselves after running unless you add ON COMPLETION PRESERVE:
CREATE EVENT reset_demo_pin
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO UPDATE notes SET pinned = TRUE WHERE id = 1;
Events also make good refreshers for summary tables, for example rebuilding a daily_note_counts table every night so dashboards never run the heavy query themselves. For multi-statement bodies, wrap DO BEGIN ... END with the DELIMITER technique.
Managing events:
SHOW EVENTS FROM orbit;
ALTER EVENT purge_note_audit DISABLE;
ALTER EVENT purge_note_audit ENABLE;
ALTER EVENT purge_note_audit ON SCHEDULE EVERY 6 HOUR;
DROP EVENT IF EXISTS reset_demo_pin;
Things to know about events:
- They run with the privileges of their definer, in the time zone of the session that created them.
- Errors go to the error log only; nobody is notified. Have the event write to a log table, or monitor the effect.
- On replicas, events created on the source are copied in a disabled state (
SLAVESIDE_DISABLEDininformation_schema.EVENTS), so the job does not run twice. Their changes reach the replica through replication. - If a scheduled run is still busy when the next one starts, both run. Keep events short or guard them with
GET_LOCK().
For jobs that need retries, alerting or coordination across servers, an external scheduler (cron, systemd timers, Kubernetes CronJobs) is often the better home.
Quick reference
| Task | SQL |
|---|---|
| Row trigger | CREATE TRIGGER name BEFORE/AFTER INSERT/UPDATE/DELETE ON t FOR EACH ROW ... |
| Change the incoming row | SET NEW.col = ... (BEFORE only) |
| Reject a row | SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...' |
| Order triggers | FOLLOWS other_trigger / PRECEDES other_trigger |
| List triggers | SHOW TRIGGERS FROM db; |
| Recurring job | CREATE EVENT e ON SCHEDULE EVERY 1 DAY STARTS '...' DO ...; |
| One-time job | CREATE EVENT e ON SCHEDULE AT NOW() + INTERVAL 1 HOUR DO ...; |
| Pause a job | ALTER EVENT e DISABLE; |
Exercise
Deleting a user cascades to their notes. Does an AFTER DELETE trigger on notes fire for those notes?
Show answer
Answer: B. Cascading actions are performed inside InnoDB and bypass the trigger layer. Audit them with a trigger on the parent table or delete the children explicitly.
Exercise
In which trigger can you change the values being stored with SET NEW.title = …?
Show answer
Answer: C. In AFTER triggers the row has already been written, so NEW is read-only. BEFORE triggers can modify NEW.
Exercise
An event created on the replication source appears on a replica. What state is it in there?
Show answer
Answer: A. Events are replicated with status SLAVESIDE_DISABLED. The source runs them and the resulting changes replicate normally.
Key takeaways
- Triggers are row-level, with
OLDandNEW;BEFOREtriggers can modifyNEWor reject the row. - Trigger writes share the transaction of the statement that fired them.
- Foreign key cascades and
TRUNCATEdo not fire triggers. - Prefer
CHECKconstraints for simple rules; keep trigger logic small and named clearly. - The event scheduler runs recurring and one-time SQL jobs; replicas keep replicated events disabled.
FAQ
Are triggers a good way to keep counters, like notes per user?
They work, but every insert and delete then updates the same counter row, which becomes a lock hot spot under heavy write load. For dashboards, a periodic summary built by an event, or COUNT(*) on an index, is often simpler and fast enough.
Can a trigger call a stored procedure?
Yes, with CALL, as long as the procedure does not return result sets or manage transactions. Keep in mind it runs once per affected row.
Why did my event not run?
Check that event_scheduler is ON, that the event is ENABLED in SHOW EVENTS, that its definer account still exists and has the needed privileges, and look at the error log. Also check the server’s idea of the current time and time zone.
Should audit logs live in MySQL?
For moderate volumes a trigger-fed table like note_audit works well. At scale, change data capture from the binary log (with tools such as Debezium) moves the history out of the main database without per-row trigger cost.