---
title: Triggers
source: 'https://academia.sh/en/courses/advanced-sql/triggers'
course: 'Advanced SQL'
language: en
updated: '2026-08-23T07:00:39+00:00'
license: 'CC BY-SA 4.0'
---

# Triggers

Trigger definition, running per row with BEFORE and AFTER, audit logging and updating derived state, enforcing a rule with RAISE, INSTEAD OF on a view, and the risk of recursion.

In the previous lesson, server-side code was always called: the procedure by `CALL`, the
function by name inside a query. Because it was read from where it was called, what it did
was visible.

A **trigger** is not called. When an insert, update, or delete happens on a table, the
engine runs it on its own. This is a powerful tool and just as much of a trap: an ordinary
looking `UPDATE` statement can write to tables that never appear in its text.

## Definition and Timing

A trigger is defined by three things: which table it watches, which event it runs on, and
whether it runs before or after that event.

- `BEFORE`: runs before the statement processes the row. Used to correct a value or reject
  the operation.
- `AFTER`: runs after the row has been processed. Used to write to other tables.
- `INSTEAD OF`: the row is not processed; the trigger runs **in its place**. This is for
  views.

The `FOR EACH ROW` form states that the trigger runs separately for every affected row.
Two special names can be used inside the body: `NEW`, which gives the row's new state, and
`OLD`, which gives its old state. `OLD` is undefined on an insert event, and `NEW` is
undefined on a delete event.

The first use is a pair of triggers doing two things at once: updating derived state and
writing an audit record.

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT NOT NULL DEFAULT 1);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
CREATE TABLE audit(id INTEGER PRIMARY KEY, event TEXT, loan_id INT, detail TEXT);
INSERT INTO book VALUES (1,'Lost Time',1),(2,'Silent House',1);

CREATE TRIGGER loan_opened AFTER INSERT ON loan
FOR EACH ROW
BEGIN
  UPDATE book SET on_shelf = 0 WHERE id = NEW.book_id;
  INSERT INTO audit(event, loan_id, detail)
    VALUES ('opened', NEW.id, 'book ' || NEW.book_id || ' member ' || NEW.member_id);
END;

CREATE TRIGGER loan_closed AFTER UPDATE OF returned ON loan
FOR EACH ROW WHEN OLD.returned IS NULL AND NEW.returned IS NOT NULL
BEGIN
  UPDATE book SET on_shelf = 1 WHERE id = NEW.book_id;
  INSERT INTO audit(event, loan_id, detail)
    VALUES ('closed', NEW.id, 'returned ' || NEW.returned);
END;

INSERT INTO loan(id, book_id, member_id, pickup) VALUES (1, 1, 4, '2024-03-11');
UPDATE loan SET returned = '2024-03-20' WHERE id = 1;

SELECT id, title, on_shelf FROM book ORDER BY id;
SELECT id, event, loan_id, detail FROM audit ORDER BY id;
SQL
```

```text
┌────┬──────────────┬──────────┐
│ id │    title     │ on_shelf │
├────┼──────────────┼──────────┤
│ 1  │ Lost Time    │ 1        │
│ 2  │ Silent House │ 1        │
└────┴──────────────┴──────────┘
┌────┬────────┬─────────┬─────────────────────┐
│ id │ event  │ loan_id │       detail        │
├────┼────────┼─────────┼─────────────────────┤
│ 1  │ opened │ 1       │ book 1 member 4     │
│ 2  │ closed │ 1       │ returned 2024-03-20 │
└────┴────────┴─────────┴─────────────────────┘
```

Two statements were written: one `INSERT` and one `UPDATE`. Four things happened: the loan
record opened, the book left the shelf, an audit row was written; then the record closed,
the book returned to the shelf, a second audit row was written. The final `on_shelf` value
being 1 shows that the second trigger undid the first trigger's effect.

The `WHEN` clause makes the trigger run only for particular rows. The condition here isolates
updates where the loan actually **closed**; an update that corrects the return date will not
put the book back on the shelf a second time.

The `AFTER UPDATE OF returned` form ties the trigger to a single column. Without a column
list, the trigger would run on an update to any column of the table — usually an unwanted
breadth.

## Enforcing a Rule

`BEFORE` triggers can reject an operation. Standard SQL defines a signal statement for
this; on engines its counterpart is an error-raising function. The trigger below blocks
lending a book that is not on the shelf:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT NOT NULL DEFAULT 1);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO book VALUES (1,'Lost Time',0),(2,'Silent House',1);

CREATE TRIGGER loan_check BEFORE INSERT ON loan
FOR EACH ROW
WHEN (SELECT on_shelf FROM book WHERE id = NEW.book_id) = 0
BEGIN
  SELECT RAISE(ABORT, 'book not on shelf');
END;

INSERT INTO loan(id, book_id, member_id, pickup) VALUES (1, 2, 4, '2024-03-11');
INSERT INTO loan(id, book_id, member_id, pickup) VALUES (2, 1, 7, '2024-03-11');
SELECT id, book_id, member_id FROM loan ORDER BY id;
SQL
```

```text
Runtime error near line 13: book not on shelf (19)
┌────┬─────────┬───────────┐
│ id │ book_id │ member_id │
├────┼─────────┼───────────┤
│ 1  │ 2       │ 4         │
└────┴─────────┴───────────┘
```

The loan for the book on the shelf went through; the one for the unavailable book was
rejected with the message the trigger gave. The message text is written inside the trigger;
the error reaching the application arrives by the same path as a constraint violation error.

This tool does not replace constraints. If a rule can be expressed by a single column's own
value, use a `CHECK` constraint; if it can be expressed by the existence of a row in another
table, use a foreign key — both are declarative, known to the planner, and cheaper than a
trigger. A trigger is only needed for rules that examine multiple tables together.

## On a View

A complex view cannot be updated directly: the engine cannot derive how a change to a view
row should be reflected in the underlying tables. An `INSTEAD OF` trigger defines that
mapping by hand:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES (1,1,4,'2024-03-01',NULL),(2,3,7,'2024-03-04',NULL);

CREATE VIEW open_loans AS SELECT id, book_id, member_id, pickup FROM loan WHERE returned IS NULL;

CREATE TRIGGER close_open_loan INSTEAD OF DELETE ON open_loans
FOR EACH ROW
BEGIN
  UPDATE loan SET returned = '2024-03-31' WHERE id = OLD.id;
END;

DELETE FROM open_loans WHERE id = 1;
SELECT id, pickup, COALESCE(returned,'(open)') AS returned FROM loan ORDER BY id;
SQL
```

```text
┌────┬────────────┬────────────┐
│ id │   pickup   │  returned  │
├────┼────────────┼────────────┤
│ 1  │ 2024-03-01 │ 2024-03-31 │
│ 2  │ 2024-03-04 │ (open)     │
└────┴────────────┴────────────┘
```

The statement that was written was a delete; what happened is an update. The row still sits
in the table, only its return date got filled in. From the view's angle it is correct — the
row is no longer an "open loan" — but the statement's text does not say so. This is the
clearest example of an implicit side effect.

## Recursion

A trigger that updates the very table it watches can run again. Engines either block this
outright or stop it with a depth limit:

```bash
sqlite3 -box -header <<'SQL'
PRAGMA recursive_triggers = ON;
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, counter INT NOT NULL DEFAULT 0);
INSERT INTO book VALUES (1,'Lost Time',0);

CREATE TRIGGER counter_increment AFTER UPDATE OF counter ON book
FOR EACH ROW
BEGIN
  UPDATE book SET counter = NEW.counter + 1 WHERE id = NEW.id;
END;

UPDATE book SET counter = 1 WHERE id = 1;
SELECT id, counter FROM book;
SQL
```

```text
Runtime error near line 11: too many levels of trigger recursion
┌────┬─────────┐
│ id │ counter │
├────┼─────────┤
│ 1  │ 0       │
└────┴─────────┘
```

A single `UPDATE` was written; the trigger called itself, and the chain was cut off at the
recursion depth limit. Because the whole statement was rolled back, the counter stayed at
zero — atomicity holds here too.

Whether recursion is on or off by default varies by engine; while off, the same definition
runs silently for a single pass and the problem stays invisible. This is one of the places
where the behavior of a schema carried between two engines can change.

## The Cost of an Implicit Side Effect

The benefit triggers provide is clear: a rule applies at all times, independent of the code
that triggered it. Whichever layer of the application the change comes from, an audit row
gets written whenever a loan opens. This guarantees the rule cannot be skipped.

The cost is that the statement's text and its effect diverge. This has concrete
consequences:

- **Debugging.** The source of an unexpected row change does not appear in query logs; the
  triggers defined in the schema have to be read.
- **Order ambiguity.** The order in which multiple triggers bound to the same event run is
  not defined by the standard; writing two triggers that depend on each other is fragile.
- **Batch operations.** A trigger that runs per row runs a hundred thousand times on a
  hundred-thousand-row update; on some bulk-loading paths it may not run at all.
- **Invisible cost.** The plan of a statement that looks simple also includes the queries
  inside the trigger body.

The measure is this: a trigger fits rules that protect **data integrity** and must not be
skipped — audit logging, consistency of a derived column, multi-table constraints. Workflow
decisions — sending a notification, calculating a fee, calling an external system — belong
in the application layer. When triggers are written, they should be kept short and
documented alongside the schema.

## Summary

- A trigger is not called; the engine runs it when an event occurs on the table it watches,
  and it reaches the row's two states through `NEW` and `OLD`.
- `BEFORE` is for correcting and rejecting, `AFTER` for writing to other tables, `INSTEAD
  OF` for updating a view; the `WHEN` clause narrows when it runs.
- Rules expressible with a single column should be declared with `CHECK` or a foreign key;
  a trigger is needed for multi-table rules.
- A trigger that updates its own table produces recursion; the engine cuts it off with a
  depth limit and the whole statement is rolled back.
- An implicit side effect separates a statement's text from its effect, affecting
  debugging, ordering assumptions, and batch-operation cost.

## Next Step

Across this topic, queries focused on giving the correct result: a subquery selected the
right set, a window saw the right frame, a transaction committed at the right moment.
Correctness is not the only measure. The Correlated Subqueries lesson measured two forms
producing the same result by visiting 200 rows against 25, with the access path making the
difference; in that lesson, adding an index made the plan say `SEARCH` instead of `SCAN`.
The next topic starts from that point. Its first lesson establishes what an index is and how
it lowers the cost of searching; from there it moves to reading a query plan and to
rewrites that give the same result for less work.