Triggers & scheduled events

BEFORE/AFTER triggers with NEW and OLD, audit logs, SIGNAL, and the event scheduler.

Procedures and functions run when you call them. Two other kinds of stored program run automatically:

Triggers fire when rows are inserted, updated or deleted in a table.
Events fire on a schedule, like a cron job inside MySQL.

Sample tables#

SQL

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    slug VARCHAR(60)
);
CREATE TABLE price_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    old_price DECIMAL(8,2),
    new_price DECIMAL(8,2),
    changed_by VARCHAR(100),
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Trigger anatomy#

SQL

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
trigger_body;

Timing: BEFORE triggers run before the row is written, so they can validate or change it. AFTER triggers run once the row has been written, which makes them good for logging and keeping other tables in sync.
Event: INSERT, UPDATE or DELETE (including LOAD DATA and REPLACE, which insert and delete rows).
FOR EACH ROW: the body runs once per affected row. An UPDATE touching 1,000 rows fires 1,000 times.
NEW.col is the new row (INSERT, UPDATE) and OLD.col is the old row (UPDATE, DELETE).

BEFORE triggers: fix up and validate data#

SQL

DELIMITER $$
CREATE TRIGGER products_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be positive';
    END IF;
    -- derive a URL slug when none is given
    IF NEW.slug IS NULL THEN
        SET NEW.slug = LOWER(REPLACE(TRIM(NEW.name), ' ', '-'));
    END IF;
END $$
DELIMITER ;

INSERT INTO products (id, name, price, stock) VALUES (1, 'Wireless Mouse', 799.00, 10);
INSERT INTO products (id, name, price, stock) VALUES (2, 'Broken Thing', 0, 1);
SELECT id, name, slug FROM products;

Output

ERROR 1644 (45000): Price must be positive
+----+----------------+----------------+
| id | name           | slug           |
+----+----------------+----------------+
|  1 | Wireless Mouse | wireless-mouse |
+----+----------------+----------------+

SET NEW.slug = ... changed the row before it was stored, and SIGNAL rejected the invalid one. For simple rules like "price > 0", a CHECK constraint is clearer. Use triggers for logic that constraints can't express.

AFTER triggers: an audit log#

Record every price change, including who made it:

SQL

CREATE TRIGGER products_after_update
AFTER UPDATE ON products
FOR EACH ROW
INSERT INTO price_history (product_id, old_price, new_price, changed_by)
SELECT NEW.id, OLD.price, NEW.price, CURRENT_USER()
FROM DUAL
WHERE NOT (OLD.price <=> NEW.price);      -- only when the price really changed

UPDATE products SET price = 749.00 WHERE id = 1;
UPDATE products SET stock = 8 WHERE id = 1;      -- no price change → nothing logged
UPDATE products SET price = 699.00 WHERE id = 1;

SELECT product_id, old_price, new_price, changed_by FROM price_history;

Output

+------------+-----------+-----------+----------------+
| product_id | old_price | new_price | changed_by     |
+------------+-----------+-----------+----------------+
|          1 |    799.00 |    749.00 | root@localhost |
|          1 |    749.00 |    699.00 | root@localhost |
+------------+-----------+-----------+----------------+

<=> is the null-safe comparison, so the check also works when a price is NULL. FROM DUAL is a dummy table that lets the INSERT ... SELECT carry a WHERE.

Keeping a summary in sync#

Triggers can maintain denormalised data, such as a running count:

SQL

CREATE TABLE reviews (id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, stars TINYINT NOT NULL);
ALTER TABLE products ADD COLUMN review_count INT NOT NULL DEFAULT 0;

CREATE TRIGGER reviews_after_insert AFTER INSERT ON reviews FOR EACH ROW
    UPDATE products SET review_count = review_count + 1 WHERE id = NEW.product_id;
CREATE TRIGGER reviews_after_delete AFTER DELETE ON reviews FOR EACH ROW
    UPDATE products SET review_count = review_count - 1 WHERE id = OLD.product_id;

INSERT INTO reviews (product_id, stars) VALUES (1, 5), (1, 4), (1, 2);
DELETE FROM reviews WHERE stars = 2;
SELECT id, name, review_count FROM products;

Output

+----+----------------+--------------+
| id | name           | review_count |
+----+----------------+--------------+
|  1 | Wireless Mouse |            2 |
+----+----------------+--------------+

A trigger runs inside the same transaction as the statement that fired it. If the trigger fails, the whole statement fails and, on InnoDB, its changes are rolled back, so the count can't drift out of sync.

Managing triggers#

SQL

SHOW TRIGGERS LIKE 'products'\G
DROP TRIGGER IF EXISTS products_before_insert;

Since MySQL 5.7 you can have several triggers for the same timing and event. Order them with FOLLOWS other_trigger or PRECEDES other_trigger.

Trigger cautions

Invisible logic. Developers reading INSERT INTO reviews won't know it also updates products. Document triggers and keep them small.
Performance. Per-row execution slows down bulk operations.
Restrictions. A trigger can't modify the table it's defined on (use SET NEW.col in a BEFORE trigger instead), can't COMMIT/ROLLBACK, and can't return result sets.
Foreign-key cascades don't fire triggers on the child table.

Events: scheduled jobs#

The event scheduler runs SQL at fixed times or intervals. It's ON by default in MySQL 8:

SQL

SHOW VARIABLES LIKE 'event_scheduler';

Output

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

(Turn it on with SET GLOBAL event_scheduler = ON; or event_scheduler=ON in my.cnf.)

A recurring event purges old log rows every night:

SQL

CREATE TABLE app_log (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(100), logged_at DATETIME NOT NULL);

CREATE EVENT purge_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR)    -- tomorrow at 02:00
COMMENT 'Delete log rows older than 30 days'
DO
    DELETE FROM app_log WHERE logged_at < NOW() - INTERVAL 30 DAY;

A one-off event runs once and is then dropped (unless you add ON COMPLETION PRESERVE):

SQL

CREATE EVENT end_of_sale
ON SCHEDULE AT '2026-12-31 23:59:59'
DO
    UPDATE products SET price = price * 1.10;

Events can call procedures and use BEGIN ... END bodies (with DELIMITER) for several statements. A typical pattern is rebuilding a reporting table every hour:

SQL

CREATE TABLE daily_sales_summary (day DATE PRIMARY KEY, revenue DECIMAL(12,2));

DELIMITER $$
CREATE EVENT refresh_sales_summary
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM daily_sales_summary WHERE day = CURRENT_DATE;
    INSERT INTO daily_sales_summary (day, revenue)
    SELECT CURRENT_DATE, COALESCE(SUM(price), 0) FROM products;   -- stand-in for real sales
END $$
DELIMITER ;

SELECT EVENT_NAME, EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STATUS
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE()
ORDER BY EVENT_NAME;

Output

+-----------------------+------------+----------------+----------------+---------+
| EVENT_NAME            | EVENT_TYPE | INTERVAL_VALUE | INTERVAL_FIELD | STATUS  |
+-----------------------+------------+----------------+----------------+---------+
| end_of_sale           | ONE TIME   | NULL           | NULL           | ENABLED |
| purge_old_logs        | RECURRING  | 1              | DAY            | ENABLED |
| refresh_sales_summary | RECURRING  | 1              | HOUR           | ENABLED |
+-----------------------+------------+----------------+----------------+---------+

Manage events with:

SQL

ALTER EVENT purge_old_logs DISABLE;        -- pause it
ALTER EVENT purge_old_logs ENABLE;
ALTER EVENT purge_old_logs ON SCHEDULE EVERY 12 HOUR;
DROP EVENT IF EXISTS end_of_sale;

Event tips

Events run as their definer, in the server's time zone. Check @@global.time_zone when picking STARTS times.
Errors are written to the server error log, not to any client, so log progress to a table if a job matters.
Big deletes should run in batches (DELETE ... LIMIT 10000 in a loop) to avoid long locks and replication lag.
On replicas, replicated events are disabled (status REPLICA_SIDE_DISABLED, or SLAVESIDE_DISABLED on older versions) so they don't run twice.
Events vs cron: events need no extra infrastructure and are backed up with the database. For jobs that span systems or need alerting, an external scheduler (cron, systemd timers, your job runner) is usually better.

What's next#

Your database now does real work on its own. Time to lock it down: users, privileges and security.

Sample tables#

Trigger anatomy#

BEFORE triggers: fix up and validate data#

AFTER triggers: an audit log#

Keeping a summary in sync#

Managing triggers#

Trigger cautions

Events: scheduled jobs#

Event tips

What's next#

Quick quiz