SQL Triggers

A trigger is a named block of SQL code that the database engine runs automatically whenever a specific event — an INSERT, UPDATE, or DELETE — happens on a table. Triggers let you enforce rules, keep derived data in sync, and log changes without relying on every application that touches the database to remember to do it itself. Used well, they centralize business logic inside the database; used carelessly, they create hidden side effects that are hard to debug — so understanding exactly when and how they fire matters as much as knowing the syntax.

Overview: How Triggers Work

Every trigger is attached to one table and one event type (INSERT, UPDATE, or DELETE), plus a timing (BEFORE, AFTER, or INSTEAD OF). When a statement modifies that table, the engine checks whether a matching trigger exists; if so, it runs the trigger’s body as part of the very same transaction as the triggering statement, before the statement is considered complete. If the trigger body raises an error or explicitly aborts, the whole triggering statement — and every row it already changed — is rolled back.

In SQLite (and MySQL), triggers are always row-level: for a statement that inserts, updates, or deletes many rows, the trigger body runs once per affected row, and inside that body you reference the row’s old values with OLD and its new values with NEW. (Some engines, notably PostgreSQL and SQL Server, also support statement-level triggers that fire once per statement regardless of row count — check your specific database’s documentation.) BEFORE triggers run before the row change is applied and before constraints like NOT NULL or CHECK are validated, which makes them useful for normalizing or validating data before it’s written. AFTER triggers run once the row change has already succeeded, which makes them the right place for logging, auditing, and cascading updates to other tables. INSTEAD OF triggers replace the triggering statement entirely — they exist mainly for views, which normally aren’t directly writable.

Triggers live in the database schema, not in application code, so they fire no matter which client issues the statement — a manual UPDATE typed into a database console fires the trigger just as reliably as an application’s code does. That consistency is the main argument for using a trigger instead of duplicating logic in every application that touches the table.

Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE [OF column_name] | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    -- one or more statements
    -- reference the triggering row with OLD.column and/or NEW.column
END;

Part	Meaning
trigger_name	A unique name for the trigger within the database.
BEFORE / AFTER / INSTEAD OF	When the trigger body runs relative to the triggering statement.
INSERT / UPDATE / DELETE	The event that fires the trigger. UPDATE OF column_name restricts it to updates whose SET clause touches that column.
ON table_name	The table (or view, for INSTEAD OF) the trigger watches.
WHEN condition	Optional filter; the trigger body only runs if this evaluates true for the current row.
OLD.column	The row’s previous value. Available for UPDATE and DELETE.
NEW.column	The row’s new value. Available for INSERT and UPDATE.
RAISE(ABORT, 'message')	Aborts the triggering statement and rolls it back with an error message.

Event	OLD available?	NEW available?
INSERT	No	Yes
UPDATE	Yes	Yes
DELETE	Yes	No

Examples

Example 1: Logging every price change

This trigger writes a row to an audit table only when a product’s price column is updated.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT,
    price REAL
);

CREATE TABLE price_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT
);

CREATE TRIGGER trg_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
BEGIN
    INSERT INTO price_audit (product_id, old_price, new_price, changed_at)
    VALUES (OLD.product_id, OLD.price, NEW.price, datetime('now'));
END;

INSERT INTO products (product_id, product_name, price) VALUES (1, 'Widget', 9.99);
UPDATE products SET price = 12.99 WHERE product_id = 1;

SELECT product_id, old_price, new_price FROM price_audit;

Result:

product_id	old_price	new_price
1	9.99	12.99

The trigger is scoped with UPDATE OF price, so it only fires when price is part of the update — not on every change to the row. Because it’s an AFTER trigger, it runs once SQLite has already applied the update, so OLD.price correctly holds 9.99 and NEW.price holds 12.99.

Example 2: Enforcing a business rule with BEFORE and RAISE

A BEFORE trigger combined with WHEN and RAISE can reject bad data before it’s ever written.

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

CREATE TRIGGER trg_check_salary
BEFORE INSERT ON employees_new
FOR EACH ROW
WHEN NEW.salary < 0
BEGIN
    SELECT RAISE(ABORT, 'Salary cannot be negative');
END;

INSERT INTO employees_new (id, name, salary) VALUES (1, 'Alice', 55000);

SELECT * FROM employees_new;

Result:

id	name	salary
1	Alice	55000

The WHEN clause means the trigger body only executes for rows where NEW.salary < 0; Alice’s row doesn’t match, so the insert proceeds normally and nothing is aborted. If a later statement tried to insert a row with salary -500, the WHEN condition would be true, RAISE(ABORT, ...) would run, and that insert would be rolled back with an error instead of ever appearing in the table.

Example 3: Keeping a summary table in sync

Triggers are a common way to maintain a denormalized counter or summary table alongside detail rows.

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

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    stock INTEGER
);

INSERT INTO inventory (product_id, stock) VALUES (100, 50);

CREATE TRIGGER trg_reduce_stock
AFTER INSERT ON orders_demo
FOR EACH ROW
BEGIN
    UPDATE inventory
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
END;

INSERT INTO orders_demo (order_id, product_id, quantity) VALUES (1, 100, 5);

SELECT product_id, stock FROM inventory;

Result:

product_id	stock
100	45

Inserting into orders_demo reaches into a second table, inventory, to keep it consistent, without the application ever writing an explicit "reduce stock" statement. This is powerful, but it comes with a cost: anyone reading orders_demo in isolation won’t see that inserting a row also changes inventory — the logic is invisible unless you know to check the schema’s triggers.

Under the Hood: Execution Order

When a statement that could fire a trigger runs, the engine works through a fixed sequence for each affected row:

1. The triggering statement is parsed and its target rows are identified.
2. For each affected row, any matching BEFORE triggers run, with OLD holding the current row and NEW holding the proposed new values (for INSERT/UPDATE). A BEFORE trigger can still reject the change with RAISE(ABORT, ...).
3. Table constraints (NOT NULL, CHECK, UNIQUE, foreign keys) are enforced.
4. The row change is applied to the table.
5. Any matching AFTER triggers run, now that the change is durable within the current transaction.
6. If any trigger raises an error, the entire triggering statement — including every row it already changed — is rolled back.

SQLite disables recursive trigger firing by default (controlled by the recursive_triggers pragma), which is one reason a trigger whose body modifies the same table it watches doesn’t automatically spiral into infinite recursion the way you might expect. Multiple triggers of the same type on the same table generally fire in the order they were created, though relying on that order across different database engines is fragile — keep one trigger per event when you can.

Common Mistakes

Mistake 1: Referencing OLD or NEW when the event doesn’t provide it

OLD only exists for UPDATE and DELETE; NEW only exists for INSERT and UPDATE. Mixing them up is a common copy-paste mistake:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT,
    price REAL
);

CREATE TABLE price_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER,
    old_price REAL,
    new_price REAL
);

CREATE TRIGGER trg_bad_insert
AFTER INSERT ON products
BEGIN
    INSERT INTO price_audit (product_id, old_price, new_price)
    VALUES (OLD.product_id, OLD.price, NEW.price);
END;

INSERT INTO products (product_id, product_name, price) VALUES (1, 'Widget', 9.99);

This fails because an INSERT has no "old" row — there was nothing there before the row was created, so OLD.product_id and OLD.price don’t exist. The fix is to only reference NEW inside an INSERT trigger:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT,
    price REAL
);

CREATE TABLE price_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER,
    old_price REAL,
    new_price REAL
);

CREATE TRIGGER trg_good_insert
AFTER INSERT ON products
BEGIN
    INSERT INTO price_audit (product_id, old_price, new_price)
    VALUES (NEW.product_id, NULL, NEW.price);
END;

INSERT INTO products (product_id, product_name, price) VALUES (1, 'Widget', 9.99);

SELECT product_id, old_price, new_price FROM price_audit;

Result: one row — product_id = 1, old_price = NULL, new_price = 9.99. There’s no old price to record for a brand-new product, so NULL is the correct value.

Mistake 2: Firing on every column update, not just the one you care about

Leaving off OF column_name means the trigger fires whenever any column in the row is updated, even changes that have nothing to do with the logic you’re trying to run:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

CREATE TABLE balance_log (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER,
    old_balance REAL,
    new_balance REAL
);

CREATE TRIGGER trg_log_balance_wrong
AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO balance_log (account_id, old_balance, new_balance)
    VALUES (OLD.id, OLD.balance, NEW.balance);
END;

INSERT INTO accounts (id, name, balance) VALUES (1, 'Alice', 100);
UPDATE accounts SET name = 'Alicia' WHERE id = 1;

SELECT * FROM balance_log;

Result: one row is logged (old_balance = 100, new_balance = 100) even though the balance never changed — only the name did. That’s noise in a log meant to track balance changes. Scoping the trigger to the specific column fixes it:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

CREATE TABLE balance_log (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER,
    old_balance REAL,
    new_balance REAL
);

CREATE TRIGGER trg_log_balance_right
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO balance_log (account_id, old_balance, new_balance)
    VALUES (OLD.id, OLD.balance, NEW.balance);
END;

INSERT INTO accounts (id, name, balance) VALUES (1, 'Alice', 100);
UPDATE accounts SET name = 'Alicia' WHERE id = 1;

SELECT COUNT(*) AS log_count FROM balance_log;

Result: log_count = 0. Because balance was never part of the SET clause, the AFTER UPDATE OF balance trigger doesn’t fire at all.

Best Practices

• Prefer AFTER triggers for auditing, logging, and cascading updates to other tables; reserve BEFORE triggers for validation and normalization that must happen before the row is written.
• Scope UPDATE triggers with OF column_name whenever you only care about changes to specific columns — it avoids firing on unrelated updates.
• Use a WHEN clause to keep the trigger body itself simple; let the condition do the filtering instead of wrapping the whole body in conditional logic.
• Keep trigger bodies small and fast — they run inside the same transaction as the triggering statement, so a slow trigger makes every insert, update, or delete on that table slower.
• Name triggers descriptively (for example trg_products_price_audit) and document them outside the database too, since they’re easy to forget exist when someone is debugging unexpected data changes.
• Avoid using a trigger to enforce logic that a CHECK constraint or foreign key could handle instead — constraints are simpler, faster, and easier to reason about.
• Be cautious with triggers that modify the same table they’re watching; understand your database’s recursive-trigger behavior before relying on it.
• Test triggers with statements that update many rows at once, not just single-row examples — row-level triggers run once per row, and bugs often only show up at scale.

Practice Exercises

1. Using an employees table with columns id, name, department, salary, and hire_date, write a BEFORE INSERT trigger that aborts the insert if salary is less than or equal to 0.
2. Create a customers_deleted table with columns customer_id and customer_name, then write an AFTER DELETE trigger on a customers table that copies the deleted row’s customer_id and customer_name into it before the row disappears. Hint: use OLD, not NEW, since this is a DELETE trigger.
3. Write an AFTER UPDATE OF price trigger on a products table that only logs a change when the new price is more than 20% higher than the old price. Hint: use a WHEN clause comparing NEW.price and OLD.price.

Summary

• A trigger is schema-level code that runs automatically on INSERT, UPDATE, or DELETE, with a timing of BEFORE, AFTER, or INSTEAD OF.
• OLD gives you the row’s previous values (available for UPDATE/DELETE); NEW gives you the row’s new values (available for INSERT/UPDATE).
• BEFORE triggers run before constraints are checked and can reject a change with RAISE(ABORT, ...); AFTER triggers run once the change is already applied within the transaction.
• Triggers run inside the same transaction as the statement that fired them — an error in a trigger rolls back the whole statement.
• Scope triggers precisely with OF column_name and WHEN conditions to avoid firing on changes you don’t actually care about.
• Because triggers are invisible in application code, keep them simple, well-named, and documented — otherwise they become a source of confusing hidden-side-effect bugs.