Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 33 ยท Schema Design and Integrity

Views and Triggers

Save queries as views and react to changes with SQLite triggers: BEFORE and AFTER triggers, INSTEAD OF on views, audit logs, updated_at timestamps and recursion.

Two features let the database itself carry some of your application's logic. A view is a saved query that you can use like a table, which keeps complicated joins in one place. A trigger is a small piece of SQL that SQLite runs automatically when rows are inserted, updated or deleted, which is how you keep audit logs, timestamps and derived data in step without remembering to do it from every program.

Both are powerful and both can be overused. This lesson shows the useful patterns first (a reporting view, an audit log, an updated_at column, an updatable view) and then the traps: triggers you cannot see from application code, and recursion.

Views and triggers

View A named SELECT you query like a table.
Trigger SQL that runs when data changes.

Neither stores copies of your data by itself.

1 / 2

Views: a saved query

CREATE VIEW name AS SELECT ... stores the query text, not its results. Every time you select from the view, SQLite runs the underlying query against the current data, so the view is never stale. Use views to hide joins, to give a stable name to a calculation, or to expose only some columns.

SQL A reporting view ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Once created, the view behaves like a table in SELECT, WHERE and joins. The second query filters the view's own columns. Because a view is just text, changing the data in products changes what the view returns on the next query. Drop it with DROP VIEW name, and use CREATE VIEW IF NOT EXISTS in setup scripts. SQLite offers CREATE TEMP VIEW for views that vanish when the connection closes.

SQLite views are read-only by default: an INSERT into a view fails with "cannot modify ... because it is a view". Triggers are the way to change that.

Triggers: reacting to changes

A trigger has a timing (BEFORE, AFTER or INSTEAD OF), an event (INSERT, UPDATE or DELETE), a table, and a body of one or more statements between BEGIN and END. SQLite triggers are always row-level: the body runs once for each affected row. Inside the body you refer to the row with two names: NEW (the inserted or updated values) and OLD (the values before an update or delete).

An audit log

The classic use is recording who changed what. Here every price change is copied into a history table, with the old and new price.

SQL AFTER UPDATE audit trigger

Press Run (or Ctrl + Enter) to see the result here.

Look at what was logged. The first update changes the lamp from 20 to 25. The second sets the desk price to the value it already has, so the WHEN clause stops the trigger and nothing is logged. The third updates two rows, so the trigger fires twice, once per row, logging the desk from 150 to 165 and the chair from 80 to 88. AFTER UPDATE OF price limits the trigger to updates that name the price column in SET.

A trigger for updated_at

Many tables carry an updated_at timestamp that should change on every edit. A trigger keeps that honest no matter which program writes. An AFTER UPDATE trigger that itself updates the same table would, in principle, fire again; SQLite's recursive triggers are off by default, so it does not, but the WHEN guard below makes the intent explicit and stays safe if recursion is enabled.

SQL Stamping updated_at automatically

Press Run (or Ctrl + Enter) to see the result here.

A new note has no updated_at (never_edited is 1). After the edit the trigger has filled it in (stamped is 1). We print booleans rather than the timestamp because the clock value differs on every run.

BEFORE triggers and validation

A BEFORE trigger runs while the change can still be stopped. SQLite has no way to modify NEW in a BEFORE trigger, but you can reject a row with SELECT RAISE(ABORT, 'message'). That produces custom error messages for rules a CHECK constraint cannot express, such as ones that need to look at other rows.

SQL RAISE(ABORT) rejects a change

Press Run (or Ctrl + Enter) to see the result here.

Hamlet and Macbeth each accept their seats. The last insert is Hamlet's third, so the trigger raises show is sold out and the statement is undone. The list above it shows only the three accepted rows.

INSTEAD OF triggers: updatable views

Because a view has no storage of its own, INSTEAD OF triggers tell SQLite what to do when someone writes to it. The trigger replaces the original statement, and its body can write to the real tables underneath.

SQL Writing through a view

Press Run (or Ctrl + Enter) to see the result here.

Inserting Alan Turing into the view splits the name and writes two columns of the real table. This pattern gives old code a stable "interface" while the tables behind it change, but keep the logic small: a trigger body is hard to debug.

Recursive triggers

By default a trigger's own writes do not fire the same trigger again. PRAGMA recursive_triggers = ON changes that, and it also makes REPLACE conflict resolution fire delete triggers. Recursion is bounded by an internal depth limit of 1000 (a runaway trigger stops with an error), but you should still make sure that every recursive step has a stopping condition.

SQL A recursive trigger counting down

Press Run (or Ctrl + Enter) to see the result here.

One insert of 4 produces five rows: 4, 3, 2, 1, 0. The WHEN NEW.n > 0 condition is the stopping rule. Without recursive_triggers = ON you would get only the single row 4.

Managing and inspecting them

  • DROP TRIGGER name and DROP VIEW name remove them. Dropping a table drops its triggers.
  • Both are stored in sqlite_master (see Altering schema safely), so SELECT name, sql FROM sqlite_master WHERE type IN ('view', 'trigger') lists them.
  • Triggers are invisible from application code, so keep a list of them in your schema docs. A surprise trigger is a common source of "why did this row change?" bugs.
  • Prefer a plain CHECK, DEFAULT or foreign key when one can do the job, and use triggers for what those cannot express.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does CREATE VIEW v AS SELECT ... store?

    A view stores only the query. Selecting from it runs the query on the current data, so it is never out of date.
  2. 2. How often does an SQLite trigger body run for UPDATE t SET x = 1 that changes 5 rows?

    SQLite triggers are row-level only. FOR EACH ROW is implied and there is no statement-level trigger.
  3. 3. Which trigger type lets you make a view writable?

    INSTEAD OF triggers replace the write on a view with statements of your choice, usually against the underlying tables.
  4. 4. How do you reject a row with a custom message from a trigger?

    RAISE(ABORT, msg) stops the statement with your message. It is how triggers enforce rules that a CHECK constraint cannot.
  5. 5. What does PRAGMA recursive_triggers = ON change?

    By default a trigger's changes do not re-fire that trigger. Turning the pragma on allows recursion, so each step needs a stopping condition.

Summary

  • A view is a stored SELECT that always shows current data; views are read-only unless you add INSTEAD OF triggers.
  • Triggers run SQL automatically per affected row, BEFORE, AFTER or INSTEAD OF an INSERT, UPDATE or DELETE, using OLD and NEW for the row values.
  • WHEN and UPDATE OF column narrow when a trigger fires; RAISE(ABORT, msg) rejects a change with your own error.
  • Audit logs and updated_at stamps are the classic good uses.
  • Recursive triggers are off by default; when enabled, give them a stopping condition.
  • Keep triggers few, small and documented, because application code cannot see them.

Next: Altering schema safely, changing tables that already hold data.