Triggers

Article · 3:00

Code that runs automatically on a write, what it is genuinely good for, and why it is easy to regret.

A trigger runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE on one table. Inside it, NEW holds the incoming row and OLD holds the previous one - so an insert trigger has only NEW, a delete trigger only OLD, and an update trigger both.

The timing determines what you can do. A BEFORE trigger can modify NEW, so it is where you normalise a value, compute a derived column or reject a write by signalling an error. An AFTER trigger cannot change the row - it has already been written - so it is where you record what happened.

The genuine use is auditing. An AFTER UPDATE trigger that writes the old and new values into an audit table captures every change no matter which application made it, which is exactly the guarantee application-level logging cannot give.

The reasons to be careful are real. A trigger is invisible: somebody debugging an INSERT has no reason to suspect that three other tables changed. It runs inside your transaction, so slow trigger work makes every write slow and extends every lock. It cascades: a trigger that writes to a table with its own trigger can produce a chain nobody has traced. And it cannot be tested from the application's test suite in any natural way. Use them for auditing and for invariants that must hold whatever writes the row - and resist putting business logic in one.

DELIMITER $$

-- BEFORE: can change NEW, and can reject the write outright.
CREATE TRIGGER trg_products_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  SET NEW.sku  = UPPER(TRIM(NEW.sku));          -- normalise
  SET NEW.name = TRIM(NEW.name);

  IF NEW.price < 0 THEN
    -- The way a trigger refuses a write.
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Price cannot be negative';
  END IF;
END$$

-- AFTER: the row is already written, so this records what happened.
-- Every change is captured whichever application made it, which is the
-- guarantee application-level logging cannot give.
CREATE TRIGGER trg_products_after_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_audit (product_id, old_price, new_price, changed_by, changed_at)
    VALUES (NEW.id, OLD.price, NEW.price, CURRENT_USER(), NOW());
  END IF;
END$$

DELIMITER ;

-- What exists, and what it does. Worth running on any schema you inherit,
-- because a trigger is invisible from the statement that fires it.
SHOW TRIGGERS FROM shop;

SELECT trigger_name, event_manipulation, action_timing, event_object_table
FROM information_schema.triggers
WHERE trigger_schema = 'shop';