Triggers and Events

What You'll Learn

By the end of this lesson you will write an INSTEAD OF trigger that makes a read-only view writable, translating one insert into writes on two base tables while silently dropping the rows that fail a rule. Along the way you will be able to say which of RAISE's four resolutions to reach for, and what each one leaves behind when it fires — the difference between undoing a statement, undoing a whole transaction, and quietly skipping one row is the difference between three very different production incidents.

This lesson is optional, and it is the last one. The core path ended with the capstone; the four extension lessons after it, including this one, exist for jobs that call for them and can be taken in any order. Nothing earlier in the track depends on anything here.

A data event is any INSERT, UPDATE, or DELETE that changes a table. Triggers let the database respond to these events automatically, and Triggers and Automation already covered the essentials: creating triggers that fire BEFORE and AFTER an event, reading the OLD and NEW pseudo-rows, logging changes to an audit table, rejecting bad data with RAISE(ABORT, ...), archiving deleted rows into a shadow table, and inspecting triggers through sqlite_master. If any of that feels shaky, revisit it first — this lesson builds directly on top of it.

A note on the name. "Events" here means data events — the row-level INSERT/UPDATE/DELETE that fire a trigger. SQLite has no scheduled-event system (unlike MySQL's CREATE EVENT scheduler). There is no way in SQLite to say "run this every night at midnight"; every trigger is tied to a data change on a table.

What Triggers and Automation did not cover is the machinery that makes triggers genuinely expressive: filtering execution with WHEN, choosing how a rule fails with the different RAISE resolutions, narrowing a trigger to a specific set of columns, making otherwise read-only views writable with INSTEAD OF, and letting triggers fire each other recursively. Those are the subject of this lesson.

Filtering Execution with WHEN

A trigger fires on every matching event, but often you only care about some of those events. A WHEN clause is a condition evaluated after the trigger matches but before its body runs — if it is false, the body is skipped entirely. This is cheaper and clearer than wrapping the whole body in a conditional SELECT ... WHERE.

Here a trigger logs restocks but stays silent when stock goes down:

CREATE TABLE inventory (
  id INTEGER PRIMARY KEY,
  item TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE restock_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL,
  added_quantity INTEGER NOT NULL,
  logged_at TEXT DEFAULT (datetime('now'))
);

CREATE TRIGGER log_restock
AFTER UPDATE OF quantity ON inventory
FOR EACH ROW
WHEN NEW.quantity > OLD.quantity
BEGIN
  INSERT INTO restock_log (item_id, added_quantity)
  VALUES (NEW.id, NEW.quantity - OLD.quantity);
END;

INSERT INTO inventory (id, item, quantity) VALUES
  (1, 'Widget A', 50),
  (2, 'Widget B', 20),
  (3, 'Gadget X', 5);

UPDATE inventory SET quantity = 75 WHERE id = 1;
UPDATE inventory SET quantity = 10 WHERE id = 2;
UPDATE inventory SET quantity = 30 WHERE id = 3;

SELECT i.item, i.quantity, r.added_quantity
FROM inventory i
LEFT JOIN restock_log r ON r.item_id = i.id;

The update on Widget B lowered the quantity, so WHEN NEW.quantity > OLD.quantity was false and log_restock never ran for that row. Only genuine restocks appear in the log. The condition is evaluated per row, so in a multi-row update some rows can pass the filter while others are skipped.

A note on AUTOINCREMENT, which appears on the log tables in this lesson and nowhere earlier in the track. In SQLite an INTEGER PRIMARY KEY column already fills itself in when you omit it, choosing one more than the largest id currently in the table. Adding AUTOINCREMENT changes one thing: it guarantees a new id is never one that has been used before, even if the row holding it was deleted. That matters for a log, where a reused id would make two different events look like the same one. It costs a little — the database keeps an extra record of the highest id ever issued — so it is the right choice for audit tables and the wrong default everywhere else. The logged_at column beside it defaults to datetime('now'), so its values are whatever the clock read on this run and are not something to compare against.

Choosing How a Rule Fails: RAISE Resolutions

The previous lesson used RAISE(ABORT, message) to reject bad data. ABORT is only one of four conflict resolutions RAISE can apply, and each fails differently. The difference matters as soon as your trigger runs inside a larger transaction or a multi-row statement.

Resolution	What it does
`RAISE(ABORT, msg)`	Cancels the current statement and undoes only that statement's changes; earlier work in the transaction survives. This is the default and the safe choice.
`RAISE(FAIL, msg)`	Stops the current statement immediately but does not undo rows the statement already changed before the failure.
`RAISE(ROLLBACK, msg)`	Aborts the entire surrounding transaction, not just the statement — everything since `BEGIN` is undone.
`RAISE(IGNORE)`	Silently skips the current row and continues. Takes no message. The row simply doesn't get written.

IGNORE is the interesting one: it lets a trigger drop individual rows from a batch while letting the rest through. Here a BEFORE INSERT trigger silently skips rows with a non-positive amount instead of failing the whole insert:

CREATE TABLE deposits (
  id INTEGER PRIMARY KEY,
  account TEXT NOT NULL,
  amount REAL NOT NULL
);

CREATE TRIGGER skip_bad_deposits
BEFORE INSERT ON deposits
FOR EACH ROW
WHEN NEW.amount <= 0
BEGIN
  SELECT RAISE(IGNORE);
END;

INSERT INTO deposits (id, account, amount) VALUES (1, 'alice', 100.0);
INSERT INTO deposits (id, account, amount) VALUES (2, 'bob', 0.0);
INSERT INTO deposits (id, account, amount) VALUES (3, 'carol', 250.0);
INSERT INTO deposits (id, account, amount) VALUES (4, 'dave', -50.0);

SELECT id, account, amount FROM deposits ORDER BY id;

Rows 2 and 4 were silently dropped by RAISE(IGNORE) — no error was raised, and the surrounding inserts still succeeded. Only the valid deposits landed. Swap IGNORE for ABORT and the first bad row would instead cancel its own insert and surface an error; swap it for ROLLBACK and it would tear down the whole transaction.

There is one detail about IGNORE that is easy to get wrong and is worth stating plainly, because it changes how you structure a trigger. RAISE(IGNORE) abandons the rest of the trigger body it appears in, and nothing more. It does not cancel other triggers on the same event, and it does not mark the row as forbidden for anyone else — so a rule expressed as "one trigger to skip bad rows, another to write good ones" does not work: the second trigger still fires and still writes the row the first one meant to drop. Measured on this engine. The shape that does work puts the guard and the work in a single body, with the RAISE(IGNORE) above everything it is meant to prevent.

Predict

INSTEAD OF triggers replace the write they intercept, which is how a read-only view becomes writable. The script below tries to put one on an ordinary table rather than a view. What happens?

CREATE TABLE t (id INTEGER PRIMARY KEY, v INTEGER);

CREATE TRIGGER bad
INSTEAD OF INSERT ON t
BEGIN
SELECT 1;
END;

It is created successfully but never fires, because a table's inserts are handled directly and there is nothing for the trigger to stand in for.
It fails at CREATE TRIGGER time with 'Error: cannot create INSTEAD OF trigger on table: t'.
It is created and behaves like a BEFORE INSERT trigger, since INSTEAD OF and BEFORE both run ahead of the write.

Narrowing a Trigger to Specific Columns

AFTER UPDATE OF price restricts a trigger to a single column. The OF clause also accepts a list of columns: the trigger fires when an UPDATE touches any column in the list, and stays dormant for updates that only touch other columns. This keeps expensive audit or recomputation logic from running on writes it doesn't care about.

CREATE TABLE profiles (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  last_login TEXT
);

CREATE TABLE contact_audit (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  profile_id INTEGER NOT NULL,
  field_snapshot TEXT NOT NULL,
  changed_at TEXT DEFAULT (datetime('now'))
);

-- Fires only when email OR phone changes, never for last_login churn
CREATE TRIGGER audit_contact_changes
AFTER UPDATE OF email, phone ON profiles
FOR EACH ROW
BEGIN
  INSERT INTO contact_audit (profile_id, field_snapshot)
  VALUES (NEW.id, NEW.email || ' / ' || COALESCE(NEW.phone, '(none)'));
END;

INSERT INTO profiles (id, name, email, phone) VALUES
  (1, 'Alice', 'alice@old.com', '555-0001');

UPDATE profiles SET last_login = datetime('now') WHERE id = 1;  -- no audit
UPDATE profiles SET email = 'alice@new.com' WHERE id = 1;       -- audited
UPDATE profiles SET phone = '555-9999' WHERE id = 1;            -- audited

SELECT profile_id, field_snapshot FROM contact_audit ORDER BY id;

The last_login update produced no audit row because last_login is not in the OF list. Only the email and phone changes were recorded — two rows, not three. Frequent bookkeeping updates never touch the audit table.

INSTEAD OF Triggers: Writable Views

A view — the named, stored query you met in Views and CTEs — is read-only by default here. An INSERT aimed at one fails with Error: cannot modify <name> because it is a view, and the reason is not a missing feature: given one incoming row and two base tables underneath, nothing determines which columns belong where. An INSTEAD OF trigger fills that gap by saying so explicitly: it fires in place of the write on the view and translates it into concrete writes on the base tables. INSTEAD OF triggers exist only for views, never for tables.

Here a book_catalog view joins authors and books. An INSTEAD OF INSERT trigger makes it insertable, creating the author on demand and then the book:

CREATE TABLE authors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  author_id INTEGER NOT NULL,
  title TEXT NOT NULL
);

CREATE VIEW book_catalog AS
SELECT b.id AS book_id, a.name AS author_name, b.title AS title
FROM books b
JOIN authors a ON a.id = b.author_id;

CREATE TRIGGER insert_into_catalog
INSTEAD OF INSERT ON book_catalog
FOR EACH ROW
BEGIN
  INSERT OR IGNORE INTO authors (name) VALUES (NEW.author_name);
  INSERT INTO books (author_id, title)
  VALUES (
    (SELECT id FROM authors WHERE name = NEW.author_name),
    NEW.title
  );
END;

-- Insert through the VIEW, not the base tables
INSERT INTO book_catalog (author_name, title) VALUES ('Ada Lovelace', 'Notes on the Engine');
INSERT INTO book_catalog (author_name, title) VALUES ('Grace Hopper', 'On Compilers');
INSERT INTO book_catalog (author_name, title) VALUES ('Ada Lovelace', 'On Bernoulli Numbers');

SELECT author_name, title FROM book_catalog ORDER BY author_name, title;
SELECT COUNT(*) AS author_count FROM authors;

Three inserts through the view produced three books but only two authors — the second "Ada Lovelace" insert reused the existing author because of INSERT OR IGNORE on the unique name. The caller wrote to a single virtual table; the trigger did the work of splitting that across authors and books. You can add INSTEAD OF UPDATE and INSTEAD OF DELETE triggers the same way to make the view fully writable.

Recursive Triggers

When a trigger's body modifies a table, that modification is itself a data event — and can fire more triggers. Whether a trigger is allowed to fire itself (directly or through a chain) is controlled by PRAGMA recursive_triggers. The behavior differs across SQLite builds, so the reliable move is to set it explicitly.

A soft-delete cascade shows the difference. A trigger marks a node deleted, and its body marks that node's children deleted — which should re-fire the trigger for each child, propagating all the way down the tree:

PRAGMA recursive_triggers = ON;

CREATE TABLE tree (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  label TEXT NOT NULL,
  deleted INTEGER NOT NULL DEFAULT 0
);

CREATE TRIGGER cascade_soft_delete
AFTER UPDATE OF deleted ON tree
FOR EACH ROW
WHEN NEW.deleted = 1
BEGIN
  UPDATE tree SET deleted = 1
  WHERE parent_id = NEW.id AND deleted = 0;
END;

INSERT INTO tree (id, parent_id, label) VALUES
  (1, NULL, 'root'),
  (2, 1,    'child-A'),
  (3, 1,    'child-B'),
  (4, 2,    'grandchild');

-- Soft-delete only the root; the cascade should reach every descendant
UPDATE tree SET deleted = 1 WHERE id = 1;

SELECT id, label, deleted FROM tree ORDER BY id;

Run it and read all four rows. With recursive_triggers = ON, the trigger fires on the children, and each child's own update fires it again for that child's children — so the flag travels the whole branch rather than stopping one step below the root. Recursion is powerful but easy to run away with, so SQLite caps the depth; design cascades that terminate, as this one does when it reaches leaf nodes with no children.

The pragma on the first line is doing real work, and the next two blocks are about what happens without it.

Predict

The cascade above works because its first line switches recursive triggers on. Suppose you write the same trigger in a fresh database and simply forget that line. What does PRAGMA recursive_triggers report by default in this environment, and what does the cascade do?

PRAGMA recursive_triggers;

It reports 1, and the cascade reaches every descendant — the pragma line in the example above is defensive rather than necessary.
It reports 0, and the cascade flags the root's direct children but stops there, leaving the grandchild undeleted with no error.
It reports 0, and any trigger whose body modifies its own table raises an error until the pragma is switched on.

A cascade that flags nothing

Debug

This soft-delete cascade has the pragma set and a five-node tree beneath the root, and it is meant to flag the root and every descendant. It runs, exits cleanly, and flags the root alone — all four nodes below it stay at 0, with no error. Commit a hypothesis about what the WHEN clause is testing at the moment the trigger is consulted, then fix it so the whole tree ends up flagged.

PRAGMA recursive_triggers = ON;

CREATE TABLE tree (
id INTEGER PRIMARY KEY,
parent_id INTEGER,
label TEXT NOT NULL,
deleted INTEGER NOT NULL DEFAULT 0
);

CREATE TRIGGER cascade_soft_delete
AFTER UPDATE OF deleted ON tree
FOR EACH ROW
WHEN OLD.deleted = 1
BEGIN
UPDATE tree SET deleted = 1
WHERE parent_id = NEW.id AND deleted = 0;
END;

INSERT INTO tree (id, parent_id, label) VALUES
(1, NULL, 'root'),
(2, 1,    'child-A'),
(3, 1,    'child-B'),
(4, 2,    'grandchild'),
(5, 4,    'great-grandchild');

UPDATE tree SET deleted = 1 WHERE id = 1;

SELECT id, label, deleted FROM tree ORDER BY id;

Expected output:

5 row(s) affected
1 row(s) affected
id | label | deleted
--------------------
1 | root | 1
2 | child-A | 1
3 | child-B | 1
4 | grandchild | 1
5 | great-grandchild | 1

Before you change anything — why do you think this fails?

Querying What a Trigger Recorded

A trigger's output is an ordinary table, so reporting on it is an ordinary query — and the clause order is pinned by the same grammar as anywhere else. The frame below is the WHEN-filtered restock trigger from earlier, driven with four updates: Widget A is restocked twice, Widget B is drawn down rather than restocked, and Gadget X is restocked once.

CREATE TABLE inventory (id INTEGER PRIMARY KEY, item TEXT NOT NULL, quantity INTEGER NOT NULL);
CREATE TABLE restock_log (id INTEGER PRIMARY KEY AUTOINCREMENT, item_id INTEGER NOT NULL, added_quantity INTEGER NOT NULL);
CREATE TRIGGER log_restock AFTER UPDATE OF quantity ON inventory FOR EACH ROW WHEN NEW.quantity > OLD.quantity
BEGIN
  INSERT INTO restock_log (item_id, added_quantity) VALUES (NEW.id, NEW.quantity - OLD.quantity);
END;
INSERT INTO inventory VALUES (1,'Widget A',50),(2,'Widget B',20),(3,'Gadget X',5);
UPDATE inventory SET quantity = 75 WHERE id = 1;
UPDATE inventory SET quantity = 90 WHERE id = 1;
UPDATE inventory SET quantity = 10 WHERE id = 2;
UPDATE inventory SET quantity = 30 WHERE id = 3;

Every arrangement below runs against those tables, already populated by the trigger.

Arrange the code

These five lines count how many substantial restocks each item received, keeping only log entries above ten units. They are shuffled. Put them in the order that runs — then answer what the order is really testing: if you wanted to keep only the items restocked more than once, why would that filter have to be a HAVING below these lines rather than another condition in the WHERE?

SELECT i.item, COUNT(*) AS restocks
GROUP BY i.id, i.item;
WHERE r.added_quantity > 10
JOIN restock_log r ON r.item_id = i.id
FROM inventory i

Try It Yourself

Combine what this lesson added. The starter below has a posts view over a posts_raw table, a moderation_log, and no trigger at all — so nothing stands between an insert and the base table, and nothing writes to the log.

Build

The starter has a posts view over a posts_raw table plus a moderation_log, and no trigger at all — so all four inserts go straight to the base table. It runs and exits cleanly, but Bob's empty post is stored and the moderation log is empty. Work TODO 1 through TODO 4 so the three author inserts go through the view instead and your trigger handles them: real posts land in posts_raw and get a log entry, while the empty one is silently dropped. After your change the first query must list seed, alice and carol, and the log must list alice and carol.

CREATE TABLE posts_raw (
id INTEGER PRIMARY KEY AUTOINCREMENT,
author TEXT NOT NULL,
body TEXT NOT NULL
);

CREATE TABLE moderation_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
author TEXT NOT NULL
);

CREATE VIEW posts AS
SELECT id, author, body FROM posts_raw;

-- TODO 1: create an INSTEAD OF INSERT trigger on the posts view, so that an
--         insert aimed at the view is turned into writes on the base tables.
-- TODO 2: as the FIRST statement of its body, drop the row when NEW.body is
--         empty, using RAISE(IGNORE) so nothing is reported to the caller.
--         Bob must leave no trace anywhere.
-- TODO 3: below the guard, write the row into posts_raw and record the author
--         in moderation_log. Alice and Carol must appear in both.
-- TODO 4: redirect the three author inserts below from posts_raw to the posts
--         view, so your trigger is what handles them. The seed insert stays
--         on the base table.
INSERT INTO posts_raw (author, body) VALUES ('seed', 'existing post');

INSERT INTO posts_raw (author, body) VALUES ('alice', 'First post');
INSERT INTO posts_raw (author, body) VALUES ('bob', '');
INSERT INTO posts_raw (author, body) VALUES ('carol', 'Hello world');

SELECT author, body FROM posts ORDER BY id;
SELECT author FROM moderation_log ORDER BY id;

Expected output:

1 row(s) affected
0 row(s) affected
0 row(s) affected
0 row(s) affected
author | body
-------------
seed | existing post
alice | First post
carol | Hello world
author
------
alice
carol

Before you run — what's your plan for the first TODO?

Once it passes, try two variations and predict each before running. First, move the RAISE(IGNORE) line below the two INSERTs in the trigger body — Bob's empty post is now stored and logged, and nothing reports an error, because the guard now protects nothing. Second, put the guard back and change RAISE(IGNORE) to RAISE(ABORT, 'empty body') — the run now exits 1 with Error: empty body, Carol's later insert still succeeds, and the choice between the two resolutions turns out to be the choice between a caller who is told and a caller who is not.

Transfer

You have made a view writable with an INSTEAD OF trigger that fans one insert out across two tables. A colleague wants the same effect in PostgreSQL, where simple views are already automatically updatable but this one is not, because it writes to more than one table. What transfers from what you built, and what does not?

The problem and its shape transfer: a view over more than one table has no write path the engine can infer, so you supply one explicitly. The mechanism does not: PostgreSQL uses a rule or an INSTEAD OF trigger written in a procedural language, not a body of plain SQL statements.
Nothing transfers, because PostgreSQL views are updatable by default and the problem does not arise there.
Everything transfers except the keyword: rename INSTEAD OF to INSTEAD and the same trigger body works unchanged in PostgreSQL.

Key Takeaways

"Events" in SQLite means data events (INSERT/UPDATE/DELETE); SQLite has no scheduled-event system like MySQL's CREATE EVENT
A WHEN clause skips a trigger's body when its condition is false, evaluated per affected row
RAISE has four resolutions: ABORT (undo the statement), FAIL (stop but keep already-changed rows), ROLLBACK (undo the whole transaction), and IGNORE (silently drop the current row)
AFTER UPDATE OF a, b fires only when an update touches a column in the list, sparing the trigger from unrelated writes
INSTEAD OF triggers exist only for views and make them writable by translating writes into changes on the base tables
RAISE(IGNORE) abandons the rest of the trigger body it appears in and nothing else, so the guard has to be the body's first statement and a second trigger on the same event still fires
PRAGMA recursive_triggers controls whether a trigger can fire itself; it defaults to 0 here, so a forgotten pragma makes a cascade apply exactly one level and report nothing

Pro Tip: INSTEAD OF triggers are the cleanest way to present a stable, writable interface over a schema that is changing underneath. Application code inserts into one tidy view while your triggers absorb the real table layout — and when you refactor those tables later, you update the trigger, not every call site. The same invisibility that makes triggers convenient makes them easy to forget, so keep them documented and keep their bodies small enough to reason about at a glance.

Recall

No notes. In 11-triggers-and-automation you used BEFORE and AFTER triggers on tables. This lesson adds INSTEAD OF on views. All three run in connection with a write — what distinguishes INSTEAD OF from the other two?

BEFORE and AFTER both run alongside a write that happens anyway; INSTEAD OF replaces the write entirely, so if its body writes nothing then nothing is written.
INSTEAD OF runs between BEFORE and AFTER, so all three can be attached to the same event on the same object.
INSTEAD OF triggers run once per statement while BEFORE and AFTER run once per row, which is why INSTEAD OF can fan one insert out to several tables.

Course Complete!

Congratulations — this is the last lesson of the SQL track, the end of the optional extension tail. The core path finished at the capstone; everything in these last four lessons was an extension you were free to skip. You now have the full set: the core arc from your first SELECT through query optimization, a warehouse you designed and built yourself, and the specialized techniques covered here.

What to do next:

Build a project with a real database — a REST API backed by SQLite or PostgreSQL is a great start
Explore the SQL Playground to experiment further
Check out the SQLite Documentation for the dialect these lessons run on, or PostgreSQL Documentation for a server-side engine