Triggers

Why triggers?

Some things must happen every time data changes, no matter which app or person changed it: write an audit row when a price changes, reduce stock when an item is sold, refuse a change that breaks a rule. If you rely on every app to remember, one day one app forgets. A trigger puts the rule inside the database, where nobody can skip it.

The parts of a trigger

An audit log in SQLite

An audit log is a table that records who changed what and when. Here every price change is copied into price_log automatically:

CREATE TABLE price_log (product_id INTEGER, old_price INTEGER, new_price INTEGER);

CREATE TRIGGER log_price
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN NEW.price <> OLD.price
BEGIN
  INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price);
END;

UPDATE products SET price = price + 50 WHERE category = 'Kitchen';
SELECT product_id, old_price, new_price FROM price_log;
-- 3 | 450  | 500
-- 4 | 1450 | 1500

Keeping stock in sync

CREATE TRIGGER reduce_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
  UPDATE products SET stock = stock - NEW.qty WHERE id = NEW.product_id;
END;

INSERT INTO orders VALUES (109, 4, '2025-04-05', 'Placed');
INSERT INTO order_items VALUES (109, 3, 2, 450), (109, 7, 1, 1999);
SELECT id, name, stock FROM products WHERE id IN (3, 7);
-- 3 | Steel Tiffin Box  | 10   (was 12)
-- 7 | Bluetooth Earbuds | 24   (was 25)

Stopping bad changes with RAISE

Inside a SQLite trigger, RAISE(ABORT, 'message') cancels the statement with an error and undoes what that statement already changed. RAISE(IGNORE) silently skips the current row instead. Use them in BEFORE triggers to guard rules that a CHECK constraint cannot express.

CREATE TRIGGER no_negative_stock
BEFORE UPDATE OF stock ON products
FOR EACH ROW
WHEN NEW.stock < 0
BEGIN
  SELECT RAISE(ABORT, 'stock cannot go below zero');
END;

UPDATE products SET stock = stock - 5 WHERE id = 8;
-- Error: stock cannot go below zero   (the Power Bank has 0 in stock)

Other engines write the same ideas differently. PostgreSQL triggers call a trigger function (RETURNS trigger) and support FOR EACH STATEMENT as well as FOR EACH ROW. MySQL rejects a change with SIGNAL SQLSTATE '45000'. SQL Server triggers fire once per statement and read the changed rows from two pseudo-tables named inserted and deleted.

-- PostgreSQL only
CREATE FUNCTION log_price_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO price_log (product_id, old_price, new_price, changed_at)
  VALUES (OLD.id, OLD.price, NEW.price, now());
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_price_log
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION log_price_change();

The dangers

• Hidden logic: someone runs one UPDATE and three other tables change. Debugging feels like magic, the bad kind. Document every trigger.
• Cascades: a trigger updates a table that has its own trigger, which updates another. Chains are slow and hard to follow.
• Speed: a row-level trigger runs for every row, so a bulk update of a million rows runs it a million times.
• Surprises in tools: bulk loads, replication or TRUNCATE may skip some triggers, depending on the engine.

Common mistakes

• Using OLD in an INSERT trigger or NEW in a DELETE trigger. There is no such row. Fix: INSERT has only NEW, DELETE only OLD.
• Logging rows that did not really change. Fix: add WHEN NEW.price <> OLD.price (or IS DISTINCT FROM in PostgreSQL).
• Putting heavy business workflows in triggers. Fix: keep triggers small (audit, counters, guards) and put workflows in app code.
• Expecting a trigger to fire for rows you load another way. Fix: check your engine's documentation for bulk loads and TRUNCATE.