SQLite Triggers provide a powerful way to automate actions and maintain data integrity within your database. With triggers, you can set up stored procedures that get automatically executed when certain events occur, like when data is inserted, updated, or deleted from a table.
Let’s explore triggers in SQLite and see how they can save you time and effort in managing your database.
What's in this article
What is are SQLite Triggers?
A trigger in SQLite is a special type of stored procedure that automatically runs (“fires”) when a specified database event occurs on a particular table.
Triggers are useful for enforcing business rules, validating input data, maintaining referential integrity, auditing changes, and automating repetitive tasks.
For example, you could create a trigger automatically updating a summary table whenever new data is inserted into the main table. Or one that prevents invalid data from being added to the database by raising an error.
SQLite supports the following types of SQLite Triggers:
- BEFORE – Runs BEFORE the event (insert, update, delete) occurs
- AFTER – Runs AFTER the event occurs
- INSTEAD OF – Replaces the event (for views only)
Triggers in SQLite fire once for each row affected by the database event, making them FOR EACH ROW triggers.
Creating Triggers in SQLite
To create a trigger in SQLite, you use the
CREATE TRIGGER statement. The basic syntax is:
CREATE TRIGGER trigger_name BEFORE|AFTER|INSTEAD OF event_type ON table_name BEGIN -- trigger logic END;
trigger_name– Name you want to give the trigger
BEFORE|AFTER|INSTEAD OF– When to fire the trigger
event_type– INSERT, UPDATE, or DELETE
table_name– Table on which to create the trigger
Let’s look at an example that creates an AFTER INSERT trigger:
CREATE TRIGGER new_user_trigger AFTER INSERT ON users BEGIN -- trigger logic END;
This trigger will fire after a new row is inserted into the
Inside the trigger logic, we can access special variables like
OLD to reference the affected rows:
NEW– Access newly inserted rows
OLD– Access deleted or updated rows
Now, let’s see some real-world examples of triggers in SQLite.
Example 1: Validating Input with a BEFORE Trigger
One common use case for triggers is to validate input data before allowing it into the database. This helps maintain data integrity and enforce business rules.
Let’s look at an example table called
CREATE TABLE orders ( id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, order_date TEXT NOT NULL );
We want to enforce that the
quantity column cannot be negative. To do this, we can create a BEFORE INSERT trigger that checks for negative quantities and raises an error if found:
CREATE TRIGGER quantity_check BEFORE INSERT ON orders FOR EACH ROW WHEN NEW.quantity < 0 BEGIN SELECT RAISE(ABORT, 'Quantity cannot be negative'); END;
This trigger will fire before each INSERT on the
orders table. It checks if
NEW.quantity (the new quantity being inserted) is less than 0. If so, it raises an error and aborts the INSERT.
Let’s test it out:
INSERT INTO orders (product_id, quantity, order_date) VALUES (1, -5, '2023-01-01'); -- Error: Quantity cannot be negative
It worked! Our trigger blocked the invalid INSERT
Also read: SQLite Upsert: Using INSERT ON CONFLICT.
This is a simple example, but it demonstrates how triggers can enforce custom validation rules in SQLite.
Example 2: Updating a Summary Table on INSERT
Another common use case for triggers is maintaining “summary” tables that store aggregates from the primary data tables.
For example, let’s say we have an
orders table that tracks all orders, and we want to keep a summary table
orders_by_month that sums the total quantity ordered per month.
Whenever a new order comes in, we need to insert it into
orders and update
orders_by_month. Triggers are perfect for automating this.
First, let’s create the tables:
CREATE TABLE orders ( id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, order_date TEXT NOT NULL ); CREATE TABLE orders_by_month ( month TEXT PRIMARY KEY, total_qty INTEGER NOT NULL );
Now we can create an AFTER INSERT trigger on
orders to update
CREATE TRIGGER update_monthly_totals AFTER INSERT ON orders BEGIN UPDATE orders_by_month SET total_qty = total_qty + NEW.quantity WHERE month = strftime('%Y-%m', NEW.order_date); INSERT INTO orders_by_month(month, total_qty) SELECT strftime('%Y-%m', NEW.order_date), NEW.quantity WHERE NOT EXISTS (SELECT 1 FROM orders_by_month WHERE month = strftime('%Y-%m', NEW.order_date)); END;
This trigger does two things:
- For existing months, it updates the total quantity
- For new months, it inserts a new record
orders_by_month always stays up to date whenever
Let’s test it by inserting a new order:
INSERT INTO orders VALUES (1, 1, 10, '2023-01-15'); -- orders_by_month is updated! -- 2023-01: 10
Our trigger fired after the INSERT and automatically propagated the change to
orders_by_month. Very handy!
Example 3: Creating Audit Logs with SQLite Triggers
A typical administrative task is creating audit logs to record changes made to necessary tables. Triggers provide an easy way to automate this logging without changing your application code.
Let’s look at an example with a
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL ); CREATE TABLE user_audit ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, change_type TEXT NOT NULL, change_time TIMESTAMP NOT NULL );
CREATE TRIGGER audit_user_insert AFTER INSERT ON users BEGIN INSERT INTO user_audit VALUES (NULL, NEW.id, 'INSERT', DATETIME('now')); END; CREATE TRIGGER audit_user_update AFTER UPDATE ON users BEGIN INSERT INTO user_audit VALUES (NULL, OLD.id, 'UPDATE', DATETIME('now')); END; CREATE TRIGGER audit_user_delete AFTER DELETE ON users BEGIN INSERT INTO user_audit VALUES (NULL, OLD.id, 'DELETE', DATETIME('now')); END;
These insert a new row into
user_audit on each insert, update or delete, recording the change details.
Let’s test them out:
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]'); -- Insert audit created UPDATE users SET email = '[email protected]' WHERE id = 1; -- Update audit created DELETE FROM users WHERE id = 1; -- Delete audit created
As you can see, triggers provide an easy way to capture audit trails without any extra coding needed automatically!
Example 4: Enforcing Referential Integrity with SQLite Triggers
Referential integrity is a key concept in database design that prevents orphaned records by enforcing parent-child relationships between tables.
For example, a
orders table may have a foreign key referencing a users table. If a user is deleted from users, we also want to delete their associated orders.
Triggers can enforce referential integrity by cascading deletes and updates across related tables.
Let’s look at an example with
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, FOREIGN KEY(user_id) REFERENCES users(id) ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, FOREIGN KEY(order_id) REFERENCES orders(id) );
If we delete a user, we want to delete their orders as well. And if we delete an order, we want to delete its items too.
Here are some triggers that will enforce these cascading deletes:
CREATE TRIGGER delete_user_orders AFTER DELETE ON users BEGIN DELETE FROM orders WHERE user_id = OLD.id; END; CREATE TRIGGER delete_order_items AFTER DELETE ON orders BEGIN DELETE FROM order_items WHERE order_id = OLD.id; END;
Let’s see this in action:
DELETE FROM users WHERE id = 1; -- User 1 deleted -- Orders for user 1 deleted -- Order items for those orders deleted
This ensures referential integrity is maintained automatically via triggers!
Example 5: Preventing Circular Trigger Logic using SQLite Triggers
When using triggers, you need to be careful to avoid circular trigger logic that causes infinite loops. This can occur when triggers cyclically update tables.
For example, let’s say we have two tables
tableB with AFTER UPDATE triggers that update each other:
CREATE TRIGGER tableA_trigger AFTER UPDATE ON tableA BEGIN UPDATE tableB SET x = NEW.x; END; CREATE TRIGGER tableB_trigger AFTER UPDATE ON tableB BEGIN UPDATE tableA SET x = NEW.x; END;
Now if we update
tableA, it will update
tableB, which will update
tableA again, in an endless loop!
To prevent this, SQLite provides the
recursive_triggers pragma. When enabled, it prevents recursive trigger execution:
PRAGMA recursive_triggers = TRUE; UPDATE tableA SET x = 5; -- Works now!
So remember to watch out for circular trigger logic and use
recursive_triggers to avoid problems.
Trigger Tips and Tricks
Here are some additional tips to use triggers effectively in SQLite:
- Combine triggers – Use one trigger to handle multiple events (INSERT, UPDATE, DELETE) instead of separate triggers to avoid duplication.
- ONLY fire when needed – In UPDATE triggers, use
WHEN NEW.column <> OLD.columnto only fire when specific columns change.
- Mind performance – Avoid heavy processing in triggers to minimize performance impact. Index columns used in WHERE clauses.
- Handle errors – Use the
RAISE()function to throw errors and
BEGIN...EXCEPTIONblocks to catch errors.
- Make idempotent – Ensure trigger logic gives same results if run multiple times for same condition.
- Encapsulate logic – Move complex trigger procedures into a separate function for reusability.
- Verify changes – Query affected tables after trigger execution to verify changes.
- Manage transactions – Use
ROLLBACKto control transactions.
Using triggers effectively takes some practice, but they are a very powerful tool for automating tasks and enforcing constraints in SQLite. Start simple and work your way up!
Here’s a quick summary of what we covered about triggers in SQLite:
- Triggers automatically run stored procedures on database events like INSERT, UPDATE, DELETE.
- Create triggers with
CREATE TRIGGERspecifying name, event timing (BEFORE/AFTER), event type (INSERT/UPDATE/DELETE), and table.
OLDto access affected rows in trigger logic.
- Common uses include validating input, maintaining summaries, auditing, and referential integrity.
- Avoid circular triggers and take care with performance, errors, and transactions.
- Use triggers judiciously to automate database tasks and enforce business rules.
I hope this guide gives you a good overview of how to use triggers effectively in SQLite. They are a powerful tool that can save you lots of time and effort once you master them!