Chapter 9

Chapter 9 — Triggers and Scheduled Events

Triggers are stored programs that MySQL runs automatically whenever a specific data-change event occurs on a table — no application code involved, no CALL needed. Scheduled events are stored programs that MySQL runs on a clock — once at a future time, or repeatedly on an interval.

Together they move certain kinds of logic directly into the database engine: audit trails, constraint enforcement beyond what CHECK can express, derived-column maintenance, and background maintenance tasks like expiring old rows or refreshing summary tables.

Power and responsibility: Triggers and events run invisibly to application code. A developer INSERT-ing a row has no obvious way to know a trigger is also writing to three other tables. Always document them, keep them focused, and treat the "magic invisible side effect" quality as a cost — not a feature — to be used sparingly.

Part 1 — Triggers

1. Trigger Syntax and Timing

CREATE [OR REPLACE] TRIGGER trigger_name { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON table_name FOR EACH ROW [{ FOLLOWS | PRECEDES } other_trigger_name] trigger_body

Each combination of timing (BEFORE/AFTER) and event (INSERT/UPDATE/DELETE) is a distinct trigger slot. MySQL 5.7+ allows multiple triggers per slot — ordered with FOLLOWS / PRECEDES.

INSERT UPDATE DELETE
BEFORE ✓ Validate or modify data before the row is written ✓ Block invalid changes; normalise values ✓ Check business rules before the delete
AFTER ✓ Write to audit log; update summary tables ✓ Propagate changes to related tables ✓ Cascade soft-delete; archive removed rows

OLD and NEW pseudo-rows

INSERT
NEW.col — the value being inserted
OLD — does not exist
UPDATE
NEW.col — value after the update
OLD.col — value before the update
DELETE
OLD.col — the value being deleted
NEW — does not exist

In a BEFORE trigger you can assign to NEW.col to change the value that will actually be written. In an AFTER trigger NEW is read-only — the row is already committed.

2. Practical Trigger Patterns

Pattern 1 — Audit log (AFTER INSERT / UPDATE / DELETE)

First, create the audit table:

CREATE TABLE IF NOT EXISTS books_audit ( audit_id INT PRIMARY KEY AUTO_INCREMENT, action ENUM('INSERT','UPDATE','DELETE') NOT NULL, book_id INT NOT NULL, old_price DECIMAL(10,2), new_price DECIMAL(10,2), old_stock INT, new_stock INT, changed_by VARCHAR(100) DEFAULT USER(), changed_at DATETIME DEFAULT NOW() );
DELIMITER $$ -- AFTER INSERT CREATE OR REPLACE TRIGGER trg_books_after_insert AFTER INSERT ON books FOR EACH ROW BEGIN INSERT INTO books_audit (action, book_id, new_price, new_stock) VALUES ('INSERT', NEW.book_id, NEW.price, NEW.stock_quantity); END$$ -- AFTER UPDATE CREATE OR REPLACE TRIGGER trg_books_after_update AFTER UPDATE ON books FOR EACH ROW BEGIN -- Only log if price or stock actually changed IF NEW.price <> OLD.price OR NEW.stock_quantity <> OLD.stock_quantity THEN INSERT INTO books_audit (action, book_id, old_price, new_price, old_stock, new_stock) VALUES ('UPDATE', NEW.book_id, OLD.price, NEW.price, OLD.stock_quantity, NEW.stock_quantity); END IF; END$$ -- AFTER DELETE CREATE OR REPLACE TRIGGER trg_books_after_delete AFTER DELETE ON books FOR EACH ROW BEGIN INSERT INTO books_audit (action, book_id, old_price, old_stock) VALUES ('DELETE', OLD.book_id, OLD.price, OLD.stock_quantity); END$$ DELIMITER ;

Pattern 2 — Validate and normalise with BEFORE INSERT / UPDATE

BEFORE triggers can modify NEW values before they hit the table, or signal an error to reject the write entirely:

DELIMITER $$ CREATE OR REPLACE TRIGGER trg_books_before_insert BEFORE INSERT ON books FOR EACH ROW BEGIN -- Reject negative prices IF NEW.price < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative'; END IF; -- Clamp stock to zero if a negative value is supplied IF NEW.stock_quantity < 0 THEN SET NEW.stock_quantity = 0; END IF; -- Trim whitespace from title SET NEW.title = TRIM(NEW.title); END$$ CREATE OR REPLACE TRIGGER trg_books_before_update BEFORE UPDATE ON books FOR EACH ROW BEGIN IF NEW.price < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative'; END IF; IF NEW.stock_quantity < 0 THEN SET NEW.stock_quantity = 0; END IF; END$$ DELIMITER ;
SIGNAL SQLSTATE '45000' raises a user-defined error. 45000 is the reserved state for generic application errors. The error propagates back to the caller exactly like any other MySQL error — the INSERT or UPDATE is rolled back and the application receives an exception.

Pattern 3 — Maintain a derived column automatically

Keep a full_name column on the authors table in sync without relying on application code to remember:

-- Add the derived column (or use a generated column in MySQL 5.7+) ALTER TABLE authors ADD COLUMN full_name VARCHAR(200) AS (CONCAT(first_name, ' ', last_name)) STORED; -- MySQL's generated column syntax makes this specific example redundant, -- but the same BEFORE trigger pattern applies to any non-trivial derivation: DELIMITER $$ CREATE OR REPLACE TRIGGER trg_orders_before_insert BEFORE INSERT ON orders FOR EACH ROW BEGIN -- Auto-compute order total from line items already in a staging table -- (illustrative pattern — adapt to your schema) SET NEW.total_amount = ( SELECT COALESCE(SUM(quantity * unit_price), 0) FROM order_items_staging WHERE session_id = NEW.session_id ); END$$ DELIMITER ;

Pattern 4 — Cascade soft-delete (AFTER DELETE)

When a customer is deleted, archive their orders rather than hard-deleting them:

CREATE TABLE IF NOT EXISTS orders_archive LIKE orders; ALTER TABLE orders_archive ADD COLUMN archived_at DATETIME DEFAULT NOW(), ADD COLUMN archived_reason VARCHAR(200); DELIMITER $$ CREATE OR REPLACE TRIGGER trg_customers_after_delete AFTER DELETE ON customers FOR EACH ROW BEGIN -- Move the deleted customer's orders into the archive INSERT INTO orders_archive (order_id, customer_id, order_date, status, total_amount, archived_reason) SELECT order_id, customer_id, order_date, status, total_amount, CONCAT('Customer ', OLD.customer_id, ' deleted') FROM orders WHERE customer_id = OLD.customer_id; END$$ DELIMITER ;

Pattern 5 — Maintain a running stock count on order_items

DELIMITER $$ -- Decrement stock when an order line is inserted CREATE OR REPLACE TRIGGER trg_order_items_after_insert AFTER INSERT ON order_items FOR EACH ROW BEGIN UPDATE books SET stock_quantity = stock_quantity - NEW.quantity WHERE book_id = NEW.book_id; END$$ -- Restore stock when an order line is deleted (cancellation) CREATE OR REPLACE TRIGGER trg_order_items_after_delete AFTER DELETE ON order_items FOR EACH ROW BEGIN UPDATE books SET stock_quantity = stock_quantity + OLD.quantity WHERE book_id = OLD.book_id; END$$ -- Handle quantity changes on existing lines CREATE OR REPLACE TRIGGER trg_order_items_after_update AFTER UPDATE ON order_items FOR EACH ROW BEGIN IF NEW.quantity <> OLD.quantity THEN UPDATE books SET stock_quantity = stock_quantity - (NEW.quantity - OLD.quantity) WHERE book_id = NEW.book_id; END IF; END$$ DELIMITER ;

3. Trigger Dangers

Triggers are invisible to application developers. An INSERT that takes 50ms instead of 1ms, or a DELETE that inexplicably fails a foreign key constraint on a different table, is extremely difficult to debug if the developer doesn't know a trigger exists. Always document triggers prominently.
  • No triggers on triggers. A trigger cannot directly fire another trigger on the same table. It can fire a trigger on a different table — creating cascade chains that are very hard to trace.
  • Triggers slow down writes. Every affected row runs the trigger body. A BEFORE UPDATE trigger on a table that receives 10,000 updates per second is now 10,000 extra operations per second.
  • Bulk operations pay per row. UPDATE books SET price = price * 1.1 with an AFTER UPDATE trigger runs the trigger once per matching row — not once for the whole statement. A million-row update fires the trigger a million times.
  • SIGNAL is your friend for validation. Without SIGNAL, a BEFORE trigger that silently discards an invalid value gives the caller no feedback. Always SIGNAL for true constraint violations.
  • Avoid long-running work in triggers. Triggers run inside the originating transaction. Any work they do holds the same locks. A trigger that sends an HTTP request or sleeps will block other transactions for the duration.
Test triggers with SHOW TRIGGERS and manual testing. After writing a trigger, explicitly INSERT/UPDATE/DELETE a test row and verify the side effect. Triggers with a bug fail silently in surprising ways — the original write may still succeed while the trigger's secondary write silently errors (unless you have a handler or the error propagates).

4. Inspecting and Dropping Triggers

-- List all triggers on the current database SHOW TRIGGERS [FROM bookshop]\G -- Filter by table SHOW TRIGGERS WHERE `Table` = 'books'\G -- Full information_schema query SELECT trigger_name, event_manipulation AS event, action_timing AS timing, event_object_table AS `table`, action_order, created FROM information_schema.triggers WHERE trigger_schema = DATABASE() ORDER BY event_object_table, action_timing, event_manipulation, action_order; -- Show a trigger's body SHOW CREATE TRIGGER trg_books_after_update\G -- Drop a trigger DROP TRIGGER IF EXISTS trg_books_after_insert; DROP TRIGGER IF EXISTS trg_books_after_update; DROP TRIGGER IF EXISTS trg_books_after_delete;

Part 2 — Scheduled Events

5. The Event Scheduler

MySQL's event scheduler is a background thread that fires stored SQL on a clock. Think of it as a cron job that lives inside the database — no OS-level cron, no external script, no network connection needed.

-- Check scheduler status SHOW VARIABLES LIKE 'event_scheduler'; -- Enable it for this session (or add to my.cnf to persist across restarts) SET GLOBAL event_scheduler = ON; -- Persist across restarts — add to [mysqld] section in my.cnf: -- event_scheduler = ON
The scheduler is OFF by default. Events defined while it is off are stored but never fire. Always verify event_scheduler = ON in production. On managed databases (AWS RDS, Google Cloud SQL) the parameter group or flags control this — check your provider's docs.

One-time vs recurring events

One-time (AT)

Fires once at a specific date/time. MySQL can optionally drop it automatically afterwards with ON COMPLETION NOT PRESERVE.

Recurring (EVERY)

Fires on a repeating interval — seconds, minutes, hours, days, weeks, months. Can have an optional start time and end time.

CREATE [OR REPLACE] EVENT [IF NOT EXISTS] event_name ON SCHEDULE { AT timestamp | EVERY interval [STARTS timestamp] [ENDS timestamp] } [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'description'] DO event_body;

6. Practical Event Patterns

Pattern 1 — Nightly sales summary table

Refresh a denormalised summary table every night at 02:00 so daytime reports hit a pre-computed table instead of joining millions of order rows:

CREATE TABLE IF NOT EXISTS daily_sales_summary ( summary_date DATE PRIMARY KEY, orders_placed INT NOT NULL DEFAULT 0, units_sold INT NOT NULL DEFAULT 0, revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00, refreshed_at DATETIME ); DELIMITER $$ CREATE OR REPLACE EVENT evt_nightly_sales_summary ON SCHEDULE EVERY 1 DAY STARTS (DATE(NOW()) + INTERVAL 1 DAY + INTERVAL 2 HOUR) -- next 02:00 ON COMPLETION PRESERVE -- keep the event definition after it fires ENABLE COMMENT 'Rebuild daily_sales_summary for the previous day' DO BEGIN INSERT INTO daily_sales_summary (summary_date, orders_placed, units_sold, revenue, refreshed_at) SELECT DATE(o.order_date) AS summary_date, COUNT(DISTINCT o.order_id) AS orders_placed, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.unit_price) AS revenue, NOW() FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE DATE(o.order_date) = CURDATE() - INTERVAL 1 DAY ON DUPLICATE KEY UPDATE orders_placed = VALUES(orders_placed), units_sold = VALUES(units_sold), revenue = VALUES(revenue), refreshed_at = VALUES(refreshed_at); END$$ DELIMITER ;

Pattern 2 — Hourly expired session cleanup

CREATE OR REPLACE EVENT evt_purge_expired_sessions ON SCHEDULE EVERY 1 HOUR ON COMPLETION PRESERVE COMMENT 'Delete login sessions older than 24 hours' DO DELETE FROM user_sessions WHERE created_at < NOW() - INTERVAL 24 HOUR;
Batch deletes in events. If the table is large, a single DELETE that wipes millions of rows holds a heavy lock. Use a loop procedure called from the event to delete in batches of a few thousand rows at a time, sleeping briefly between each batch.

Pattern 3 — One-time future job

-- Run a specific task at exactly one future moment then discard the event CREATE EVENT evt_launch_day_price_reset ON SCHEDULE AT '2026-09-01 09:00:00' ON COMPLETION NOT PRESERVE -- auto-drop after it fires DO UPDATE books SET price = price * 1.05 -- 5% price rise on 1 Sept WHERE publisher_id = 3;

Pattern 4 — Weekly review request emails (via a log table)

Events can't send emails directly — but they can write to a queue table that an external process polls:

CREATE TABLE IF NOT EXISTS email_queue ( queue_id INT PRIMARY KEY AUTO_INCREMENT, to_email VARCHAR(200) NOT NULL, subject VARCHAR(255) NOT NULL, body TEXT, queued_at DATETIME DEFAULT NOW(), sent_at DATETIME ); CREATE OR REPLACE EVENT evt_weekly_review_requests ON SCHEDULE EVERY 1 WEEK STARTS '2026-06-16 08:00:00' -- first Monday ON COMPLETION PRESERVE DO -- Queue a nudge for customers who bought something in the last week -- but haven't left a review INSERT INTO email_queue (to_email, subject) SELECT DISTINCT c.email, 'How was your recent purchase from the Bookshop?' FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id LEFT JOIN reviews r ON r.book_id = oi.book_id AND r.customer_id = c.customer_id WHERE o.order_date >= CURDATE() - INTERVAL 7 DAY AND r.review_id IS NULL;

7. Managing Events

-- List all events SHOW EVENTS [FROM bookshop]\G -- Detailed query via information_schema SELECT event_name, status, execute_at, interval_value, interval_field, starts, ends, last_executed, event_comment FROM information_schema.events WHERE event_schema = DATABASE() ORDER BY event_name; -- See the full event body SHOW CREATE EVENT evt_nightly_sales_summary\G -- Disable an event (keep definition, stop firing) ALTER EVENT evt_nightly_sales_summary DISABLE; -- Re-enable ALTER EVENT evt_nightly_sales_summary ENABLE; -- Change schedule ALTER EVENT evt_purge_expired_sessions ON SCHEDULE EVERY 30 MINUTE; -- Drop DROP EVENT IF EXISTS evt_launch_day_price_reset;

Quick Reference

ConceptSyntax / ruleNotes
Create trigger CREATE OR REPLACE TRIGGER name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table FOR EACH ROW BEGIN … END One trigger per timing+event combination per table (multiple allowed with FOLLOWS/PRECEDES).
NEW / OLD NEW.col (INSERT, UPDATE) · OLD.col (UPDATE, DELETE) Assign to NEW in BEFORE trigger to change the written value. NEW is read-only in AFTER.
Raise an error SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '…'; Rolls back the triggering statement. Always SIGNAL for genuine constraint violations.
Trigger ordering FOLLOWS other_trigger / PRECEDES other_trigger Controls execution order when multiple triggers exist for the same event. MySQL 5.7+.
Inspect triggers SHOW TRIGGERS [FROM db]; · SHOW CREATE TRIGGER name; Also available in information_schema.triggers.
Drop trigger DROP TRIGGER IF EXISTS name; Triggers are dropped with the table when the table is dropped.
Enable scheduler SET GLOBAL event_scheduler = ON; · add event_scheduler=ON to my.cnf Off by default. Events are stored but never fire while it is off.
One-time event ON SCHEDULE AT 'YYYY-MM-DD HH:MM:SS' Use ON COMPLETION NOT PRESERVE to auto-drop after firing.
Recurring event ON SCHEDULE EVERY n {SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR} [STARTS …] [ENDS …] Use ON COMPLETION PRESERVE so the definition survives after each run.
Disable / enable event ALTER EVENT name DISABLE; · ALTER EVENT name ENABLE; Useful during maintenance windows or deployments.
Change schedule ALTER EVENT name ON SCHEDULE EVERY 30 MINUTE; ALTER EVENT can also change the body, comment, and status.
Drop event DROP EVENT IF EXISTS name; Always IF EXISTS in scripts.
Next: Chapter 10 — Query optimisation: EXPLAIN and EXPLAIN ANALYZE output, index strategy (composite, covering, prefix indexes), avoiding full table scans, and profiling with the slow query log.