Triggers: MySQL | Himanshu Sharma

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonWrite triggers that react to inserts, updates and deletes, and judge when a trigger is the right tool.

Code that fires by itself

A trigger is a piece of SQL attached to a table that MySQL runs automatically when a row is inserted, updated or deleted. You never CALL it; it fires as a side effect of an ordinary statement.

A trigger is defined by three choices:

Timing: BEFORE (runs before the change is applied, can alter or reject it) or AFTER (runs once the change succeeded).
Event: INSERT, UPDATE or DELETE.
FOR EACH ROW: it runs once for every affected row.

Inside the body, two pseudo-rows are available: NEW (the row being inserted or the updated row's new values) and OLD (the row being deleted or the updated row's old values).

An audit log

The most popular use: record who changed what. Create a log table, then a trigger that writes to it whenever a product's price changes:

sql

USE shop;
DROP TABLE IF EXISTS price_history;
CREATE TABLE price_history (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  old_price  DECIMAL(10,2) NOT NULL,
  new_price  DECIMAL(10,2) NOT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TRIGGER IF EXISTS trg_products_price_audit;
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 ;

Now an ordinary update does extra work behind the scenes:

sql

UPDATE products SET price = 26.99 WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 2;
UPDATE products SET price = price * 1.10 WHERE category_id = 5;
SELECT product_id, old_price, new_price FROM price_history ORDER BY id;

result

+------------+-----------+-----------+
| product_id | old_price | new_price |
+------------+-----------+-----------+
|          1 |     24.99 |     26.99 |
|         14 |     49.99 |     54.99 |
|         15 |     19.99 |     21.99 |
+------------+-----------+-----------+

Only the price changes were logged. The stock update fired the trigger too, but the IF skipped it. We left out changed_at because the clock value differs on every run.

BEFORE triggers: validate and fix

A BEFORE trigger can tidy input or reject it. This one normalises email addresses and refuses nonsense:

sql

DROP TRIGGER IF EXISTS trg_customers_before_insert;
DELIMITER //
CREATE TRIGGER trg_customers_before_insert
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
  IF NEW.email NOT LIKE '%_@_%.__%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email address';
  END IF;
END //
DELIMITER ;

INSERT INTO customers (id, first_name, last_name, email, city, country, signup_date)
VALUES (13, 'Nora', 'Haas', '  Nora.Haas@Example.COM ', 'Berlin', 'Germany', '2024-09-01');
SELECT id, email FROM customers WHERE id = 13;

result

+----+-----------------------+
| id | email                 |
+----+-----------------------+
| 13 | nora.haas@example.com |
+----+-----------------------+

The stored address was trimmed and lower-cased before it was written. A bad address is rejected:

sql

INSERT INTO customers (id, first_name, last_name, email, city, country, signup_date)
VALUES (14, 'Bad', 'Email', 'not-an-email', 'Berlin', 'Germany', '2024-09-02');

result

ERROR 1644 (45000): Invalid email address

Keeping a derived value in sync

Triggers can maintain summary columns. Suppose each customer should carry an order count:

sql

ALTER TABLE customers ADD COLUMN order_count INT NOT NULL DEFAULT 0;
UPDATE customers c SET order_count = (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id);

DROP TRIGGER IF EXISTS trg_orders_count_insert;
DROP TRIGGER IF EXISTS trg_orders_count_delete;
DELIMITER //
CREATE TRIGGER trg_orders_count_insert AFTER INSERT ON orders FOR EACH ROW
BEGIN
  UPDATE customers SET order_count = order_count + 1 WHERE id = NEW.customer_id;
END //
CREATE TRIGGER trg_orders_count_delete AFTER DELETE ON orders FOR EACH ROW
BEGIN
  UPDATE customers SET order_count = order_count - 1 WHERE id = OLD.customer_id;
END //
DELIMITER ;

INSERT INTO orders (id, customer_id, order_date, status) VALUES (21, 9, '2024-08-05', 'pending');
SELECT id, first_name, order_count FROM customers WHERE id IN (1, 9);

result

+----+------------+-------------+
| id | first_name | order_count |
+----+------------+-------------+
|  1 | Asha       |           4 |
|  9 | Isabel     |           1 |
+----+------------+-------------+

Isabel, who had no orders, now has one. This is denormalisation (from the design lesson) kept in step automatically. The risk is that it must cover every path that can change the data, including updates that move an order to another customer.

Managing triggers

sql

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'shop'
ORDER BY EVENT_OBJECT_TABLE, TRIGGER_NAME;

result

+-----------------------------+--------------------+--------------------+---------------+
| TRIGGER_NAME                | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_TIMING |
+-----------------------------+--------------------+--------------------+---------------+
| trg_customers_before_insert | INSERT             | customers          | BEFORE        |
| trg_orders_count_delete     | DELETE             | orders             | AFTER         |
| trg_orders_count_insert     | INSERT             | orders             | AFTER         |
| trg_products_price_audit    | UPDATE             | products           | AFTER         |
+-----------------------------+--------------------+--------------------+---------------+

Drop one with DROP TRIGGER IF EXISTS name;. MySQL allows several triggers for the same event and timing on a table, which run in creation order (use FOLLOWS or PRECEDES to control that). Clean up the sample:

sql

DROP TRIGGER IF EXISTS trg_products_price_audit;
DROP TRIGGER IF EXISTS trg_customers_before_insert;
DROP TRIGGER IF EXISTS trg_orders_count_insert;
DROP TRIGGER IF EXISTS trg_orders_count_delete;

Limits and cautions

Triggers fire per row, so a statement that changes a million rows runs the trigger a million times.
They are not fired by cascading foreign key actions in MySQL, which can surprise you.
A trigger cannot start or end a transaction and cannot modify the table it is defined on in a way that would cause recursion.
They are invisible to someone reading the application code. Document them.
Bulk loads with triggers are slow. Teams often disable them during imports.

Because of these costs, think about alternatives: a CHECK or foreign key for validation, a generated column for a derived value, a view or query for a count, and application code or a stored procedure for multi-step logic. Triggers shine for audit trails that must capture every change, whichever application makes it.

Common mistakes

Putting heavy logic in a trigger, slowing every write.
Forgetting DELIMITER in the client.
Trying to change NEW in an AFTER trigger.
Not covering UPDATE and DELETE when keeping a summary in sync.
Forgetting the trigger exists and wondering where the extra rows come from.

Exercise

Create a table deleted_customers(id, first_name, email, deleted_at) and a trigger that copies a customer's id, first name and email into it right before the customer is deleted. Delete customer 12 (Liam, who has no orders) and check the log.

Show one possible answer

sql

DROP TABLE IF EXISTS deleted_customers;
CREATE TABLE deleted_customers (
  id INT, first_name VARCHAR(50), email VARCHAR(100),
  deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DROP TRIGGER IF EXISTS trg_customers_archive;
DELIMITER //
CREATE TRIGGER trg_customers_archive BEFORE DELETE ON customers FOR EACH ROW
BEGIN
  INSERT INTO deleted_customers (id, first_name, email) VALUES (OLD.id, OLD.first_name, OLD.email);
END //
DELIMITER ;

DELETE FROM customers WHERE id = 12;
SELECT id, first_name, email FROM deleted_customers;
DROP TRIGGER IF EXISTS trg_customers_archive;

result

+------+------------+-------------------------+
| id   | first_name | email                   |
+------+------------+-------------------------+
|   12 | Liam       | liam.obrien@example.com |
+------+------------+-------------------------+

Key points

A trigger is SQL that MySQL runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE on a table.
Inside a trigger, NEW holds the incoming row values and OLD holds the previous values (OLD for UPDATE and DELETE, NEW for INSERT and UPDATE).
Typical uses are audit logs, keeping a derived value in sync and rejecting bad data with SIGNAL.
Triggers run invisibly, which makes them hard to debug. Use them sparingly, keep them short, and prefer constraints or application logic when those are enough.

Code that fires by itself

An audit log

BEFORE triggers: validate and fix

Keeping a derived value in sync

Managing triggers

Limits and cautions

Common mistakes

Exercise

Key points

Check yourself