MySQL Triggers: BEFORE and AFTER INSERT, UPDATE, DELETE

A trigger is code that MySQL runs automatically when a row is inserted, updated or deleted in a table. You do not call it.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    updated_at DATETIME NULL
) ENGINE = InnoDB;

CREATE TABLE price_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    old_price DECIMAL(8,2) NULL,
    new_price DECIMAL(8,2) NOT NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

An audit trail with an AFTER trigger

query.sql

SQL

DELIMITER //

CREATE TRIGGER trg_products_price_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_history (product_id, old_price, new_price) VALUES (OLD.id, OLD.price, NEW.price);
    END IF;
END//

DELIMITER ;

INSERT INTO products (name, price) VALUES ('Notebook', 80), ('Pen', 15);
UPDATE products SET price = 85 WHERE name = 'Notebook';
UPDATE products SET name = 'Gel Pen' WHERE name = 'Pen';
UPDATE products SET price = 90 WHERE name = 'Notebook';

SELECT product_id, old_price, new_price FROM price_history ORDER BY id;

Output

OUTPUT

+------------+-----------+-----------+
| product_id | old_price | new_price |
+------------+-----------+-----------+
|          1 |     80.00 |     85.00 |
|          1 |     85.00 |     90.00 |
+------------+-----------+-----------+
2 rows in set

The history has two rows, one per price change. The rename of the pen did not change the price, so the trigger wrote nothing.

The syntax

Text

PLAIN

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

BEFORE triggers run before the row is written. They can change NEW values and reject the change.
AFTER triggers run after the row is written. They are used for side effects (logging, copying).
FOR EACH ROW: it runs once per affected row. MySQL has no statement-level triggers.

NEW and OLD

Event	`OLD`	`NEW`
`INSERT`	not available	The row being inserted
`UPDATE`	The row before the change	The row after the change
`DELETE`	The row being deleted	not available

A BEFORE trigger: set defaults and validate

query.sql

SQL

DELIMITER //

CREATE TRIGGER trg_products_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SET NEW.name = TRIM(NEW.name);
    SET NEW.updated_at = NOW();
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
END//

CREATE TRIGGER trg_products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//

DELIMITER ;

INSERT INTO products (name, price) VALUES ('   Marker   ', 145);
SELECT CONCAT('[', name, ']') AS name, updated_at IS NOT NULL AS has_timestamp FROM products WHERE price = 145;

Output

OUTPUT

+----------+---------------+
| name     | has_timestamp |
+----------+---------------+
| [Marker] |             1 |
+----------+---------------+
1 row in set

query.sql

SQL

INSERT INTO products (name, price) VALUES ('Broken', -1);

Output

OUTPUT

ERROR 1644 (45000): Price cannot be negative

The trigger trimmed the name, filled updated_at, and refused the negative price. For simple rules, a CHECK constraint or DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP is simpler and safer than a trigger (CHECK Constraints).

Keep a summary up to date

query.sql

SQL

CREATE TABLE order_lines (id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, amount DECIMAL(8,2) NOT NULL) ENGINE = InnoDB;
CREATE TABLE order_totals (order_id INT PRIMARY KEY, total DECIMAL(10,2) NOT NULL DEFAULT 0, line_count INT NOT NULL DEFAULT 0) ENGINE = InnoDB;

DELIMITER //

CREATE TRIGGER trg_lines_after_insert AFTER INSERT ON order_lines FOR EACH ROW
BEGIN
    INSERT INTO order_totals (order_id, total, line_count) VALUES (NEW.order_id, NEW.amount, 1)
    ON DUPLICATE KEY UPDATE total = total + NEW.amount, line_count = line_count + 1;
END//

CREATE TRIGGER trg_lines_after_delete AFTER DELETE ON order_lines FOR EACH ROW
BEGIN
    UPDATE order_totals SET total = total - OLD.amount, line_count = line_count - 1 WHERE order_id = OLD.order_id;
END//

DELIMITER ;

INSERT INTO order_lines (order_id, amount) VALUES (1, 80), (1, 155), (2, 999);
DELETE FROM order_lines WHERE amount = 155;
SELECT order_id, total, line_count FROM order_totals ORDER BY order_id;

Output

OUTPUT

+----------+--------+------------+
| order_id | total  | line_count |
+----------+--------+------------+
|        1 |  80.00 |          1 |
|        2 | 999.00 |          1 |
+----------+--------+------------+
2 rows in set

This is a denormalised counter kept consistent by the database. It also shows the cost: every insert and delete pays for an extra write, and a hot parent row (a very popular order or category) can become a lock bottleneck.

Seeing triggers

query.sql

SQL

SELECT trigger_name, event_manipulation, action_timing, event_object_table FROM information_schema.triggers WHERE trigger_schema = 'learn' ORDER BY trigger_name;

Output

OUTPUT

+----------------------------+--------------------+---------------+--------------------+
| TRIGGER_NAME               | EVENT_MANIPULATION | ACTION_TIMING | EVENT_OBJECT_TABLE |
+----------------------------+--------------------+---------------+--------------------+
| trg_lines_after_delete     | DELETE             | AFTER         | order_lines        |
| trg_lines_after_insert     | INSERT             | AFTER         | order_lines        |
| trg_products_before_insert | INSERT             | BEFORE        | products           |
| trg_products_before_update | UPDATE             | BEFORE        | products           |
| trg_products_price_audit   | UPDATE             | AFTER         | products           |
+----------------------------+--------------------+---------------+--------------------+
5 rows in set

SHOW TRIGGERS and SHOW CREATE TRIGGER name also work. DROP TRIGGER [IF EXISTS] name removes one.

Limits and rules

A trigger cannot use START TRANSACTION, COMMIT or ROLLBACK, and cannot return a result set.
It cannot modify the table it fires on (except by changing NEW in a BEFORE trigger).
It does not fire for cascaded foreign key actions (Foreign Key Actions).
It does not fire for TRUNCATE TABLE, and LOAD DATA fires it only for inserted rows.
You can have several triggers for the same event and timing (FOLLOWS / PRECEDES set the order).
An error in a trigger makes the whole statement fail and roll back.
A trigger runs for each row: a bulk update of a million rows runs it a million times.

Why triggers are controversial

Pros: they guarantee that a rule or an audit entry is created whatever application changes the data, including a manual UPDATE.

Cons:

Invisible logic. Code in the application does something that the application does not show. Developers are surprised by side effects.
Hard to debug and test, and easy to forget in migrations.
Slow for bulk changes and lock-heavy under concurrency.
Chains of triggers (one trigger writes a table that has its own trigger) become a maze.
They are bypassed by cascades and TRUNCATE.

When to use a trigger

An audit/history table that must capture every change, from any source.
Rules that cannot be a constraint (a cross-row check) and must be enforced centrally.
Maintaining a small counter or summary where the application cannot be trusted to do it.

For anything else, prefer constraints, application logic or an explicit procedure. Document every trigger in the schema README.

dangerous

Test with bulk operations

A trigger that is fine for one row can ruin a bulk import or a nightly job. Test your triggers with realistic row counts.