What is a database trigger? | Interview Question

Q: What is a database trigger?

**Target Audience:** Beginners who know `INSERT` / `UPDATE` / `DELETE` and want to understand code that runs "by itself"; candidates who need BEFORE vs AFTER, row vs statement and the trade-offs. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the runnable examples) and PostgreSQL 16.15 (statement-level triggers, PL/pgSQL trigger functions, ordering, TRUNCATE and timing tests), both run for this page. MySQL notes are cited. > **How to read this doc:** Each idea is explained in plain words first, then tagged `📌 Interview term:` with the vocabulary interviewers use. Every result and error below came from an actual run; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A shop has a rule: "whenever anyone changes a price tag, write the old and new price in the logbook". The staff are busy and forget. So the owner installs a little machine on the price-tag printer: every time a new tag is printed, the machine writes

The problem

Question presented to candidate: "What is a database trigger? Walk me through BEFORE versus AFTER, row-level versus statement-level, and tell me when you would and would not use one."

What a strong answer should cover:

A trigger is code the database runs automatically when an INSERT, UPDATE or DELETE (in PostgreSQL also TRUNCATE) happens on a table; nobody calls it, and it runs inside the same transaction as the statement
Timing: BEFORE can change or reject the incoming row (NEW), AFTER sees the final row and is used for audit logs and side tables, INSTEAD OF makes views writable
Granularity: FOR EACH ROW fires once per affected row with OLD / NEW; FOR EACH STATEMENT fires once per statement, even for zero rows (PostgreSQL; SQLite only has row triggers)
Good uses: audit trails, updated_at stamps, keeping a denormalized counter or summary table in sync, enforcing a rule a CHECK cannot express
Costs: hidden logic that surprises readers, per-row overhead on bulk writes, cascades and recursion, ordering between several triggers, engine differences (MySQL error 1442)

Code / implementation expected: Optional — a small audit trigger with OLD / NEW, and an explanation of what fires when.

Schema used by the examples:

trigger

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know INSERT / UPDATE / DELETE and want to understand code that runs "by itself"; candidates who need BEFORE vs AFTER, row vs statement and the trade-offs. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the runnable examples) and PostgreSQL 16.15 (statement-level triggers, PL/pgSQL trigger functions, ordering, TRUNCATE and timing tests), both run for this page. MySQL notes are cited.

How to read this doc: Each idea is explained in plain words first, then tagged 📌 Interview term: with the vocabulary interviewers use. Every result and error below came from an actual run; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A shop has a rule: "whenever anyone changes a price tag, write the old and new price in the logbook". The staff are busy and forget. So the owner ins

The solution is hidden so you can try first

8 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLAFTER UPDATE trigger: write every real price change to an audit table

SQLBEFORE INSERT trigger with WHEN and RAISE(IGNORE): silently skip bad rows

PostgreSQL: a row-level trigger fires per row, a statement-level trigger once per statement

CREATE FUNCTION count_fires() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE trigger_log SET fires = fires + 1 WHERE level = lower(TG_LEVEL);
  RETURN NULL;  -- the return value of an AFTER trigger is ignored
END;
$$;

CREATE TRIGGER per_row AFTER UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION count_fires();
CREATE TRIGGER per_statement AFTER UPDATE ON products
  FOR EACH STATEMENT EXECUTE FUNCTION count_fires();

UPDATE products SET price = price + 1 WHERE price < 100;  -- 4 rows, 1 statement
UPDATE products SET price = price + 1 WHERE id = 99;      -- 0 rows, 1 statement

SELECT level, fires FROM trigger_log ORDER BY level;

PostgreSQL: a BEFORE trigger rewrites the incoming row

CREATE FUNCTION tidy_product() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.name := initcap(trim(NEW.name));   -- a BEFORE trigger may rewrite the row
  IF NEW.stock < 0 THEN
    RAISE EXCEPTION 'stock cannot be negative for %', NEW.name;
  END IF;
  RETURN NEW;                            -- returning NULL would skip the row
END;
$$;

CREATE TRIGGER tidy_before_write
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION tidy_product();

INSERT INTO products (id, name, price, stock) VALUES (6, '   usb HUB  ', 25, 12);
UPDATE products SET name = 'wireless mouse' WHERE id = 2;

SELECT id, name, price, stock FROM products WHERE id IN (2, 6) ORDER BY id;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.