SQL Triggers Explained: BEFORE and AFTER Triggers, Audit Logs and Pitfalls

Finance asks who changed a product’s price, from what, and when. The application has a dozen code paths that update prices, plus scripts and admin tools. Adding logging to every one of them is fragile. SQL triggers attach the logic to the table itself, so every price change is recorded, no matter where it came from.

We built the examples in SQLite 3.50 and ran them; outputs below are from that run. Trigger syntax varies more than most SQL between databases, so we note the differences.

What is a trigger?

A trigger is a named block of SQL stored in the database and tied to a table and an event. When that event happens, such as an update to the products table, the database runs the trigger automatically, inside the same transaction as the change. Nobody calls a trigger directly.

Anatomy of a trigger

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

The pieces answer four questions: when (before or after), what event, which table and which rows (an optional condition). SQLite triggers are always row-level, running once per affected row.

OLD and NEW

Comparing OLD.price and NEW.price is how a trigger knows what actually changed. PostgreSQL and MySQL use the same names; SQL Server uses tables called inserted and deleted instead.

An AFTER audit trigger

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INT, stock INT);
CREATE TABLE price_audit (product_id INT, old_price INT, new_price INT, changed_at TEXT);

CREATE TRIGGER trg_price_audit AFTER UPDATE OF price ON products
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_audit VALUES (OLD.id, OLD.price, NEW.price, datetime('now'));
END;

We then ran three updates: the price of the pen from 20 to 25, the same price again, and a stock change only.

SELECT product_id, old_price, new_price FROM price_audit;
-- [(1, 20, 25)]

Only the real price change was logged. UPDATE OF price ignored the stock-only update, and WHEN OLD.price <> NEW.price ignored the update that set the same value. Without those two filters, an audit table quickly fills with noise. AFTER triggers suit logging because the change has already been accepted.

A BEFORE validation trigger

CREATE TRIGGER trg_no_negative_price BEFORE UPDATE OF price ON products
WHEN NEW.price < 0
BEGIN
  SELECT RAISE(ABORT, 'price cannot be negative');
END;

UPDATE products SET price = -5 WHERE id = 1;
-- price cannot be negative

A BEFORE trigger can stop a change before it happens. For a simple rule like this, a CHECK constraint is simpler and clearer. Triggers earn their place for rules a CHECK cannot express, such as rules that look at other tables:

CREATE TRIGGER trg_stock_check BEFORE INSERT ON order_items
WHEN (SELECT stock FROM products WHERE id = NEW.product_id) < NEW.qty
BEGIN
  SELECT RAISE(ABORT, 'not enough stock');
END;

INSERT INTO order_items (product_id, qty) VALUES (2, 5);   -- ink has 2 in stock
-- not enough stock

The order row was not created; the table still held just one order afterwards. In PostgreSQL and MySQL, a BEFORE trigger can also change NEW values, for example to trim text or set an updated_at timestamp, before the row is saved.

Keeping data in sync

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

INSERT INTO order_items (product_id, qty) VALUES (1, 3);
SELECT name, stock FROM products;
-- [('Pen', 6), ('Ink', 2)]

Ordering 3 pens reduced stock from 9 to 6 automatically. This is convenient and guarantees consistency for every insert path. It is also exactly the kind of hidden behaviour that surprises developers later, which we come back to below.

Bulk updates and row-level triggers

UPDATE products SET price = price + 1;   -- 2 products
SELECT COUNT(*) FROM price_audit;        -- 3 (1 earlier + 2 new)

A single UPDATE statement that touched two rows fired the row-level audit trigger twice. On a table with a million rows, one bulk update means a million trigger executions and a million audit rows, which can make a quick maintenance query very slow. PostgreSQL and SQL Server also support statement-level triggers, which run once per statement and can see all changed rows together.

Triggers and transactions

BEGIN;
INSERT INTO order_items (product_id, qty) VALUES (1, 1);   -- trigger reduces stock
ROLLBACK;
SELECT stock FROM products WHERE id = 1;   -- [(6,)]

Triggers run inside the same transaction as the statement that fired them. When we rolled back, both the order and the stock change it triggered were undone, leaving stock at 6. This is an important guarantee: the main change and its side effects succeed or fail together. It also means a slow or failing trigger slows down or fails the original statement.

Dialect differences

The SQL Server difference catches many people: because its triggers fire once per statement, code that assumes a single row will silently handle only one of many changed rows. Always write SQL Server triggers to work with sets. We did not run the non-SQLite databases for this article.

Pitfalls

• Hidden logic: a developer reading application code cannot see that inserting an order changes stock. Document triggers where people will look.
• Cascades: triggers that update tables with their own triggers create chains that are hard to follow, and recursion in some cases.
• Performance: row-level work on bulk operations, as we saw, and extra locking.
• Bypassed paths: some bulk-load tools and commands skip triggers; for example, TRUNCATE does not fire row-level delete triggers. See DELETE vs TRUNCATE vs DROP.
• Migrations: renaming a column can break a trigger that is not part of the code review.

Alternatives to triggers

• Constraints for simple validation.
• Application services that wrap related changes in one transaction, keeping logic visible.
• Change data capture (CDC) tools that read the database log for auditing and syncing, without slowing writes.
• Generated or computed columns for values derived from the same row.
• Views for derived data that does not need to be stored.

Testing triggers

Treat triggers as code. For each one, test the event that should fire it, the event that should not (same value, other column), a multi-row statement, the failure path, and a rollback. Those five tests, which mirror our examples, catch most trigger bugs. Our SQL questions for testers include more database checks.

Managing triggers over time

Triggers live in the database, so they need the same care as application code:

• Keep them in version control as part of your migrations, never created by hand in production.
• List them regularly. Every database can show its triggers, such as sqlite_master in SQLite, information_schema.triggers in MySQL and PostgreSQL, or sys.triggers in SQL Server.
• Replace, do not stack. To change a trigger, drop and recreate it, with DROP TRIGGER IF EXISTS, in the same migration.
• Document the side effects next to the table definition and in the code that writes to the table, so the next developer is not surprised.
• Disable deliberately. Some bulk loads disable triggers for speed; if you do this, run the missing logic afterwards and record that you did.

A short comment at the top of each trigger explaining why it exists saves hours of investigation later.

Final thoughts

SQL triggers guarantee that certain things happen whenever data changes, whatever code made the change. Use AFTER triggers for audit logs, BEFORE triggers for validation that constraints cannot express, and filter with UPDATE OF and WHEN to avoid noise. Keep each trigger small and documented, remember that row-level triggers multiply on bulk updates, and test them like any other code.

Frequently asked questions

Keep learning