Skip to content
Tutorials Center
My learning

Topics

Lesson 54 of 75 ยท Programmable Objects

Triggers

Learn MySQL triggers: BEFORE and AFTER INSERT, UPDATE and DELETE, the OLD and NEW rows, an audit log pattern, SIGNAL validation, limits, costs and alternatives.

A trigger is a piece of code attached to a table that MySQL runs automatically when a row is inserted, updated or deleted. You do not call it: the database does, on every matching change, no matter which application, script or person made it. That is both the appeal and the danger. A trigger can enforce a rule or keep an audit trail that no client can bypass, and it can also hide logic where nobody looks for it and slow down every write.

This lesson covers MySQL trigger syntax, the OLD and NEW row aliases, the classic audit-log pattern and the limits and costs you should weigh before adding one. MySQL triggers need a real server, so the MySQL syntax is read-only here. SQLite has triggers too, with slightly different syntax, and the live examples use those to run the same patterns. Familiarity with stored procedures and error handling with SIGNAL helps.

When a trigger fires

INSERT A new row arrives.
UPDATE A row changes.
DELETE A row goes away.

Each can run BEFORE or AFTER the change.

1 / 2

The syntax

SQL
DELIMITER //

CREATE TRIGGER trg_members_before_insert
BEFORE INSERT ON members
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = NOW();
  END IF;
END //

DELIMITER ;

The MySQL trigger code in this lesson is read-only here, because it needs a real server. The parts:

  • Timing: BEFORE runs before the row is written, AFTER runs after.
  • Event: INSERT, UPDATE or DELETE. One trigger handles one event, so three events need three triggers (or one procedure that each calls).
  • FOR EACH ROW: MySQL only has row-level triggers. A statement that changes 10,000 rows runs the body 10,000 times.
  • OLD and NEW: NEW.col is the incoming value and OLD.col is the existing value. INSERT has only NEW, DELETE has only OLD, UPDATE has both.
  • A BEFORE trigger can change NEW (SET NEW.col = ...), which makes it the place for normalizing and defaulting. An AFTER trigger sees the final row and cannot change it.

Name triggers after table, timing and event, such as members_bi or trg_members_before_insert. When several triggers exist for the same table, timing and event, they run in creation order, and you can control that with FOLLOWS and PRECEDES. You inspect and remove them with:

SQL
SHOW TRIGGERS FROM shop LIKE 'members';
SHOW CREATE TRIGGER trg_members_before_insert;
DROP TRIGGER IF EXISTS trg_members_before_insert;

The audit log pattern

The most common use is recording who changed what. An AFTER UPDATE trigger writes the old and new values into a history table:

SQL
CREATE TABLE price_audit (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_price  DECIMAL(10,2),
  new_price  DECIMAL(10,2),
  changed_by VARCHAR(100),
  changed_at DATETIME NOT NULL
);

DELIMITER //

CREATE TRIGGER products_au_price
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF NOT (OLD.price <=> NEW.price) THEN
    INSERT INTO price_audit (product_id, old_price, new_price, changed_by, changed_at)
    VALUES (OLD.id, OLD.price, NEW.price, CURRENT_USER(), NOW());
  END IF;
END //

DELIMITER ;

Notes: <=> is MySQL's NULL-safe equality, so a change from NULL to a price is detected too. CURRENT_USER() is the MySQL account, which for a web app is the shared application user. If you need the real human, the application must pass it (for example in a session variable that the trigger reads). The audit insert runs in the same transaction as the update: if the transaction rolls back, so does the log entry, which is exactly what you want.

SQLite supports the same pattern. Its syntax differs a little: there is no FOR EACH ROW ... BEGIN delimiter dance, you put the WHEN condition in the header, and IS NOT plays the role of the NULL-safe comparison:

SQL An audit trigger that logs price changes

Press Run (or Ctrl + Enter) to see the result here.

Four audit rows appear: the pen's change from 2.50 to 3.00, then three more from the 10% rise (the pen from 3.00, the book from 12.00 and the bag from 40.00). The no-op update of the book produced no row, because the WHEN condition was false. The rise ran the trigger once per row, which is how MySQL row triggers behave too.

Keeping a summary in sync

Another typical job is maintaining a denormalized counter or total so reads stay cheap. Here an AFTER INSERT and an AFTER DELETE trigger keep a per-customer order count correct:

SQL Triggers that keep a counter in sync

Press Run (or Ctrl + Enter) to see the result here.

Ada ends with 1 and Bo with 1. The MySQL version has the same shape, with FOR EACH ROW and a BEGIN ... END body. The weakness is also visible here: if someone inserts orders in a way that bypasses the trigger (see the limits below), the counter silently drifts. A plain SELECT COUNT(*) can never drift, so use stored counters only where the read cost justifies them.

Rejecting bad data with SIGNAL

A BEFORE trigger can block a change by raising an error:

SQL
DELIMITER //

CREATE TRIGGER orders_bi_check
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.total < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order total cannot be negative';
  END IF;
END //

DELIMITER ;

The statement fails and nothing is stored. Since MySQL 8.0.16 a CHECK constraint does this job more simply and visibly for single-row rules, so keep triggers for rules a CHECK cannot express (for example, rules that look at other tables).

Limits and surprises

  • Not fired by foreign key cascades. Rows deleted or updated through ON DELETE CASCADE or ON UPDATE actions do not fire triggers on the child table. An audit trigger on the child will miss them.
  • Not fired by TRUNCATE TABLE, which does not process rows. DROP TABLE removes the triggers with the table.
  • REPLACE runs delete and insert triggers (a duplicate causes a delete then an insert). INSERT ... ON DUPLICATE KEY UPDATE fires BEFORE INSERT first and then the update triggers if it turns into an update. LOAD DATA fires insert triggers.
  • No transaction control inside a trigger: no COMMIT, ROLLBACK or START TRANSACTION, and no statement that causes an implicit commit.
  • A trigger cannot modify the table that fired it (error 1442), apart from changing NEW in a BEFORE trigger. Otherwise it could loop forever.
  • A trigger cannot return a result set.
  • Replication and mysqldump carry triggers, but a restore under a different account may fail because of DEFINER (see the privileges note in the stored routines lesson). You need the TRIGGER privilege to create them.
  • If the trigger body fails, the whole statement fails. A broken audit trigger can stop all price changes.

Cost and alternatives

Every trigger adds work to every affected row, inside the same transaction, holding locks longer. A bulk UPDATE touching a million rows with an audit trigger writes a million extra rows. Triggers are also invisible to developers reading application code, they are hard to test and debug, and they can cascade (a trigger's own writes may fire other triggers).

Before adding one, consider:

Need Alternative
Single-row validation CHECK, NOT NULL, UNIQUE, foreign keys
Derived value Generated column
Audit trail Application-level logging in the same transaction, or reading the binary log with a change-data-capture tool such as Debezium
Counter or total Compute with COUNT(*) or SUM in a view, or update in application code
Timestamps DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP

Triggers earn their place when a rule must hold for every writer, and the database is the only place you can guarantee that. Keep each one small, document it, store its source in your migrations and test it with a bulk update before you ship.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which trigger timing lets you change the values of the row being written?

    A BEFORE trigger runs before the row is stored and can SET NEW.column. An AFTER trigger sees the final row and cannot change it.
  2. 2. Which rows are available in a DELETE trigger?

    A deleted row has no new version, so only OLD exists. INSERT has only NEW, and UPDATE has both.
  3. 3. A child table has an audit trigger. Rows are deleted from it through ON DELETE CASCADE. What happens?

    In MySQL, foreign key cascade actions do not activate triggers on the affected child table.
  4. 4. A statement updates 50,000 rows and the table has an AFTER UPDATE trigger. How often does the body run?

    MySQL has only row-level triggers (FOR EACH ROW), so the body runs for every affected row.
  5. 5. Which is usually the better tool for a simple rule such as price >= 0?

    CHECK constraints are enforced since MySQL 8.0.16 and are simpler and more visible than triggers for single-row rules.

Summary

  • A trigger runs automatically for each row on INSERT, UPDATE or DELETE, either BEFORE or AFTER. MySQL has row-level triggers only.
  • NEW and OLD give the incoming and existing row. Only BEFORE triggers can change NEW.
  • Audit logs, normalization and counters are the classic uses. SIGNAL rejects bad data, but CHECK is simpler for single-row rules.
  • Triggers do not fire for foreign key cascades or TRUNCATE, cannot use transaction control and cannot modify their own table.
  • Every trigger adds cost to every write and hides logic from readers. Prefer constraints, generated columns, application code or change-data-capture where they fit.

Next: Events and the Event Scheduler, where the database runs jobs on a timer.