Triggers

Triggers run SQL automatically for every row inserted, updated or deleted, leaving no trace in the statement that fired them.

Syntax

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON tbl_name FOR EACH ROW [{FOLLOWS | PRECEDES} other_trigger] trigger_body

A trigger is a block of SQL the server runs automatically when a row in one table is inserted, updated or deleted. Six combinations exist: BEFORE or AFTER, each with INSERT, UPDATE or DELETE. MySQL triggers are always FOR EACH ROW; there is no statement-level trigger. An INSERT trigger also fires for LOAD DATA and REPLACE.

OLD and NEW

Event	OLD	NEW
INSERT	not available	the incoming row
UPDATE	the row as it was	the row as it will be
DELETE	the row being removed	not available

In a BEFORE trigger you may assign to NEW.col to rewrite the row on its way in. In an AFTER trigger the write has already happened, so assignment is pointless and rejected.

Order and failure

More than one trigger may share a time and event; they fire in creation order unless you add FOLLOWS or PRECEDES. If a BEFORE trigger fails, the row operation does not happen. An AFTER trigger runs only if the row operation succeeded, and its failure fails the statement — on InnoDB that rolls the statement's changes back.

Why they are hard to debug

A trigger cannot return a result set, so there is no printing from inside one; log to a table. It cannot modify a table the invoking statement is already using. And nothing in the application's SQL mentions the trigger, so its writes are invisible at the call site.

Example

CREATE TABLE order_audit (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id   BIGINT       NOT NULL,
    changed_at DATETIME(6)  NOT NULL,
    changed_by VARCHAR(128) NOT NULL,
    old_status VARCHAR(32),
    new_status VARCHAR(32),
    old_total  BIGINT,
    new_total  BIGINT,
    KEY idx_order (order_id, changed_at)
) ENGINE=InnoDB;

-- DELIMITER is a mysql client command, not server SQL: it stops the client
-- from cutting the body in half at the first internal semicolon.
DELIMITER //

CREATE TRIGGER orders_au_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    -- <=> is NULL-safe equality, so NULL -> NULL counts as "unchanged"
    -- instead of yielding NULL and skipping the IF.
    IF NOT (OLD.status <=> NEW.status)
       OR NOT (OLD.total_cents <=> NEW.total_cents) THEN
        INSERT INTO order_audit
            (order_id, changed_at, changed_by,
             old_status, new_status, old_total, new_total)
        VALUES
            (OLD.id, NOW(6), CURRENT_USER(),
             OLD.status, NEW.status, OLD.total_cents, NEW.total_cents);
    END IF;
END //

DELIMITER ;

When to use it

Write an audit row whenever an order's status or total changes, without touching application code.
Normalise or stamp a column (lowercase email, updated_at) regardless of which service wrote the row.
Enforce a cross-table rule that a CHECK constraint cannot express, rejecting the write with SIGNAL.
Push a row onto an outbox table inside the same transaction as the change it describes.

More examples

Single-statement shorthand and the BEGIN ... END longhand

Assigning to NEW.col only works in a BEFORE trigger, which is how you normalise or stamp a value without trusting every caller to do it. Recognise both spellings: the one-line form is common in migrations, the block form in anything with logic.

-- Shorthand: one statement, no BEGIN/END, so no DELIMITER change needed.
CREATE TRIGGER users_bi_normalise
BEFORE INSERT ON users
FOR EACH ROW
    SET NEW.email = LOWER(TRIM(NEW.email));

-- Longhand: the same thing, written as a block. Any trigger with two or
-- more statements must use this form, and then the client needs DELIMITER.
DELIMITER //
CREATE TRIGGER users_bu_normalise
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(TRIM(NEW.email));
    SET NEW.updated_at = NOW(6);
END //
DELIMITER ;

Rejecting a row from inside a trigger

SIGNAL with SQLSTATE '45000' raises an application error; because this is a BEFORE trigger, the row is not inserted. Note the SELECT ... INTO: a bare SELECT would try to return a result set, which a trigger may not do. Use a trigger only when the rule spans tables — a rule about one row's own columns belongs in a CHECK constraint.

DELIMITER //
CREATE TRIGGER order_items_bi_check
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE frozen INT DEFAULT 0;

    SELECT COUNT(*) INTO frozen
    FROM orders
    WHERE id = NEW.order_id AND status IN ('shipped', 'cancelled');

    IF frozen > 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot add items to a shipped or cancelled order';
    END IF;
END //
DELIMITER ;

Finding out which triggers exist

This is the first query to run when a table does something you cannot find in the application code. ACTION_ORDER shows the real firing sequence, and FOLLOWS or PRECEDES pins it down so a later migration that recreates one trigger does not silently reorder them.

SHOW TRIGGERS FROM shop LIKE 'orders';

-- Full detail, including firing order within a time/event pair.
SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING,
       ACTION_ORDER, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_ORDER;

-- Make the order explicit rather than relying on creation sequence.
CREATE TRIGGER orders_au_notify
AFTER UPDATE ON orders
FOR EACH ROW FOLLOWS orders_au_audit
    INSERT INTO outbox (topic, order_id) VALUES ('order.updated', NEW.id);

Discussion

Be the first to comment on this lesson.

OLD and NEW

Order and failure

Why they are hard to debug

Example

When to use it

More examples

Single-statement shorthand and the BEGIN ... END longhand

Rejecting a row from inside a trigger

Finding out which triggers exist

Related

Discussion