SQLite Triggers: CREATE TRIGGER, BEFORE/AFTER, and OLD/NEW

A Trigger Runs SQL Automatically

A trigger is a stored block of SQL that fires whenever a specific event happens on a specific table. You write it once. SQLite handles the "when" for you.

The shape:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('Notebook', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'Notebook';
SELECT * FROM price_history;

We never wrote an explicit INSERT into price_history. The trigger did it. Every future price update will be logged the same way, whether it comes from the CLI, a script, or an app.

The Anatomy of CREATE TRIGGER

Read the syntax piece by piece:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- one or more statements
END;

• Timing — BEFORE runs before the change, AFTER runs after, INSTEAD OF replaces it (views only).
• Event — which operation triggers it. UPDATE OF col1, col2 narrows updates to specific columns.
• Table — the table being watched.
• FOR EACH ROW — SQLite only supports row-level triggers, so this is implicit. You can write it for clarity; it changes nothing.
• WHEN — an optional condition. The trigger body only runs if it's true.
• Body — one or more statements between BEGIN and END. Each must end with a semicolon.

That's the whole grammar. Most real triggers are five to ten lines.

OLD and NEW: The Row Being Changed

Inside the body, two pseudo-rows let you see the data:

• NEW — the incoming row. Available in INSERT and UPDATE triggers.
• OLD — the existing row. Available in UPDATE and DELETE triggers.

A DELETE trigger only has OLD. An INSERT trigger only has NEW. An UPDATE trigger has both.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

The deleted row is gone from accounts, but its data was captured in deletions before it disappeared.

BEFORE: Validate or Adjust the Row

BEFORE triggers run before the row change hits disk. They're handy for raising an error or normalising data:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- noop, just illustrating context
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'email cannot be empty');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

The second INSERT aborts before any row is written. RAISE(ABORT, '...') cancels the current statement and rolls back to the start of it — RAISE(FAIL, ...), RAISE(ROLLBACK, ...), and RAISE(IGNORE) give you finer control over what happens.

For pure data validation, prefer CHECK constraints — they're declarative and the optimizer knows about them. Reach for a BEFORE trigger when the rule needs to look at other tables or do something a CHECK can't express.

WHEN: Conditional Triggers

A WHEN clause filters which row changes actually fire the body. It's evaluated per row, after OLD and NEW are bound:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

The first order doesn't make the cut. The other two do. Without the WHEN clause, every insert would have written to big_orders and you'd be filtering on the read side instead.

INSTEAD OF: Making a View Writable

Views are read-only by default. An INSTEAD OF trigger intercepts a write against a view and runs your SQL in its place — usually translating it into writes against the underlying table(s):

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

The application talks to the view as if it were a table. The trigger handles the split into first_name and last_name behind the scenes.

Listing and Dropping Triggers

Triggers live in sqlite_master alongside tables and indexes:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- placeholder body
END;

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

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; is the safe form. Dropping the table a trigger lives on drops the trigger automatically — you don't need to clean up first.

Pitfalls Worth Knowing

A few things bite people the first time:

• Triggers fire per row, not per statement. An UPDATE that touches 1,000 rows fires the trigger 1,000 times. If the body itself is expensive, that adds up fast.
• Triggers run inside the surrounding transaction. If the outer statement rolls back, the trigger's writes roll back too. That's usually what you want, but it means a trigger isn't an escape hatch for "log this no matter what."
• Recursive triggers are off by default. A trigger that modifies the same table won't re-fire itself unless you set PRAGMA recursive_triggers = ON;. Leave it off unless you have a specific reason.
• Application-side writes can bypass them, but only by skipping the database. As long as every write goes through SQLite, the trigger will run. ORMs that batch via raw SQL still trigger them.
• Don't put business logic spread across many triggers. They're invisible from the call site — someone debugging "why did this row appear?" has to grep sqlite_master. Use them for cross-cutting concerns (audit logs, derived columns, view writability) and keep the rest in application code.

A Realistic Audit-Log Example

Pulling the patterns together — track every change to a posts table:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('Draft', 'first cut');
UPDATE posts SET title = 'Published' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

One trigger keeps updated_at honest and writes an audit row in a single place. The application code doing the UPDATE doesn't need to know either of those things exist.

Next: JSON Support

Triggers handle automation around row events. The next piece of advanced SQLite is what you can store inside a row — JSON. SQLite has a full set of JSON functions for querying and updating structured data without leaving SQL, and that's the next page.