Triggers and Their Limits

A trigger runs FOR EACH ROW of an INSERT, UPDATE or DELETE on one table, BEFORE or AFTER the row is written. OLD.col is the row as it was, NEW.col as it will be; only a BEFORE trigger may assign NEW.col, and a SIGNAL there cancels the change. Here cancelling an order restocks its books, and every stock change is audited:

A guard, a restock and an audit triggerSQL

CREATE TABLE stock_audit (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED,
  old_stock INT, new_stock INT, changed_by VARCHAR(100), changed_at DATETIME DEFAULT NOW());
DELIMITER //
CREATE TRIGGER orders_guard BEFORE UPDATE ON orders FOR EACH ROW
  IF OLD.status = 'shipped' AND NEW.status = 'cancelled' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A shipped order cannot be cancelled';
  END IF //
CREATE TRIGGER orders_restock AFTER UPDATE ON orders FOR EACH ROW
  IF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN
    UPDATE products AS p JOIN order_items AS i ON i.product_id = p.id
    SET p.stock = p.stock + i.quantity WHERE i.order_id = NEW.id;
  END IF //
CREATE TRIGGER products_audit AFTER UPDATE ON products FOR EACH ROW
  IF NEW.stock <> OLD.stock THEN
    INSERT INTO stock_audit (product_id, old_stock, new_stock, changed_by)
    VALUES (NEW.id, OLD.stock, NEW.stock, CURRENT_USER());
  END IF //
DELIMITER ;
CALL place_order(8, '[{"id": 7, "qty": 3}]', @id);
UPDATE orders SET status = 'cancelled' WHERE id = @id;
UPDATE orders SET status = 'cancelled' WHERE id = 1;
SELECT product_id, old_stock, new_stock, changed_by FROM stock_audit;

Output

ERROR 1644 (45000): A shipped order cannot be cancelled
+------------+-----------+-----------+----------------+
| product_id | old_stock | new_stock | changed_by     |
+------------+-----------+-----------+----------------+
|          7 |        59 |        56 | root@localhost |
|          7 |        56 |        59 | root@localhost |
+------------+-----------+-----------+----------------+

The first audit row comes from place_order, which knows nothing about auditing, the second from orders_restock firing products_audit. It all belongs to the triggering statement, so any error rolls the statement back. The limits:

A trigger cannot write to its own table (error 1442, Can't update table 'products' in stored function/trigger), and setting NEW.col in an AFTER trigger is error 1362.
There are no statement-level triggers, foreign-key actions such as ON DELETE CASCADE fire none, and a row-based replica applies logged rows without running its own.
FOLLOWS or PRECEDES orders triggers that share a timing and event. PHP code cannot see triggers, so keep them short and check SHOW TRIGGERS.

MENU

Triggers and Their Limits