Offensive Security Researcher & Quantum Cryptography Analyst

Triggers in SQLite: A Complete Guide With Practical Examples

Abdul Wahab Junaid
Triggers in SQLite

If you have spent any real time working with SQLite, you have probably reached a point where you wished the database itself could “react” to changes instead of you writing extra application code every single time a row gets inserted, updated, or deleted. That is exactly the problem triggers solve. I want to walk you through everything I know about triggers in SQLite — what they are, how they work under the hood, the exact syntax you need, real examples you can copy and adapt, and the mistakes I see people make over and over again.

By the end of this article, you should be comfortable creating, managing, and debugging triggers in your own SQLite databases.

What Exactly Is a Trigger in SQLite?

A trigger is a named database object that automatically executes a set of SQL statements whenever a specified event happens on a specified table. The event is usually an INSERT, UPDATE, or DELETE operation. Think of a trigger as a small piece of logic that sits inside the database itself, waiting quietly until the right moment, then firing off automatically without any explicit call from your application.

I like to describe triggers to beginners as “if this, then that” rules for your database. If a row gets inserted into orders, then automatically update inventory. If someone deletes a row from employees, then automatically log that deletion into an employee_audit table. You don’t have to remember to write that logic in every script, every API endpoint, or every admin tool — the database handles it consistently, every time.

This is powerful because it centralizes logic. Instead of scattering “update the timestamp” or “log this change” code across ten different places in your application, you write it once as a trigger, and SQLite guarantees it runs whenever the relevant event occurs, no matter which client or script caused it.

Why Use Triggers?

Here are the situations where I reach for triggers instead of application-level code:

That said, triggers are not free. They add hidden behavior to your database, and hidden behavior can be confusing to new developers on a team who don’t know a trigger exists. I always recommend documenting your triggers clearly and keeping their logic as simple as possible.

The Basic Syntax

Here is the general structure for creating a trigger in SQLite:

CREATE TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE]
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    -- SQL statements to execute
END;

Let’s break this down piece by piece because understanding each clause is what lets you build triggers confidently instead of just copying examples blindly.

1. Trigger Timing: BEFORE, AFTER, INSTEAD OF

This tells SQLite when the trigger should fire relative to the triggering event.

I use AFTER triggers most often for logging, because I want the log entry to reflect what actually happened. I use BEFORE triggers when I need to validate or modify something before it’s committed, such as rejecting an invalid insert.

2. Trigger Event: INSERT, UPDATE, DELETE

This specifies which operation activates the trigger. You can also target specific columns for UPDATE triggers, like this:

CREATE TRIGGER trg_name
AFTER UPDATE OF price ON products
BEGIN
    ...
END;

That trigger only fires when the price column specifically is updated, not when any other column changes. This is a subtle but very useful feature — I use it constantly to avoid unnecessary trigger executions.

3. FOR EACH ROW

In SQLite, all triggers are effectively row-level triggers — there is no such thing as a statement-level trigger like you might find in PostgreSQL. Including FOR EACH ROW is optional syntax that SQLite accepts for compatibility, but the behavior is always per-row regardless.

4. WHEN Clause

The WHEN clause lets you add a condition so the trigger body only executes if that condition is true. This is incredibly useful for avoiding wasted work.

CREATE TRIGGER trg_check_stock
AFTER UPDATE ON inventory
WHEN NEW.quantity < 10
BEGIN
    INSERT INTO low_stock_alerts (product_id, alert_date)
    VALUES (NEW.product_id, datetime('now'));
END;

Here, the alert only gets created if the new quantity drops below 10 — otherwise the trigger body is skipped entirely.

Understanding OLD and NEW

Inside a trigger body, you get access to two special “pseudo-tables” (really just row references): OLD and NEW.

For INSERT, only NEW exists because there was no previous row. For DELETE, only OLD exists because there’s no new row. For UPDATE, both are available, so you can compare before-and-after values.

Practical Example 1: Auto-Updating a Timestamp

One of the most common uses for a trigger is making sure an updated_at column always reflects the last modification time, without relying on application code to set it correctly.

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TRIGGER trg_tasks_updated_at
AFTER UPDATE ON tasks
FOR EACH ROW
BEGIN
    UPDATE tasks
    SET updated_at = datetime('now')
    WHERE id = OLD.id;
END;

Now, every time I run an UPDATE on the tasks table, updated_at refreshes automatically. I never have to remember to include that field in my UPDATE statements again.

Practical Example 2: Audit Logging

Let’s say I want to track every time a row is deleted from an employees table, including who deleted it and when.

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

CREATE TABLE employee_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    name TEXT,
    salary REAL,
    deleted_at TEXT
);

CREATE TRIGGER trg_employee_delete_audit
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, name, salary, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.salary, datetime('now'));
END;

Now every deletion leaves a permanent trace, even if the deletion came from a careless DELETE FROM employees; statement without a WHERE clause. This has genuinely saved projects I’ve worked on from disaster more than once.

Practical Example 3: Enforcing a Custom Business Rule

Suppose I want to prevent anyone from inserting a negative price into a products table. While CHECK constraints can technically do this in modern SQLite, triggers give you more flexibility for complex, multi-column, or cross-table validation.

CREATE TRIGGER trg_prevent_negative_price
BEFORE INSERT ON products
FOR EACH ROW
WHEN NEW.price < 0
BEGIN
    SELECT RAISE(ABORT, 'Price cannot be negative');
END;

The RAISE(ABORT, 'message') function stops the entire operation and rolls it back, returning the specified error message. This is the standard way to make a trigger reject an operation.

SQLite supports several RAISE behaviors:

Practical Example 4: Keeping a Summary Table in Sync

Let’s say I have an order_items table and I want to maintain a running total in an orders table without recalculating it every time with a SUM() query.

CREATE TRIGGER trg_update_order_total_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total = (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = NEW.order_id)
    WHERE id = NEW.order_id;
END;

I’d typically create matching triggers for UPDATE and DELETE on order_items as well, so the orders.total column always stays accurate no matter what changes.

INSTEAD OF Triggers on Views

Views in SQLite are normally read-only. If you try to INSERT, UPDATE, or DELETE directly against a view, SQLite will complain. INSTEAD OF triggers solve this by letting you define what should actually happen when someone tries to modify a view.

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE is_active = 1;

CREATE TRIGGER trg_insert_active_user
INSTEAD OF INSERT ON active_users
BEGIN
    INSERT INTO users (name, email, is_active)
    VALUES (NEW.name, NEW.email, 1);
END;

Now, when someone runs INSERT INTO active_users (name, email) VALUES (...), SQLite doesn’t try to insert into the view directly — it runs the logic inside the trigger instead, inserting into the real underlying users table.

Dropping and Managing Triggers

To remove a trigger, you use:

DROP TRIGGER trigger_name;

To see what triggers exist in your database, you can query the sqlite_master table:

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

This is genuinely one of the most useful debugging queries I run when I inherit a database I didn’t build myself — it tells me immediately what hidden automation exists.

Common Pitfalls I’ve Run Into

Recursive trigger loops. If a trigger on table A updates table A itself, and that update fires the same trigger again, you can get an infinite loop or at least unexpected recursive behavior. SQLite disables recursive triggers by default, but if you turn on PRAGMA recursive_triggers = ON;, you need to be extremely careful.

Triggers firing on bulk operations. Triggers fire once per affected row, not once per statement. If you run an UPDATE that touches 10,000 rows, your trigger runs 10,000 times. This can slow down bulk operations significantly, so for large batch jobs I sometimes temporarily drop the trigger, do the bulk work, then recreate it.

Forgetting that triggers don’t fire on some operations. Triggers won’t fire for changes made through certain low-level APIs, like when SQLite’s TRUNCATE optimization deletes all rows in a table without a WHERE clause via a special internal path in some builds, or when foreign key ON DELETE CASCADE actions occur — actually, cascading deletes DO fire triggers on the child table in SQLite, but this is a point of confusion, so always test your assumptions with a real example.

Overusing triggers for logic that belongs in the application. Just because you can enforce a rule in a trigger doesn’t mean you always should. If the logic is complex and rarely changes, a trigger is great. If it’s business logic that changes frequently, it might be easier to maintain in your application layer where you have proper testing and version control tools.

Best Practices I Follow

  1. Name triggers descriptively. Something like trg_orders_update_total tells you immediately what it does and which table it touches, rather than a vague name like trigger1.
  2. Keep trigger bodies short. If a trigger body starts growing beyond a handful of statements, consider whether the logic really belongs at the database level.
  3. Document every trigger. I keep a simple markdown file in every project listing each trigger, its purpose, and the table it’s attached to. Future me (and my teammates) always thank past me for this.
  4. Test triggers with edge cases. Especially NULL values and bulk operations — these are where trigger bugs hide.
  5. Use WHEN clauses to avoid unnecessary work. Don’t let a trigger execute a full body of logic if a simple condition can skip it early.
  6. Be cautious with cascading triggers. When a trigger on one table causes changes on another table that itself has triggers, trace through the full chain mentally (or on paper) before deploying.

Triggers and Transactions

It’s worth understanding how triggers interact with transactions, because this affects how you reason about error handling and rollback behavior. When a trigger fires as part of a larger operation — say, an INSERT statement that’s part of a multi-statement transaction — the trigger’s actions become part of that same transaction. If the trigger body fails for any reason (a constraint violation, a RAISE(ABORT, ...) call, or any other error), the failure propagates back to the original statement that caused the trigger to fire.

This means if you have a chain of triggers — trigger A fires trigger B, which fires trigger C — and trigger C fails, the entire chain rolls back together as a single unit, undoing everything from A, B, and C, plus the original statement that started the chain. This “all or nothing” behavior is exactly what you want for data integrity, but it also means a single misbehaving trigger deep in a chain can silently prevent an entirely unrelated-looking operation from succeeding, which can be confusing to debug if you don’t know the full trigger chain exists.

I always recommend keeping a mental (or literal, written) map of trigger dependencies in any database where triggers cascade into other triggers, specifically for this reason.

Performance Considerations

Triggers add overhead to every operation they’re attached to, and this overhead is easy to underestimate when you’re testing with a handful of sample rows but haven’t thought through what happens at scale. A few performance notes worth internalizing:

For large batch operations where triggers would meaningfully slow things down, a common and reasonable pattern is to temporarily disable or drop the relevant trigger, perform the bulk operation, then recreate the trigger and run any necessary catch-up logic (like a single bulk UPDATE to fix summary tables) afterward.

Frequently Asked Questions

Can I have multiple triggers on the same event and table?

Yes. SQLite allows multiple triggers for the same event (say, multiple AFTER INSERT triggers on the same table). They execute in the order they were created, though I’d generally recommend consolidating related logic into a single trigger where practical, simply for easier maintenance and debugging.

Do triggers fire during a transaction rollback?

No. If a transaction is rolled back, any changes made by triggers within that transaction are undone along with everything else, exactly as you’d expect from standard transactional behavior.

Can a trigger call itself recursively?

Only if PRAGMA recursive_triggers = ON; is set, and even then, only under specific conditions where the triggering statement type matches. By default, recursive triggering is disabled in SQLite, which prevents a whole class of accidental infinite loops.

Do triggers work with INSERT OR REPLACE and INSERT OR IGNORE?

Yes, but the exact firing behavior depends on how the conflict resolution plays out. An INSERT OR REPLACE that ends up deleting a conflicting row before inserting the new one can fire both DELETE and INSERT triggers as part of that single statement, which is worth testing explicitly if your schema relies on this.

Wrapping Up

Triggers in SQLite are one of those features that feel intimidating the first time you look at the syntax, but once you’ve written two or three of them, they become second nature. They let you push repetitive, rule-based logic down into the database itself, which means fewer bugs from forgotten application code and more consistent behavior across every client that touches your data.

My advice: start small. Write a trigger that just updates a timestamp. Then try one that logs deletions. Once those feel comfortable, move on to more advanced patterns like maintaining summary tables or validating business rules with RAISE(). Triggers are a genuinely powerful tool once you understand exactly when and how they fire — and now you do.

Exit mobile version