Triggers and Scheduled Events

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

Concept	Syntax / rule	Notes
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.