Polyglot
› Languages › SQLite › views triggers
SQLite § views-triggers

Views and triggers

A view is a named query — a stored SELECT statement that can be referenced as if it were a table. A trigger is a procedural fragment that runs automatically in response to an INSERT, UPDATE, or DELETE on a table. The two mechanisms together permit a measure of declarative encapsulation in SQL: a view presents derived data without the consumer knowing the source schema, and a trigger maintains derived data, audit logs, and cross-table invariants without the application’s involvement.

This page covers CREATE VIEW, the INSTEAD OF triggers that make views updatable, and the full surface of CREATE TRIGGER including the WHEN clause, OLD. and NEW. references, and the conventional uses.

Views

A view is declared with CREATE VIEW:

CREATE VIEW active_customer AS
SELECT id, name, email, phone
FROM customer
WHERE deleted_at IS NULL;

The view’s definition — the underlying SELECT — is stored in sqlite_master and re-evaluated each time the view is referenced. A view is not a materialised cache of the query result: querying the view executes the underlying SELECT against the current state of the source tables.

The view is queried like a table:

SELECT name FROM active_customer WHERE email LIKE '%@example.com';

The view’s columns inherit names from the SELECT’s projection; the column names can be specified explicitly in the view declaration:

CREATE VIEW customer_summary (id, name, lifetime_value) AS
SELECT c.id, c.name, COALESCE(SUM(o.amount), 0)
FROM customer c
LEFT JOIN bouquet_order o ON o.customer_id = c.id
GROUP BY c.id;

CREATE TEMP VIEW creates a view in the temporary database, scoped to the connection. CREATE VIEW IF NOT EXISTS suppresses the error if the view already exists.

DROP VIEW removes a view; DROP VIEW IF EXISTS is the safe form. Dropping a view does not affect the underlying tables.

DROP VIEW IF EXISTS active_customer;

When to use a view

The conventional uses of views:

  • Encapsulation: presenting a simplified or restricted projection of a table to consumers that should not depend on the underlying schema.
  • Composition: building a complex query in stages, where each stage is a view and the final query references the views by name.
  • Backward compatibility: when a column is split or renamed in the underlying schema, a view can preserve the old surface for legacy consumers.
  • Permission boundary (in engines that support row-level security; SQLite does not, but the pattern transfers): exposing only the rows or columns that a particular role should see.

A view is not a materialised cache. SQLite has no MATERIALIZED VIEW statement; a materialised view is constructed manually with a table, an INSERT from a SELECT, and triggers (or scheduled refreshes) to keep it current. The recursive-CTE form WITH … AS MATERIALIZED (see CTEs and recursion) materialises within a single query but does not persist across queries.

Updatable views

A view is non-updatable by default: an INSERT, UPDATE, or DELETE against a view fails. The underlying tables must be modified directly.

INSERT INTO active_customer (name) VALUES ('Ada');
-- Error: cannot modify active_customer because it is a view

A view can be made updatable through INSTEAD OF triggers, which intercept the data-modification statement and translate it into operations on the underlying tables. The pattern is described under Triggers.

Triggers

A trigger is a fragment of SQL that runs automatically in response to an INSERT, UPDATE, or DELETE on a table. The basic form:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
[ WHEN condition ]
BEGIN
    sql_statement;
    sql_statement;
    …
END;

The components:

  • Timing: BEFORE runs before the modification is applied; AFTER runs after; INSTEAD OF replaces the modification (and is permitted only on views).
  • Event: INSERT, UPDATE (optionally UPDATE OF col1, col2), or DELETE.
  • FOR EACH ROW: the only granularity SQLite supports. The trigger fires once per affected row, not once per statement. (ISO SQL has FOR EACH STATEMENT; SQLite does not.)
  • WHEN: an optional predicate. If present, the trigger fires only when the predicate is true for the affected row.
  • Body: a sequence of SQL statements. The body may reference OLD.col (the value before the modification, available in UPDATE and DELETE triggers) and NEW.col (the value after the modification, available in INSERT and UPDATE triggers).

A worked example

A trigger that maintains an updated_at timestamp on every row update:

CREATE TABLE customer (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT,
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;

CREATE TRIGGER trg_customer_updated_at
AFTER UPDATE ON customer
FOR EACH ROW
WHEN OLD.updated_at = NEW.updated_at      -- prevent recursion
BEGIN
    UPDATE customer
    SET updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
    WHERE id = NEW.id;
END;

The WHEN clause prevents the trigger from running when the UPDATE statement itself sets updated_at — without it, the trigger’s own UPDATE would re-fire the trigger, producing infinite recursion (which SQLite catches with recursive_triggers off, but at the cost of an error rather than a clean no-op).

Audit logging

Triggers are the conventional mechanism for table-level audit logging:

CREATE TABLE customer_audit (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    action      TEXT NOT NULL CHECK (action IN ('insert','update','delete')),
    old_data    TEXT,
    new_data    TEXT,
    occurred_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;

CREATE TRIGGER trg_customer_audit_insert
AFTER INSERT ON customer
FOR EACH ROW
BEGIN
    INSERT INTO customer_audit (customer_id, action, new_data)
    VALUES (NEW.id, 'insert', json_object('name', NEW.name, 'email', NEW.email));
END;

CREATE TRIGGER trg_customer_audit_update
AFTER UPDATE ON customer
FOR EACH ROW
BEGIN
    INSERT INTO customer_audit (customer_id, action, old_data, new_data)
    VALUES (
        NEW.id, 'update',
        json_object('name', OLD.name, 'email', OLD.email),
        json_object('name', NEW.name, 'email', NEW.email)
    );
END;

CREATE TRIGGER trg_customer_audit_delete
AFTER DELETE ON customer
FOR EACH ROW
BEGIN
    INSERT INTO customer_audit (customer_id, action, old_data)
    VALUES (OLD.id, 'delete', json_object('name', OLD.name, 'email', OLD.email));
END;

The triggers serialise the row’s data as JSON and store it in the audit table; the audit table can later be queried for any historical view of a row’s value. The pattern is widely used in compliance-heavy domains.

Cross-table invariants

Triggers can enforce invariants that span tables, beyond the reach of CHECK constraints:

CREATE TABLE invoice_item (
    id         INTEGER PRIMARY KEY,
    invoice_id INTEGER NOT NULL REFERENCES invoice(id),
    amount     REAL NOT NULL CHECK (amount >= 0)
);

CREATE TRIGGER trg_invoice_total_on_insert
AFTER INSERT ON invoice_item
FOR EACH ROW
BEGIN
    UPDATE invoice
    SET total = total + NEW.amount
    WHERE id = NEW.invoice_id;
END;

The trigger keeps an invoice.total column current as invoice_item rows are added. The pattern requires symmetric triggers for UPDATE (to handle amount changes) and DELETE (to subtract the removed amount); the four-trigger set is verbose enough that the conventional alternative is to compute SUM(amount) from invoice_item directly when needed, sacrificing a small amount of read performance for substantially simpler invariants.

Updatable views via INSTEAD OF

A view’s INSTEAD OF triggers translate data-modification statements into operations on the underlying tables.

CREATE VIEW active_customer AS
SELECT id, name, email
FROM customer
WHERE deleted_at IS NULL;

CREATE TRIGGER trg_active_customer_insert
INSTEAD OF INSERT ON active_customer
FOR EACH ROW
BEGIN
    INSERT INTO customer (name, email) VALUES (NEW.name, NEW.email);
END;

CREATE TRIGGER trg_active_customer_delete
INSTEAD OF DELETE ON active_customer
FOR EACH ROW
BEGIN
    UPDATE customer SET deleted_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now') WHERE id = OLD.id;
END;

The first trigger redirects an INSERT INTO active_customer to an INSERT INTO customer; the second redirects a DELETE FROM active_customer to a soft-delete on the underlying table. The view’s consumer sees a normal-looking table with INSERT and DELETE permissions; the view’s definition implements the soft-delete semantics behind the scenes.

UPDATE OF

An UPDATE trigger may be qualified with a list of columns; the trigger then fires only when one of those columns is updated.

CREATE TRIGGER trg_email_changed
AFTER UPDATE OF email ON customer
FOR EACH ROW
BEGIN
    INSERT INTO email_change_log (customer_id, old_email, new_email)
    VALUES (NEW.id, OLD.email, NEW.email);
END;

The UPDATE OF qualifier is a useful narrowing when only certain column changes are interesting; without it, the trigger fires on every update and the body must check OLD.col != NEW.col to filter.

Recursion

Triggers may fire other triggers. The PRAGMA recursive_triggers setting controls whether the recursion is permitted:

PRAGMA recursive_triggers = ON;     -- default in 3.7+

Without recursive triggers, a trigger that modifies its own table does not re-fire the same trigger; with them, the recursion proceeds until the modifications terminate (or until the engine’s recursion limit is hit). The recommendation is to leave the default in place and to design triggers to terminate quickly; the WHEN clause is the conventional mechanism for preventing accidental recursion (as in the updated_at example above).

When to use a trigger

Triggers are a powerful tool that can be over-used. The recommendations:

  • Yes: maintaining audit logs, denormalised counters, and timestamps that the application should not need to update.
  • Yes: enforcing cross-table invariants beyond the reach of CHECK constraints.
  • Yes: implementing soft-delete and INSTEAD OF updatable views.
  • No: business logic that the application should be responsible for; triggers are invisible from the application’s source code and complicate debugging.
  • No: data validation that a CHECK constraint can express; the constraint is more visible in the schema and easier to reason about.
  • No: complex multi-step transformations; if a trigger body is more than a few statements, the logic likely belongs in the application’s data-access layer.

The trigger surface is a sharp tool; used judiciously it eliminates a category of bug (forgetting to update the audit log, for instance), but used liberally it produces schemas in which the database does things the application’s code does not describe, which is a fertile source of confusion.

Inspecting views and triggers

Views and triggers are stored in sqlite_master and can be enumerated through the type column:

SELECT name, sql FROM sqlite_master WHERE type = 'view';
SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'trigger';

The sqlite3 shell .dot commands .schema and .indexes include views and triggers in their output:

.schema customer    # includes the table, its indexes, and any triggers

The recommendation for any non-trivial schema is to keep view and trigger definitions in version-controlled migration scripts, both for review and for the ability to recreate the schema deterministically.