Triggers

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.

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

┌────┬──────────────┬──────────┐
│ 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:

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

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:

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

┌────┬────────────┬────────────┐
│ 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:

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

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.