CREATE TRIGGER

Create a trigger that automatically executes one or more SQL statements when a row is inserted into, updated in, or deleted from a table.

Syntax

CREATE [TEMPORARY] TRIGGER [IF NOT EXISTS] [schema-name.]trigger-name
    [BEFORE | AFTER | INSTEAD OF]
    {INSERT | UPDATE [OF column-name, ...] | DELETE}
    ON table-name
    [FOR EACH ROW]
    [WHEN expression]
BEGIN
    statement;
    [statement; ...]
END;

Description

A trigger defines a set of SQL statements that run automatically when a specified data modification event occurs on a table or view. Triggers execute within the same transaction as the statement that fired them -- if the transaction is rolled back, the trigger's effects are also rolled back.

Parameters

Trigger Timing

BEFORE Triggers

A BEFORE trigger fires before the triggering statement modifies the row. Use BEFORE triggers to validate or transform data before it is written.

• The NEW row reference contains the values that are about to be written. For INSERT triggers, NEW is the row being inserted. For UPDATE triggers, NEW contains the updated values.
• The OLD row reference is available in UPDATE and DELETE triggers and contains the current values before modification.
• If a BEFORE trigger raises an error, the triggering operation is aborted for that row.

AFTER Triggers

An AFTER trigger fires after the triggering statement has modified the row. Use AFTER triggers for logging, auditing, or cascading changes to other tables.

• The NEW and OLD row references are available with the same semantics as BEFORE triggers.
• The row has already been written when the trigger body executes.

INSTEAD OF Triggers

An INSTEAD OF trigger can only be created on a view. It fires in place of the triggering INSERT, UPDATE, or DELETE, allowing you to make views writable.

• The actual INSERT, UPDATE, or DELETE on the view does not execute. The trigger body is responsible for performing the desired changes on the underlying tables.

Row References

Inside a trigger body, NEW and OLD are special row references that provide access to column values.

-- Access individual columns
NEW.email
OLD.status

WHEN Clause

The optional WHEN clause filters which rows cause the trigger body to execute. The expression can reference NEW and OLD columns.

-- Only fire when the status column actually changes
CREATE TRIGGER log_status_change
    AFTER UPDATE OF status ON orders
    WHEN OLD.status != NEW.status
BEGIN
    INSERT INTO order_log (order_id, old_status, new_status, changed_at)
    VALUES (NEW.id, OLD.status, NEW.status, datetime('now'));
END;

UPDATE OF Columns

For UPDATE triggers, you can restrict the trigger to fire only when specific columns are modified. Without the OF clause, the trigger fires on any UPDATE to the table.

-- Only fires when price or quantity changes, not when name changes
CREATE TRIGGER recalc_total
    BEFORE UPDATE OF price, quantity ON line_items
BEGIN
    UPDATE line_items SET total = NEW.price * NEW.quantity WHERE id = NEW.id;
END;

RAISE Function

The RAISE function is used inside trigger bodies (and other contexts) to interrupt execution and signal an error. It takes one of four forms:

CREATE TRIGGER validate_age
    BEFORE INSERT ON users
BEGIN
    SELECT RAISE(ABORT, 'age must be positive')
    WHERE NEW.age <= 0;
END;

Multiple Statements

A trigger body can contain multiple SQL statements separated by semicolons. The statements execute in order within the same transaction.

CREATE TRIGGER on_user_delete
    AFTER DELETE ON users
BEGIN
    DELETE FROM user_preferences WHERE user_id = OLD.id;
    DELETE FROM user_sessions WHERE user_id = OLD.id;
    INSERT INTO audit_log (action, entity, entity_id, performed_at)
    VALUES ('delete', 'user', OLD.id, datetime('now'));
END;

Examples

Audit Logging Trigger

Track all changes to a table with an audit log.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL DEFAULT 0
);

CREATE TABLE account_audit (
    id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    action TEXT NOT NULL,
    old_balance REAL,
    new_balance REAL,
    changed_at TEXT NOT NULL
);

CREATE TRIGGER audit_balance_change
    AFTER UPDATE OF balance ON accounts
BEGIN
    INSERT INTO account_audit (account_id, action, old_balance, new_balance, changed_at)
    VALUES (NEW.id, 'update', OLD.balance, NEW.balance, datetime('now'));
END;

-- This UPDATE automatically creates an audit row
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

Validation Trigger

Enforce business rules before data is written.

CREATE TABLE reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    check_in TEXT NOT NULL,
    check_out TEXT NOT NULL
);

CREATE TRIGGER validate_reservation
    BEFORE INSERT ON reservations
BEGIN
    SELECT RAISE(ABORT, 'check_out must be after check_in')
    WHERE NEW.check_out <= NEW.check_in;
END;

Cascading Update Trigger

Propagate changes to related tables.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category_id INTEGER
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- When a category is deleted, unset the category on all associated products
CREATE TRIGGER clear_product_category
    BEFORE DELETE ON categories
BEGIN
    UPDATE products SET category_id = NULL WHERE category_id = OLD.id;
END;

See Also

• DROP TRIGGER for removing triggers
• CREATE VIEW for views that can use INSTEAD OF triggers
• INSERT, UPDATE, DELETE for the statements that fire triggers