SQLite CREATE TRIGGER: BEFORE/AFTER, WHEN Clause, OLD and NEW Examples

A column holds one value and the table remembers nothing about the value before it, so the change that mattered is the one you cannot see. A trigger moves that record-keeping into the database file, where every writer passes through it: SQLite runs the statements you register once for each row an INSERT, UPDATE, or DELETE touches. The examples below run in the sqlite3 shell against two tables:

CREATE TABLE students (
    student_id   INTEGER PRIMARY KEY,
    student_name TEXT NOT NULL,
    grade        TEXT NOT NULL
);

CREATE TABLE student_audit (
    audit_id     INTEGER PRIMARY KEY,
    student_id   INTEGER NOT NULL,
    old_grade    TEXT,
    new_grade    TEXT,
    changed_at   TEXT NOT NULL
);

What SQLite triggers do

A trigger is stored SQL attached to one event on one table or view: INSERT, UPDATE, or DELETE. The CREATE TRIGGER documentation is explicit that SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers, so an UPDATE that matches four hundred rows runs the body four hundred times, and writing FOR EACH ROW in the definition is optional.

Timing decides what a trigger is good for. BEFORE runs ahead of the row change and is the default when neither keyword is present, AFTER runs once the row is written, and INSTEAD OF replaces the operation altogether. BEFORE and AFTER triggers work only on ordinary tables, and INSTEAD OF triggers work only on views. Validation therefore belongs in a BEFORE trigger, logging in an AFTER trigger.

Inside the body, the row that fired the trigger is available as OLD.column_name and NEW.column_name, which is what makes a before-and-after log possible from one definition. Triggers are automatically dropped when the table they are associated with is dropped, so a DROP TABLE students takes the audit trigger with it while student_audit keeps its rows.

If the schema is not there yet, start with SQLite CREATE DATABASE and SQLite CREATE TABLE.

SQLite CREATE TRIGGER syntax

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | UPDATE OF column | DELETE }
ON table_name
FOR EACH ROW
WHEN condition
BEGIN
    statements;
END;

The body between BEGIN and END is one or more statements, each ended by its own semicolon, and the whole definition ends at the semicolon after END. Written against the two tables above, an audit trigger looks like this:

CREATE TRIGGER log_student_grade_change
AFTER UPDATE OF grade ON students
FOR EACH ROW
WHEN OLD.grade IS NOT NEW.grade
BEGIN
    INSERT INTO student_audit(student_id, old_grade, new_grade, changed_at)
    VALUES (OLD.student_id, OLD.grade, NEW.grade, datetime('now'));
END;

Two clauses narrow when it fires. UPDATE OF grade means the trigger fires only if grade appears on the left-hand side of one of the terms in the SET clause of the UPDATE statement, so an update that only changes student_name never reaches the body. The optional WHEN clause is checked per row, and the statements run only if it is true. datetime('now') writes Coordinated Universal Time, which is what the date and time functions use for the string now; add the 'localtime' modifier if the audit trail has to read in the local zone.

BEFORE, AFTER, and INSTEAD OF compared

A BEFORE trigger sees the statement while the row can still be refused, which is where RAISE belongs. An AFTER trigger sees a row that is already written, so an audit entry it inserts is only ever written next to a change that actually happened. Since BEFORE is what a definition means when it names no timing at all, write the keyword even where it is the one you want, so the next reader does not have to know the default to know what the trigger does.

INSTEAD OF is the odd one, because it exists for a restriction rather than for timing: you cannot DELETE, INSERT, or UPDATE a view, since views are read-only in SQLite. An INSTEAD OF trigger on the view catches the write and turns it into statements against the base tables, which is how a view becomes writable. SQLite views covers the view side of that arrangement.

OLD and NEW references

Which of the two names carries values depends on the event, because an insert has no previous row and a delete has no new one:

An INSERT trigger therefore reads NEW.column_name, a DELETE trigger reads OLD.column_name, and an UPDATE trigger compares the two.

Compare them with IS NOT rather than <> when the column may be NULL. The IS and IS NOT operators work like = and != except when one or both of the operands are NULL, and it is not possible for an IS or IS NOT expression to evaluate to NULL, which the expression documentation states outright. Written as OLD.grade <> NEW.grade, a grade going from NULL to a letter produces NULL, the WHEN clause is not true, and the one change you most want in the log is the one that never gets there.

The WHEN clause and the writes it skips

A trigger fires for every row the statement matches, not for every row it changes, and those two sets are different as soon as an application saves a form without editing anything. The WHEN clause is where that difference gets handled. With the trigger above in place, an update that stores the grade a student already has matches the row, checks OLD.grade IS NOT NEW.grade, finds it false, and writes nothing:

UPDATE students
SET grade = 'B'
WHERE student_name = 'Alice';

Without the clause, the same statement appends an audit row saying that B became B. Multiply that by an hourly import over a table of ten thousand students and the audit table stops being readable, which costs more than the writes do. The condition can test anything the row exposes: a status column, a NULL becoming a value, a total crossing a threshold.

Create a trigger in sqlite3

Open the database file, paste the two CREATE TABLE statements and the CREATE TRIGGER from above, then exercise it:

sqlite3 school.db

INSERT INTO students(student_name, grade)
VALUES ('Alice', 'B');

UPDATE students
SET grade = 'A'
WHERE student_name = 'Alice';

SELECT audit_id, student_id, old_grade, new_grade
FROM student_audit;

The INSERT fired nothing, because the trigger is registered on UPDATE OF grade. The UPDATE fired it once, and changed_at, left out of the SELECT above, holds the UTC timestamp datetime('now') produced. Both student_id values are 1 because INTEGER PRIMARY KEY is an alias for the rowid, so the first row of each table gets 1 without either insert naming a key.

The shell reads a definition like this one as a single statement and waits for the semicolon that follows END, which is why the whole block can be pasted in one go. To see what is stored afterwards, read the schema table, whose type column holds table, index, view, or trigger:

SELECT name, tbl_name FROM sqlite_schema WHERE type = 'trigger';

The sql column of the same table returns the definition as it was written, which is the copy a deployment script should be compared against.

Practical row-level trigger patterns

Reject a row with RAISE

CREATE TRIGGER validate_grade
BEFORE INSERT ON students
FOR EACH ROW
WHEN NEW.grade NOT IN ('A', 'B', 'C', 'D', 'F')
BEGIN
    SELECT RAISE(ABORT, 'grade must be A, B, C, D, or F');
END;

RAISE comes in four forms. ROLLBACK, ABORT, and FAIL each perform the matching ON CONFLICT processing and terminate the current query, returning the error code SQLITE_CONSTRAINT to the application along with the message you wrote. RAISE(IGNORE) instead abandons the rest of the trigger program and the statement that fired it, without an error. A CHECK constraint covers a rule this simple more cheaply, and a trigger covers the rules a constraint cannot express, such as one that reads a second table. SQLite constraints has the constraint side.

Keep a summary table in sync

CREATE TABLE order_items (
    item_id          INTEGER PRIMARY KEY,
    order_id         INTEGER NOT NULL,
    line_total_cents INTEGER NOT NULL
);

CREATE TABLE order_totals (
    order_id     INTEGER PRIMARY KEY,
    total_cents  INTEGER NOT NULL DEFAULT 0
);

CREATE TRIGGER update_order_total_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE order_totals
    SET total_cents = total_cents + NEW.line_total_cents
    WHERE order_id = NEW.order_id;
END;

Give the order a totals row, then add a line item to it:

INSERT INTO order_totals(order_id) VALUES (1);

INSERT INTO order_items(order_id, line_total_cents) VALUES (1, 2500);

SELECT order_id, total_cents FROM order_totals;

The totals row started at the column default of 0, and the trigger added the line total to it inside the same statement, so a report can read order_totals without summing anything. The body updates one row through the primary key, which is the shape to aim for: a bulk insert of a thousand items runs it a thousand times, and a body that scans a table would turn one import into a thousand scans. SQLite indexes and SQLite EXPLAIN PLAN are where that goes next.

Statements a trigger body will not take

The SQL inside a trigger is a restricted subset. The table named in an INSERT, UPDATE, or DELETE must be an unqualified table name, so main.students is out. INSERT INTO table DEFAULT VALUES is not supported. ORDER BY and LIMIT on UPDATE and DELETE are not supported, and neither are INDEXED BY and NOT INDEXED. Common table expressions are not supported directly, though a sub-select inside the statement may use one. One more setting decides whether a trigger that writes a table can fire the triggers on that table: PRAGMA recursive_triggers controls it, and its default value is off.

Create a trigger in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Trigger DDL is SQL, so DbSchema runs it the way the shell does. Connect to the SQLite file through the SQLite JDBC driver, then open the SQL Editor from the Editors menu and paste the definition. Use Run Script rather than Execute Query: Execute Query runs the statement at the cursor, delimited by ;, and the semicolons inside a BEGIN ... END body would cut the definition in half, while Run Script executes the entire editor content.

Reverse engineering brings the triggers back. DbSchema uses the JDBC driver to retrieve table, column, and foreign key information, and custom queries for triggers, procedures, and functions, which the JDBC API does not expose, so the trigger source arrives in the model along with the tables. What each action changes is worth keeping straight: statements you run in the SQL Editor go to the SQLite database, while the diagram, the saved editors, and the reverse-engineered trigger source live in the .dbs model file.

The diagram earns its place once there are several audit tables. No foreign key links student_audit to students, so nothing in the schema says the two belong together until you put them on one diagram and note which trigger fills which.

Start with the event and the timing, then let WHEN decide which rows deserve a write. Download DbSchema at https://dbschema.com/download.html, open the school.db file in it, and send the audit trigger to the database with Run Script: connecting, reverse engineering, the interactive diagrams, and the SQL editor are in the free Community Edition, and saving the model to a .dbs file is in Pro.

FAQ

Does SQLite support INSTEAD OF triggers?

SQLite supports them on views, as the comparison of the three timings above describes. Their firings are not counted by sqlite3_changes() or sqlite3_total_changes(), so an INSERT through a view leaves the change count of the statement at zero while the row lands in the base table.

How do I disable a trigger in SQLite?

SQLite has no command that disables a trigger while keeping it. Drop it with DROP TRIGGER trigger_name; and create it again when you need it. Do not drop one on a production database before the definition is in version control next to the tables, because nothing else keeps a copy of it.

Sources

1. SQLite documentation: CREATE TRIGGER
2. SQLite documentation: CREATE VIEW
3. SQLite documentation: Expressions
4. SQLite documentation: Date and time functions
5. SQLite documentation: PRAGMA statements