Lesson 16 intermediate

Triggers and Scheduled Events

React to data changes and run jobs inside MySQL: BEFORE and AFTER triggers on INSERT, UPDATE and DELETE, the NEW and OLD rows, validating and normalising data, an audit log for Orbit's notes, trigger order with FOLLOWS and PRECEDES, the limits of triggers (including foreign key cascades that do not fire them), and the event scheduler for recurring and one-time jobs.

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:

EventOLDNEW
INSERTnot availableThe row being inserted
UPDATEThe row before the changeThe row after the change
DELETEThe row being deletednot 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_audit does not run for those notes. If you need to audit them, add a BEFORE DELETE trigger on users as well, or delete the notes explicitly first.
  • Triggers cannot use START TRANSACTION, COMMIT or ROLLBACK, cannot return result sets and cannot modify the table they are defined on (except through NEW in a BEFORE trigger).
  • They run for every row: a trigger that does a slow query turns a 10,000-row UPDATE into 10,000 slow queries.
  • TRUNCATE does not fire DELETE triggers.
  • 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_DISABLED in information_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

TaskSQL
Row triggerCREATE TRIGGER name BEFORE/AFTER INSERT/UPDATE/DELETE ON t FOR EACH ROW ...
Change the incoming rowSET NEW.col = ... (BEFORE only)
Reject a rowSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...'
Order triggersFOLLOWS other_trigger / PRECEDES other_trigger
List triggersSHOW TRIGGERS FROM db;
Recurring jobCREATE EVENT e ON SCHEDULE EVERY 1 DAY STARTS '...' DO ...;
One-time jobCREATE EVENT e ON SCHEDULE AT NOW() + INTERVAL 1 HOUR DO ...;
Pause a jobALTER EVENT e DISABLE;

Exercise

Deleting a user cascades to their notes. Does an AFTER DELETE trigger on notes fire for those notes?

  1. Yes, once per deleted note
  2. No, InnoDB foreign key cascades do not fire triggers
  3. Yes, but only once for the whole cascade
  4. Only if the trigger is declared FOR EACH STATEMENT
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 = …?

  1. AFTER INSERT
  2. AFTER UPDATE
  3. BEFORE INSERT and BEFORE UPDATE
  4. Any trigger, as long as it uses BEGIN … END
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?

  1. Disabled on the replica, so it does not run twice
  2. Enabled, running on both servers
  3. It is not replicated at all
  4. Enabled, but only runs when the source is down
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 OLD and NEW; BEFORE triggers can modify NEW or reject the row.
  • Trigger writes share the transaction of the statement that fired them.
  • Foreign key cascades and TRUNCATE do not fire triggers.
  • Prefer CHECK constraints 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.

↑ ↓ navigate Enter open Esc close