SQL Triggers: BEFORE, AFTER and INSTEAD OF

Suppose every price change has to be recorded. You could ask every developer to remember to write to a log table, or you can let the database do it. This SQLite trigger copies the old and new price into price_changes whenever a product's price is updated:

CREATE TABLE price_changes (
  product_id INTEGER,
  old_price  REAL,
  new_price  REAL,
  changed_at TEXT
);

CREATE TRIGGER trg_products_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_changes (product_id, old_price, new_price, changed_at)
  VALUES (OLD.id, OLD.price, NEW.price, datetime('now'));
END;

From then on, UPDATE products SET price = 49.99 WHERE id = 7 also inserts a row into price_changes, whichever application, script or person ran the UPDATE. The trigger is part of the table, not of any one program.

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF column] | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
  -- statements
END;

OLD and NEW

SQLite and MySQL write OLD.col and NEW.col, PostgreSQL the same inside its trigger function, Oracle :OLD.col and :NEW.col. SQL Server has no per-row variables: it gives the trigger two tables, inserted and deleted, holding all affected rows.

BEFORE: check or adjust the incoming row

A BEFORE trigger runs before the row is written, so it can reject it. This one refuses a product price below cost:

CREATE TRIGGER trg_products_price_check
BEFORE UPDATE OF price ON products
FOR EACH ROW
WHEN NEW.price < NEW.cost
BEGIN
  SELECT RAISE(ABORT, 'price cannot be below cost');
END;

The rule it protects holds for every product in the Playground today:

SELECT COUNT(*) AS products,
       SUM(price < cost) AS priced_below_cost
FROM products;

▶ Run this query in the SQL Playground

A simple rule like this can also be a CHECK constraint (CHECK (price >= cost)), which is simpler and should be preferred. Triggers are for rules a constraint cannot express, such as ones that look at other tables.

AFTER: react to a change that happened

AFTER triggers suit audit logs and follow-up updates, because the change is already in the table. The price audit at the top of the page is an AFTER UPDATE trigger. Another example keeps the review count on products in sync:

CREATE TRIGGER trg_reviews_count
AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
  UPDATE products
  SET review_count = review_count + 1
  WHERE id = NEW.product_id;
END;

INSTEAD OF: make a view accept changes

INSTEAD OF triggers replace the statement with your own logic. Their usual job is letting a view accept INSERT, UPDATE or DELETE. A soft delete is a common example: "deleting" through the view only marks the row:

CREATE VIEW active_users AS
SELECT id, first_name, last_name, email
FROM users
WHERE status = 'active';

CREATE TRIGGER trg_active_users_delete
INSTEAD OF DELETE ON active_users
FOR EACH ROW
BEGIN
  UPDATE users SET status = 'inactive' WHERE id = OLD.id;
END;

After DELETE FROM active_users WHERE id = 3, user 3 is still in users, now with status inactive, and no longer appears in the view. The Playground already has both kinds of user:

SELECT status, COUNT(*) AS users
FROM users
GROUP BY status;

▶ Run this query in the SQL Playground

The same price audit in other databases:

-- PostgreSQL: the logic lives in a function
CREATE FUNCTION log_price_change() RETURNS trigger AS $$
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_changes VALUES (OLD.id, OLD.price, NEW.price, now());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW EXECUTE FUNCTION log_price_change();

-- MySQL
CREATE TRIGGER trg_products_price_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_changes VALUES (OLD.id, OLD.price, NEW.price, NOW());
  END IF;
END;

-- SQL Server: once per statement, all rows in inserted / deleted
CREATE TRIGGER trg_products_price_audit
ON products
AFTER UPDATE
AS
BEGIN
  INSERT INTO price_changes (product_id, old_price, new_price, changed_at)
  SELECT d.id, d.price, i.price, SYSDATETIME()
  FROM deleted d
  JOIN inserted i ON i.id = d.id
  WHERE i.price <> d.price;
END;

List existing triggers with SELECT name FROM sqlite_master WHERE type = 'trigger' in SQLite, SHOW TRIGGERS in MySQL, information_schema.triggers in PostgreSQL, and sys.triggers in SQL Server. Remove one with DROP TRIGGER name; dropping a table drops its triggers too.

Triggers are strongest for rules that must hold whichever program writes to the table. They are weakest for complex workflows: logic that runs invisibly on every write surprises people, is hard to test and slows down bulk changes. See stored procedures.

Five errors and how to avoid them.

CREATE TRIGGER t AFTER INSERT ON products ...
-- checks NEW.price and raises an error

CREATE TRIGGER t BEFORE INSERT ON products ...
-- or, simpler, a CHECK constraint

DECLARE @id INT = (SELECT id FROM inserted);
-- fails or picks one row when 50 rows change

INSERT INTO audit (...)
SELECT ... FROM inserted i JOIN deleted d ON d.id = i.id;

AFTER UPDATE ON products
BEGIN
  INSERT INTO price_changes ...;
END;

AFTER UPDATE OF price ON products
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_changes ...;
END;

AFTER UPDATE ON products
BEGIN
  UPDATE products SET updated_at = now() WHERE id = NEW.id;
END;

-- set the column in a BEFORE trigger on NEW instead,
-- or add WHEN OLD.updated_at IS NEW.updated_at

-- inserting an order also emails, charges a card
-- and updates five tables, all from triggers

-- triggers: audit and integrity only
-- application or procedure: the order workflow

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Check whether the Playground database has any triggers. Table: sqlite_master

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS triggers
   FROM sqlite_master
   WHERE type = 'trigger';

   ▶ Run this query in the SQL Playground

   The Playground has none. In your own SQLite database, triggers you create are listed here.

2. 2Medium A trigger is meant to keep products.review_count equal to the number of rows in reviews. Check how many products currently match. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS products,
          SUM(p.review_count = (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id)) AS counts_in_sync
   FROM products p;

   ▶ Run this query in the SQL Playground

   In the Playground the two are independent sample data, so they do not match. This is the kind of drift a trigger such as trg_reviews_count is meant to prevent.

3. 3Medium Before adding a BEFORE INSERT trigger that rejects salaries under 40,000, check whether any existing employee would break the rule. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS below_40000
   FROM employees
   WHERE salary < 40000;

   ▶ Run this query in the SQL Playground

   Check existing data before enforcing a new rule; a trigger only checks rows changed after it exists.

4. 4Medium An audit trigger would log every status change. Show how many orders are in each status today, the baseline such a log starts from. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT status, COUNT(*) AS orders
   FROM orders
   GROUP BY status
   ORDER BY orders DESC;

   ▶ Run this query in the SQL Playground

   Triggers record changes from the moment they are created; the starting state has to be captured separately.