How to Use Triggers in SQLite

Key Insights

SQLite triggers automate database operations by executing SQL statements in response to INSERT, UPDATE, or DELETE events, eliminating the need for application-level logic for tasks like audit logging and data validation.
BEFORE triggers can modify data or prevent operations entirely, while AFTER triggers are ideal for cascading changes and logging—understanding this distinction is critical for correct implementation.
Triggers execute within the same transaction as the triggering statement, making them reliable for maintaining data integrity but potentially problematic for performance if overused or poorly designed.

Introduction to SQLite Triggers

Triggers are database objects that automatically execute specified SQL statements when certain events occur on a table. Think of them as event listeners for your database—when a row is inserted, updated, or deleted, triggers spring into action without any explicit call from your application code.

The primary advantage of triggers is centralizing business logic at the database level. Instead of implementing audit logging in every service that touches your database, you define it once in a trigger. This ensures consistency and reduces the chance of forgetting to log changes when adding new features.

Common use cases include maintaining audit trails, enforcing complex business rules that go beyond simple constraints, automatically updating timestamp columns, keeping denormalized data in sync, and implementing soft deletes. If you find yourself writing the same data manipulation logic across multiple parts of your application, a trigger might be the right solution.

Trigger Syntax and Basic Structure

SQLite triggers follow a straightforward syntax pattern. Every trigger needs a name, a table to attach to, a timing specification (BEFORE, AFTER, or INSTEAD OF), an event type (INSERT, UPDATE, or DELETE), and the SQL statements to execute.

Here’s the basic structure:

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

Let’s create a simple audit log system. First, we’ll set up our tables:

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

CREATE TABLE users_audit (
    audit_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    username TEXT,
    email TEXT,
    action TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

Now, an AFTER INSERT trigger that logs new user registrations:

CREATE TRIGGER log_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_audit (user_id, username, email, action)
    VALUES (NEW.id, NEW.username, NEW.email, 'INSERT');
END;

When you insert a user, the audit table automatically receives a record. The NEW keyword references the newly inserted row’s values.

Working with BEFORE and AFTER Triggers

The timing of trigger execution fundamentally changes what you can accomplish. BEFORE triggers execute before the triggering operation commits, giving you the opportunity to modify the data being inserted or updated, or even abort the operation by raising an error. AFTER triggers run after the operation completes, making them suitable for cascading changes and logging.

Use BEFORE triggers for validation and data normalization:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    discount_price DECIMAL(10,2)
);

CREATE TRIGGER validate_discount_price
BEFORE INSERT ON products
FOR EACH ROW
WHEN NEW.discount_price IS NOT NULL
BEGIN
    SELECT RAISE(ABORT, 'Discount price cannot be higher than regular price')
    WHERE NEW.discount_price > NEW.price;
END;

This trigger prevents invalid data from ever entering the database. Attempting to insert a product with a discount price higher than the regular price will fail immediately.

AFTER triggers are perfect for cleanup operations:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total DECIMAL(10,2)
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER
);

CREATE TRIGGER cleanup_order_items
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    DELETE FROM order_items WHERE order_id = OLD.id;
END;

When an order is deleted, this trigger automatically removes all associated order items, maintaining referential integrity without requiring foreign key CASCADE behavior.

Using NEW and OLD References

SQLite provides NEW and OLD pseudo-tables to access row data within triggers. The availability of these references depends on the trigger event:

INSERT triggers: Only NEW is available
DELETE triggers: Only OLD is available
UPDATE triggers: Both NEW and OLD are available

This UPDATE trigger tracks price changes for products:

CREATE TABLE price_history (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER track_price_changes
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN OLD.price != NEW.price
BEGIN
    INSERT INTO price_history (product_id, old_price, new_price)
    VALUES (NEW.id, OLD.price, NEW.price);
END;

The WHEN clause makes this trigger conditional—it only fires when the price actually changes. The UPDATE OF price specification further optimizes by only triggering on price column updates, not updates to other columns.

Practical Use Cases

Real-world applications often require sophisticated trigger combinations. Here’s a complete audit system with user tracking:

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_by INTEGER,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE document_history (
    id INTEGER PRIMARY KEY,
    document_id INTEGER,
    title TEXT,
    content TEXT,
    changed_by INTEGER,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    action TEXT
);

CREATE TRIGGER document_insert_audit
AFTER INSERT ON documents
FOR EACH ROW
BEGIN
    INSERT INTO document_history (document_id, title, content, changed_by, action)
    VALUES (NEW.id, NEW.title, NEW.content, NEW.created_by, 'CREATED');
END;

CREATE TRIGGER document_update_audit
AFTER UPDATE ON documents
FOR EACH ROW
BEGIN
    INSERT INTO document_history (document_id, title, content, changed_by, action)
    VALUES (NEW.id, NEW.title, NEW.content, NEW.updated_by, 'UPDATED');
END;

Automatic timestamp management is another common pattern:

CREATE TRIGGER update_document_timestamp
BEFORE UPDATE ON documents
FOR EACH ROW
BEGIN
    UPDATE documents SET updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;

Note: In SQLite, you can’t directly modify NEW values in AFTER triggers. For BEFORE triggers on UPDATE operations, you’d use SELECT NEW.updated_at = CURRENT_TIMESTAMP without the WHERE clause.

Trigger Limitations and Best Practices

SQLite triggers have specific limitations you need to understand. Triggers cannot use ATTACH, DETACH, or ALTER TABLE statements. Recursive triggers (triggers that fire themselves) are disabled by default, though you can enable them with PRAGMA recursive_triggers = ON—but be extremely careful with this setting to avoid infinite loops.

Performance is a critical consideration. Triggers execute within the same transaction as the triggering statement, so a slow trigger delays the entire operation. If your trigger performs complex calculations or multiple table scans, you’re adding that overhead to every single INSERT, UPDATE, or DELETE.

Avoid using triggers for:

Complex business logic better suited for application code
Operations requiring external API calls or file system access
Calculations that could be done at query time with views
Anything that makes debugging difficult

Keep triggers focused and simple. If you need to debug trigger behavior, enable query logging or use temporary tables to capture intermediate state:

CREATE TEMP TABLE trigger_debug (
    message TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER debug_example
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO trigger_debug (message)
    VALUES ('Inserted user: ' || NEW.username);
END;

Always consider whether a trigger is truly necessary. Sometimes a well-placed CHECK constraint, FOREIGN KEY with CASCADE, or application-level logic is clearer and more maintainable.

Managing and Dropping Triggers

SQLite stores trigger definitions in the sqlite_master system table. To list all triggers in your database:

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

To see triggers for a specific table:

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

Dropping a trigger is straightforward:

DROP TRIGGER IF EXISTS log_user_insert;

The IF EXISTS clause prevents errors if the trigger doesn’t exist, which is particularly useful in migration scripts.

When modifying a trigger, you must drop and recreate it—SQLite doesn’t support CREATE OR REPLACE TRIGGER. This pattern works well in migration scripts:

DROP TRIGGER IF EXISTS log_user_insert;

CREATE TRIGGER log_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_audit (user_id, username, email, action, timestamp)
    VALUES (NEW.id, NEW.username, NEW.email, 'INSERT', CURRENT_TIMESTAMP);
END;

Triggers are powerful tools for maintaining data integrity and automating database operations, but they come with complexity costs. Use them judiciously, document their behavior clearly, and always test thoroughly before deploying to production. When used appropriately, triggers can significantly simplify your application architecture by centralizing critical data management logic at the database level.