MySQL – Triggers

A trigger is a block of SQL that runs automatically when a row is inserted, updated or deleted. It cannot be called and it cannot be skipped, which is both the appeal and the problem.

An audit trail

Recording every status change on an order is the case triggers are genuinely good at — it must happen for every writer, and no application code has to remember it:

CREATE TABLE order_status_audit (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id   BIGINT      NOT NULL,
    old_status VARCHAR(30),
    new_status VARCHAR(30) NOT NULL,
    changed_at DATETIME(6) NOT NULL
);

DELIMITER $$

CREATE TRIGGER trg_order_status_audit
AFTER UPDATE ON customer_order FOR EACH ROW
BEGIN
    IF NEW.status <> OLD.status THEN
        INSERT INTO order_status_audit (order_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW(6));
    END IF;
END$$

DELIMITER ;

Now change some rows — including one update that does not touch the status:

UPDATE customer_order SET status = 'PREPARING'  WHERE id = 1;
UPDATE customer_order SET phone  = '555'        WHERE id = 1;   -- not a status change
UPDATE customer_order SET status = 'COMPLETED'  WHERE id = 1;

SELECT order_id, old_status, new_status FROM order_status_audit ORDER BY id;

+----------+------------+------------+
| order_id | old_status | new_status |
+----------+------------+------------+
|        1 | COMPLETED  | PREPARING  |
|        1 | PREPARING  | COMPLETED  |
+----------+------------+------------+

Two rows, not three. The trigger fires for every updated row regardless — the IF is what keeps the phone change out of the audit. Without it you would log every update as a status change from a value to itself.

NEW and OLD

In a BEFORE trigger, NEW columns are writable — that is how you normalise or default a value on the way in:

DELIMITER $$

CREATE TRIGGER trg_normalise_email
BEFORE INSERT ON app_user FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(TRIM(NEW.email));
END$$

DELIMITER ;

BEFORE for validating or adjusting the row; AFTER for reacting to a change that has already happened. Six combinations in total, and MySQL 5.7 onwards allows more than one trigger for the same event — ordered with FOLLOWS / PRECEDES.

⚠️ A trigger cannot touch its own table

This one creates cleanly and fails when it runs, which is the worst time to find out:

DELIMITER $$

CREATE TRIGGER trg_bad AFTER UPDATE ON customer_order FOR EACH ROW
BEGIN
    UPDATE customer_order SET updated_at = NOW(6) WHERE id = NEW.id;
END$$

DELIMITER ;

-- ERROR 1442 (HY000): Can't update table 'customer_order' in stored function/trigger
-- because it is already used by statement which invoked this stored function/trigger.
UPDATE customer_order SET phone = '777' WHERE id = 2;

CREATE TRIGGER succeeded. The error arrives on the first update to the table — possibly in production, possibly weeks later. MySQL forbids it to avoid infinite recursion.

To modify the row being written, use a BEFORE trigger and assign to NEW, which needs no UPDATE at all.

The other limitations

• TRUNCATE does not fire triggers. It is not a row-by-row delete. Neither are changes made by foreign key ON DELETE CASCADE — the cascade removes child rows without firing their delete triggers.
• A trigger runs inside the calling statement's transaction. If it fails, the whole statement is rolled back. It cannot COMMIT or ROLLBACK.
• It runs once per row, so an update touching 100,000 rows runs it 100,000 times. The audit table above doubles the write cost of a bulk status change.
• SHOW TRIGGERS lists them; information_schema.TRIGGERS is queryable. There is no CREATE OR REPLACE, so changing one means DROP then CREATE.

The real argument against them

A trigger is invisible. Someone debugging why a row appeared in order_status_audit will read the application code, find nothing that writes to it, and have no reason to suspect the database is doing it. The same applies to a value that mysteriously differs from what was inserted.

That cost is worth paying when the rule must hold for every writer and cannot be enforced any other way — audit trails and immutable history are the standard examples. It is not worth paying for business logic, which belongs where it can be read, tested and version-controlled. The pizza schema uses none.

If you do use them: keep them small, name them so the table and event are obvious (trg_order_status_audit), keep the definitions in migration files, and document them where an application developer will actually look — a comment in the entity class beats a comment in the trigger.

The common alternatives are worth knowing. Application-level hooks (the pizza schema's JPA @PrePersist) are visible and testable but only cover writers going through that code. DEFAULT CURRENT_TIMESTAMP ON UPDATE handles timestamps declaratively without a trigger at all. And change data capture reading the binary log gives you an audit trail without touching the write path.

What to remember

• BEFORE to adjust NEW; AFTER to react.
• It fires per row for every update — test the column you care about.
• A trigger cannot modify its own table, and that error appears at run time.
• TRUNCATE and FK cascades do not fire triggers.
• Audit trails yes; business logic no. Invisible behaviour is expensive to debug.