Triggers

A trigger is SQL that the database runs automatically when something happens to a table — a row inserted, updated or deleted. Triggers can keep audit logs, maintain derived values, and enforce rules too complex for a CHECK constraint. Used sparingly, they're powerful; overused, they make systems hard to understand.

An audit log

CREATE TABLE price_history (
  product_id INTEGER NOT NULL,
  old_price DECIMAL(8, 2),
  new_price DECIMAL(8, 2),
  changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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

UPDATE products SET price = 27.90 WHERE id = 1;
UPDATE products SET price = price * 0.9 WHERE category = 'Toys';

SELECT product_id, old_price, round(new_price, 2) AS new_price FROM price_history;

The trigger fires after every update of price. Inside it, OLD is the row before the change and NEW is the row after. The application never has to remember to write the log.

Anatomy of a trigger

Maintaining stock automatically

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

INSERT INTO orders (id, customer_id, order_date, status) VALUES (16, 2, '2026-03-12', 'pending');
INSERT INTO order_items VALUES (16, 7, 3, 12.50);

SELECT name, stock FROM products WHERE id = 7;

Every new order line lowers the product's stock — 200 became 197.

Enforcing a rule

CREATE TRIGGER no_overselling
BEFORE INSERT ON order_items
FOR EACH ROW
WHEN (SELECT stock FROM products WHERE id = NEW.product_id) < NEW.quantity
BEGIN
  SELECT RAISE(ABORT, 'Not enough stock');
END;

INSERT INTO order_items VALUES (14, 6, 1, 89.00);

Error: Not enough stock

The Mechanical Keyboard has no stock, so the trigger aborts the insert with a message. A CHECK constraint can't do this, because it can only look at the row being written, not at other tables. (In PostgreSQL, trigger logic lives in a function written in PL/pgSQL; SQL Server uses THROW; MySQL uses SIGNAL.)

Keeping a timestamp current

ALTER TABLE customers ADD COLUMN updated_at TEXT;

CREATE TRIGGER touch_customer
AFTER UPDATE OF name, email, city ON customers
FOR EACH ROW
BEGIN
  UPDATE customers SET updated_at = '2026-03-12 10:00:00' WHERE id = NEW.id;
END;

UPDATE customers SET city = 'Braga' WHERE id = 9;

SELECT name, city, updated_at FROM customers WHERE id = 9;

A real trigger would use CURRENT_TIMESTAMP; a fixed value keeps this example's output stable. MySQL can do this without a trigger: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

Dropping triggers

DROP TRIGGER IF EXISTS log_price_change;

Use triggers sparingly

• Hidden behaviour: a simple INSERT may silently change other tables, surprising anyone who doesn't know the trigger exists.
• Performance: row-level triggers run once per row, slowing bulk operations.
• Chains: triggers that fire other triggers are hard to debug.

Good uses are audit trails, timestamps, and integrity rules that must hold no matter who writes the data. Business workflows (sending emails, calculating discounts) usually belong in application code.

Try it yourself

Create a trigger that records every deleted customer in an archive table.