SQLite Triggers: CREATE TRIGGER With Tested Examples

Ninad Pathak

A trigger runs SQL inside the database whenever a row changes, so something like an audit log happens on every matching change, wherever the change came from. SQLite triggers have their own rules, like recursion being off by default. In this article I’ll walk you through creating them with tested examples.

TLDR

  • CREATE TRIGGER name AFTER UPDATE ON table BEGIN … END; runs the statements between BEGIN and END for each changed row.
  • Use NEW.column for the new values (INSERT, UPDATE) and OLD.column for the old ones (UPDATE, DELETE).
  • Add WHEN condition to fire only for some rows, and UPDATE OF column to fire only when that column is updated.
  • SELECT RAISE(ABORT, 'message') inside a trigger rejects the change with your error message.
  • List triggers with SELECT name FROM sqlite_schema WHERE type = 'trigger';. Remove one with DROP TRIGGER.
Table of contents

What is a trigger in SQLite?

A trigger is a set of SQL statements that SQLite runs automatically when rows in a given table change. “At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers” (SQLite CREATE TRIGGER), so the body runs once per affected row.

SQLite has no stored procedures, so triggers are the only way to keep logic inside the database file:

sqlite> CREATE PROCEDURE restock() BEGIN UPDATE products SET stock = 100; END;
Error: in prepare, near "PROCEDURE": syntax error
  CREATE PROCEDURE restock() BEGIN UPDATE products SET stock = 100; END;
         ^--- error here

SQLite CREATE TRIGGER syntax

CREATE [TEMP] TRIGGER [IF NOT EXISTS] trigger_name
  {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF column, ...] | DELETE}
  ON table_name
  [FOR EACH ROW]
  [WHEN condition]
BEGIN
  statement; ...
END;
PartWhat it does
BEFORE / AFTERRuns the body before or after the row changes. BEFORE is the default
INSTEAD OFRuns the body in place of the change. Views only
UPDATE OF columnFires only when one of the listed columns is in the UPDATE‘s SET
WHEN conditionFires only for rows where the condition is true
NEW.col / OLD.colThe row’s new values (INSERT, UPDATE) and old values (UPDATE, DELETE)

How to create a trigger in SQLite

To show you how triggers work, I will use a small shop database. Its products and orders tables come with a table that audits price changes. All the examples below run in this one database, in order:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL
);
CREATE TABLE price_audit (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  old_price REAL,
  new_price REAL,
  changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO products (name, price, stock) VALUES ('Pen', 1.50, 100), ('Notebook', 4.25, 20);

Step 1: Write an AFTER UPDATE trigger that logs changes

This trigger records every price change. UPDATE OF price skips updates that don’t touch the price, and WHEN skips updates that set the same price:

sqlite> CREATE TRIGGER log_price_change AFTER UPDATE OF price ON products WHEN OLD.price <> NEW.price BEGIN INSERT INTO price_audit (product_id, old_price, new_price) VALUES (OLD.id, OLD.price, NEW.price); END;
sqlite> UPDATE products SET price = 1.75 WHERE name = 'Pen';
sqlite> UPDATE products SET stock = 90 WHERE name = 'Pen';
sqlite> SELECT product_id, old_price, new_price FROM price_audit;
product_id  old_price  new_price
----------  ---------  ---------
1           1.5        1.75

The stock update didn’t add an audit row, because it didn’t change the price.

Step 2: Reject bad data with a BEFORE INSERT trigger

RAISE(ABORT, 'message') cancels the statement and returns your message as a constraint error:

sqlite> CREATE TRIGGER check_quantity BEFORE INSERT ON orders WHEN NEW.quantity <= 0 BEGIN SELECT RAISE(ABORT, 'quantity must be positive'); END;
sqlite> INSERT INTO orders (product_id, quantity) VALUES (1, -5);
Error: stepping, quantity must be positive (19)

For a simple rule like this one, a CHECK (quantity > 0) constraint does the same job. Triggers are for rules that need other tables.

Examples

Each example builds on the database from the steps above.

Keep stock in sync with an AFTER INSERT trigger

Each new order takes its quantity out of stock. If that leaves stock below zero, RAISE(ABORT, …) undoes the whole insert, including the stock change:

sqlite> CREATE TRIGGER take_stock AFTER INSERT ON orders BEGIN UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id; SELECT RAISE(ABORT, 'not enough stock') WHERE (SELECT stock FROM products WHERE id = NEW.product_id) < 0; END;
sqlite> INSERT INTO orders (product_id, quantity) VALUES (2, 5);
sqlite> INSERT INTO orders (product_id, quantity) VALUES (2, 50);
Error: stepping, not enough stock (19)
sqlite> SELECT id, name, stock FROM products; SELECT * FROM orders;
id  name      stock
--  --------  -----
1   Pen       90
2   Notebook  15
id  product_id  quantity
--  ----------  --------
1   2           5

The second order failed, and Notebook stock is still 15, not -35.

Set an updated_at timestamp automatically

This trigger stamps the time on every update. The WHEN NEW.updated_at IS OLD.updated_at check stops it from reacting to its own update:

sqlite> CREATE TRIGGER touch_product AFTER UPDATE ON products WHEN NEW.updated_at IS OLD.updated_at BEGIN UPDATE products SET updated_at = datetime('now') WHERE id = NEW.id; END;
sqlite> UPDATE products SET name = 'Gel pen' WHERE id = 1;
sqlite> SELECT id, name, updated_at IS NOT NULL AS has_timestamp FROM products;
id  name      has_timestamp
--  --------  -------------
1   Gel pen   1
2   Notebook  0

Undo a change with an AFTER DELETE trigger

Deleting an order puts its quantity back in stock. Only OLD exists in a delete trigger:

sqlite> CREATE TRIGGER restore_stock AFTER DELETE ON orders BEGIN UPDATE products SET stock = stock + OLD.quantity WHERE id = OLD.product_id; END;
sqlite> DELETE FROM orders WHERE id = 1; SELECT id, name, stock FROM products WHERE id = 2;
id  name      stock
--  --------  -----
2   Notebook  20

Make a view updatable with INSTEAD OF

SQLite views are read-only. An INSTEAD OF trigger tells SQLite what to do with an update on the view:

sqlite> CREATE VIEW product_prices AS SELECT id, name, price FROM products;
sqlite> UPDATE product_prices SET price = 5.00 WHERE id = 2;
Error: in prepare, cannot modify product_prices because it is a view
sqlite> CREATE TRIGGER update_product_prices INSTEAD OF UPDATE ON product_prices BEGIN UPDATE products SET price = NEW.price WHERE id = OLD.id; END;
sqlite> UPDATE product_prices SET price = 5.00 WHERE id = 2; SELECT * FROM product_prices;
id  name      price
--  --------  -----
1   Gel pen   1.75
2   Notebook  5.0

List all triggers in a database

Triggers are stored in sqlite_schema. In the sqlite3 shell, .schema products also prints the triggers on that table:

sqlite> SELECT name, tbl_name FROM sqlite_schema WHERE type = 'trigger';
name                   tbl_name
---------------------  --------------
log_price_change       products
check_quantity         orders
take_stock             orders
touch_product          products
restore_stock          orders
update_product_prices  product_prices

Create a trigger only if it doesn’t exist

sqlite> CREATE TRIGGER log_price_change AFTER UPDATE ON products BEGIN SELECT 1; END;
Error: in prepare, trigger log_price_change already exists
  CREATE TRIGGER log_price_change AFTER UPDATE ON products BEGIN SELECT 1; END;
                 ^--- error here
sqlite> CREATE TRIGGER IF NOT EXISTS log_price_change AFTER UPDATE ON products BEGIN SELECT 1; END;

Drop a trigger

DROP TRIGGER IF EXISTS removes one trigger. Dropping a table also drops its triggers:

sqlite> DROP TRIGGER IF EXISTS touch_product; SELECT count(*) AS triggers FROM sqlite_schema WHERE type = 'trigger';
triggers
--------
5

Edge cases

These rules come from SQLite’s documentation and can surprise you if you know triggers from other databases.

Recursive triggers are off by default

A trigger that inserts into its own table doesn’t fire again unless PRAGMA recursive_triggers = ON. Recursive triggers arrived in version 3.6.18 “but was initially turned OFF by default, for compatibility” (SQLite PRAGMA). The setting is per connection:

sqlite> CREATE TABLE counter (n INTEGER); CREATE TRIGGER count_down AFTER INSERT ON counter WHEN NEW.n > 0 BEGIN INSERT INTO counter VALUES (NEW.n - 1); END;
sqlite> INSERT INTO counter VALUES (3); SELECT group_concat(n) AS rows FROM counter;
rows
----
3,2
sqlite> DELETE FROM counter; PRAGMA recursive_triggers = ON; INSERT INTO counter VALUES (3); SELECT group_concat(n) AS rows FROM counter;
rows
-------
3,2,1,0

Prefer AFTER triggers when you change the same row

If a BEFORE UPDATE or BEFORE DELETE trigger changes the row being processed, “the result of the subsequent update or delete operation is undefined”. SQLite’s documentation says “programmers are encouraged to prefer AFTER triggers over BEFORE triggers”.

NEW.id isn’t set yet in BEFORE INSERT

“The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the rowid is not explicitly set to an integer.” Use an AFTER INSERT trigger when you need the new row’s ID.

Conclusion

Use AFTER triggers for side effects such as audit logs and timestamps. Use BEFORE triggers with RAISE(ABORT, …) for validation that a CHECK constraint can’t express. Use INSTEAD OF triggers to make views writable. Keep each trigger short, because it runs once for every affected row.

Further reading

Tested with SQLite 3.51.0 on 7 October 2026.

Ninad Pathak
Previous:
Read next: