Triggers and Events

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 lesson 11 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.

Choosing How a Rule Fails: RAISE Resolutions

Lesson 11 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.

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 * FROM deposits;

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.

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

By default a view (covered in Views and CTEs) is read-only — you cannot INSERT or UPDATE through it, because the database can't guess how to spread a change across the underlying tables. An INSTEAD OF trigger fills that gap: 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;

With recursive_triggers = ON, deleting the root cascaded to child-A, child-B, and grandchild — the trigger fired on the children, whose own update fired it again on the grandchild. With the pragma OFF, the trigger would not re-fire itself, so only the direct children (child-A, child-B) would be flagged and grandchild would survive with deleted = 0. Recursion is powerful but easy to run away with, so SQLite caps the depth (PRAGMA recursive_triggers aside, a hard recursion limit prevents infinite loops); design cascades that terminate, as this one does when it reaches leaf nodes with no children.

Try It Yourself

Combine what this lesson added. Build a moderation system for a posts view backed by a posts_raw table plus a moderation_log. Write an INSTEAD OF INSERT trigger on the view that (a) skips posts whose body is empty using RAISE(IGNORE), and (b) writes accepted posts into posts_raw and a log entry into moderation_log.

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,
  logged_at TEXT DEFAULT (datetime('now'))
);

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

-- Write your trigger here, then switch the inserts below to target the view:
-- 1. INSTEAD OF INSERT on `posts`
-- 2. RAISE(IGNORE) when NEW.body is empty ('')
-- 3. Otherwise INSERT into posts_raw AND moderation_log
--
-- Once your trigger exists, insert through the VIEW instead of posts_raw:
--   INSERT INTO posts (author, body) VALUES ('alice', 'First post');
--   INSERT INTO posts (author, body) VALUES ('bob', '');       -- should be ignored
--   INSERT INTO posts (author, body) VALUES ('carol', 'Hello world');

-- Seed data (writes straight to the base table so this scaffold runs as-is):
INSERT INTO posts_raw (author, body) VALUES ('seed', 'existing post');

SELECT author, body FROM posts;
SELECT author FROM moderation_log;

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
• PRAGMA recursive_triggers controls whether a trigger can fire itself; set it explicitly and design cascades that terminate

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.

Next Steps

You now know how to automate database logic with triggers. The final lesson covers query optimization — how to read execution plans, use indexes effectively, and restructure queries so your database does less work to return the same results.