SQLite Triggers: Automate Actions on Table Changes (2026)

SQLite triggers can automate actions in response to data modification events on a table.

Let’s see one in action. Imagine you have a products table and you want to keep a running log of every price change.

-- Create the products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

-- Create a table to log price changes
CREATE TABLE price_log (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    change_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Create the trigger
CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
FOR EACH ROW
BEGIN
    INSERT INTO price_log (product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

Now, let’s insert some data and then update a price:

-- Insert a product
INSERT INTO products (name, price) VALUES ('Gadget X', 19.99);

-- Check the product table
SELECT * FROM products;
-- Output: 1, Gadget X, 19.99

-- Update the price
UPDATE products SET price = 24.99 WHERE id = 1;

-- Check the product table again
SELECT * FROM products;
-- Output: 1, Gadget X, 24.99

-- Check the price log table
SELECT * FROM price_log;
-- Output: 1, 19.99, 24.99, 2023-10-27 10:30:00 (timestamp will vary)

The log_price_change trigger executed automatically after the UPDATE statement. It captured the OLD price (19.99) and the NEW price (24.99) for the product with id = 1 and inserted them into the price_log table.

Triggers are defined using the CREATE TRIGGER statement. You specify when the trigger should fire (AFTER UPDATE OF price), what table it’s associated with (ON products), and for which rows (FOR EACH ROW). The BEGIN...END block contains the SQL statements that execute when the trigger fires. Inside this block, you can reference OLD values (the row’s state before the change) and NEW values (the row’s state after the change).

The primary use case for triggers is enforcing data integrity and maintaining audit trails. You can use them to:

• Audit changes: As shown, log modifications to sensitive data.
• Maintain summary data: Update aggregate values in another table when a related table changes. For example, if you have an orders table and an order_items table, a trigger on order_items could update the total_amount in the orders table.
• Enforce complex business rules: Implement logic that cannot be expressed with simple CHECK constraints. For instance, preventing a user from deleting a record if it’s referenced in another table in a way that a foreign key constraint doesn’t cover.
• Cascade operations: Perform related inserts, updates, or deletes on other tables.

You can create triggers for INSERT, UPDATE, and DELETE operations. You can also specify that a trigger should fire BEFORE or AFTER the event. BEFORE triggers can be used to modify NEW values before they are written to the table, or to abort the operation entirely by returning ABORT.

A common pattern is to use a trigger to automatically populate a last_modified_timestamp column.

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME
);

CREATE TRIGGER update_user_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

This trigger ensures that the updated_at column is always current whenever a user’s record is modified.

It’s crucial to understand that triggers execute within the same transaction as the statement that fired them. If the triggering statement is rolled back, the actions performed by the trigger are also rolled back. Conversely, if a trigger fails (e.g., due to a constraint violation), it will cause the entire transaction to fail.

When you’re debugging triggers, remember that OLD and NEW are special keywords that refer to the row before and after the UPDATE or DELETE (for OLD), and the row before insertion or after modification (for NEW). For INSERT triggers, OLD is not available. For DELETE triggers, NEW is not available.

The ability to execute arbitrary SQL statements within a trigger context, especially for AFTER triggers that operate on data that has already been committed to the row, means you can implement very sophisticated data management logic. For example, you could have a trigger that, upon inserting a new order, automatically checks inventory levels in a separate inventory table and decrements the stock count. If the stock count would drop below a predefined minimum, the trigger could raise an error, preventing the order from being placed and rolling back the inventory update.

You can also create INSTEAD OF triggers on views. These triggers allow you to perform actions on underlying tables when a user attempts to modify data through a view that doesn’t directly map to a single table’s primary key or has complex joins.

The next step is to explore BEFORE triggers and how they can be used to validate or modify data before it’s written to the table.