Triggers & Scheduled Events

Code the database runs by itself

Everything so far ran because you typed a query. A trigger is different: it is a block of SQL the database runs automatically the moment a row is inserted, updated or deleted in a table — no application call needed. Triggers are how a database enforces rules and keeps audit trails on its own, so the logic can never be skipped no matter which app touches the table.

A trigger definition answers three questions: when (BEFORE or AFTER), which event (INSERT, UPDATE or DELETE), and on which table. Inside the trigger you can read the row being changed through two special references: NEW (the incoming row, available on INSERT/UPDATE) and OLD (the previous row, available on UPDATE/DELETE).

An audit-log trigger

A classic use is an audit log — a record of every change. Suppose we have an accounts table and a separate audit_log table. This trigger writes a log row automatically every time an account’s balance is updated:

DELIMITER //

CREATE TRIGGER log_balance_change
AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (account_id, old_balance, new_balance, changed_at)
  VALUES (OLD.id, OLD.balance, NEW.balance, NOW());
END //

DELIMITER ;

Walking through it (the DELIMITER // trick is the same one you met with stored procedures — it stops the inner semicolons ending the definition early): AFTER UPDATE ON accounts says “run this just after any row in accounts is updated”. FOR EACH ROW means it fires once per affected row. Inside, OLD.balance is the balance before the change and NEW.balance is the balance after — we insert both, plus the account id and the current time, into audit_log.

Now watch it fire. You run an ordinary UPDATE — and the log row appears without you writing any INSERT:

UPDATE accounts SET balance = 1500 WHERE id = 1;

SELECT * FROM audit_log;

You only ran the UPDATE. But because the trigger is attached to the table, MySQL also ran the trigger’s INSERT behind the scenes, capturing the old and new balance. The SELECT then shows the audit row that appeared on its own.

Scheduled events — jobs on a timer

A trigger reacts to a row change. Sometimes instead you want something to run on a schedule — clear out old sessions every night, refresh a summary table every hour. MySQL’s event scheduler runs a block of SQL on a timer, like a built-in cron job. First it must be switched on, then you create the event:

SET GLOBAL event_scheduler = ON;

CREATE EVENT delete_old_logs
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM audit_log WHERE changed_at < NOW() - INTERVAL 30 DAY;

Reading it: SET GLOBAL event_scheduler = ON turns on the background scheduler (off by default). CREATE EVENT delete_old_logs names the job. ON SCHEDULE EVERY 1 DAY sets how often it runs. DO is followed by the SQL to run — here a DELETE that removes any audit row older than 30 days (NOW() - INTERVAL 30 DAY is the date one month ago). You can also schedule a one-off with ON SCHEDULE AT '2026-12-31 23:59:00'.