SQLite CREATE TRIGGER – BEFORE/AFTER, WHEN Clause, OLD and NEW Examples | DbSchema

Table of Contents

1. What SQLite triggers do
2. SQLite CREATE TRIGGER syntax
3. BEFORE, AFTER, and INSTEAD OF compared
4. OLD and NEW references
5. Using the WHEN clause
6. Creating a trigger in sqlite3
7. Practical row-level trigger patterns
8. Creating a trigger in DbSchema
9. FAQ
10. Conclusion
11. References

SQLite CREATE TRIGGER lets the database react automatically to INSERT, UPDATE, or DELETE events. Triggers are often used for auditing, validation, derived values, and keeping related tables in sync without repeating logic in the application.

This guide focuses on the SQLite details that searchers usually need: BEFORE versus AFTER, the WHEN clause, OLD and NEW values, row-level trigger patterns, and how DbSchema helps document the behavior visually.

What SQLite triggers do

A trigger is stored SQL that fires automatically when a matching event occurs on a table or view. SQLite supports triggers for:

• INSERT
• UPDATE
• DELETE

A few SQLite-specific facts matter immediately:

• SQLite supports row-level triggers only; there is no FOR EACH STATEMENT trigger type
• BEFORE and AFTER triggers work on tables
• INSTEAD OF triggers work on views
• the trigger body can read OLD.column_name and NEW.column_name when those references make sense for the event

If you still need the table structure first, begin with SQLite CREATE DATABASE and SQLite CREATE TABLE.

SQLite CREATE TRIGGER syntax

The general syntax looks like this:

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

A realistic example:

CREATE TRIGGER log_student_update
AFTER UPDATE OF grade ON students
FOR EACH ROW
WHEN OLD.grade IS NOT NEW.grade
BEGIN
    INSERT INTO student_audit(student_id, old_grade, new_grade, changed_at)
    VALUES (OLD.student_id, OLD.grade, NEW.grade, datetime('now'));
END;

The WHEN clause is optional, but it is one of the best ways to keep trigger logic precise and avoid unnecessary writes.

BEFORE, AFTER, and INSTEAD OF compared

When in doubt, prefer AFTER unless you need to prevent or rewrite the original action before it lands.

OLD and NEW references

OLD and NEW expose row values to the trigger body.

Examples:

• in an INSERT trigger, use NEW.column_name
• in a DELETE trigger, use OLD.column_name
• in an UPDATE trigger, compare OLD.value and NEW.value

This is especially useful for audit logs and change detection.

Using the WHEN clause

The WHEN clause runs the trigger body only when the condition is true.

Example: log only meaningful price changes.

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

Without the WHEN clause, every UPDATE of products would write an audit row, even if the price was unchanged.

Creating a trigger in sqlite3

Open SQLite:

sqlite3 school.db

Create a table and an audit table:

CREATE TABLE students (
    student_id   INTEGER PRIMARY KEY,
    student_name TEXT NOT NULL,
    grade        TEXT NOT NULL
);

CREATE TABLE student_audit (
    audit_id     INTEGER PRIMARY KEY,
    student_id   INTEGER NOT NULL,
    old_grade    TEXT,
    new_grade    TEXT,
    changed_at   TEXT NOT NULL
);

Now create the trigger:

CREATE TRIGGER log_student_grade_change
AFTER UPDATE OF grade ON students
FOR EACH ROW
WHEN OLD.grade IS NOT NEW.grade
BEGIN
    INSERT INTO student_audit(student_id, old_grade, new_grade, changed_at)
    VALUES (OLD.student_id, OLD.grade, NEW.grade, datetime('now'));
END;

Test it:

INSERT INTO students(student_name, grade)
VALUES ('Alice', 'B');

UPDATE students
SET grade = 'A'
WHERE student_name = 'Alice';

SELECT *
FROM student_audit;

Expected result:

Practical row-level trigger patterns

1. Validation with RAISE()

Use a BEFORE trigger to stop invalid data:

CREATE TRIGGER validate_grade
BEFORE INSERT ON students
FOR EACH ROW
WHEN NEW.grade NOT IN ('A', 'B', 'C', 'D', 'F')
BEGIN
    SELECT RAISE(ABORT, 'grade must be A, B, C, D, or F');
END;

This complements, but does not replace, good table design. For structural rules, also review SQLite constraints.

2. Keep a summary table in sync

CREATE TABLE order_totals (
    order_id     INTEGER PRIMARY KEY,
    total_cents  INTEGER NOT NULL DEFAULT 0
);

CREATE TRIGGER update_order_total_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE order_totals
    SET total_cents = total_cents + NEW.line_total_cents
    WHERE order_id = NEW.order_id;
END;

3. Prefer clear, minimal trigger bodies

A trigger fires once per row. Heavy logic can become expensive on bulk updates, so keep the body short and index the tables it touches. If the trigger drives a lookup-heavy workflow, the next optimization topic is often SQLite indexes or SQLite EXPLAIN PLAN.

Also note these SQLite caveats:

• statements inside triggers support a restricted SQL subset compared with top-level statements
• recursive trigger behavior is controlled separately with PRAGMA recursive_triggers
• dropping a table drops its triggers automatically

Creating a trigger in DbSchema

DbSchema is helpful when you want trigger logic documented next to the schema instead of hidden in loose SQL files.

Typical workflow:

1. connect through the SQLite JDBC driver
2. reverse-engineer the tables and views into a diagram
3. open the target table and add or edit the trigger SQL
4. review related tables, foreign keys, and indexes visually before deployment
5. publish schema documentation or refine the layout in the diagram editor

That workflow makes it easier to explain why a trigger exists, which columns it touches, and which tables it updates.

FAQ

Does SQLite support statement-level triggers?

No. SQLite supports row-level triggers only.

When should I use the WHEN clause?

Use WHEN when the trigger should fire only for meaningful cases, such as a changed price, a non-null value, or a particular status.

Can I use OLD and NEW in every trigger?

No. NEW is available for INSERT and UPDATE; OLD is available for UPDATE and DELETE.

Does SQLite support INSTEAD OF triggers?

Yes. INSTEAD OF triggers are supported on views.

How do I disable a trigger in SQLite?

SQLite does not have a separate disable command. Drop the trigger with DROP TRIGGER trigger_name; and recreate it when needed.

Conclusion

SQLite triggers are best when the database should enforce or record behavior automatically for each row change. The most useful patterns are usually small: audit logs, targeted validation, and summary maintenance.

DbSchema helps by keeping the trigger SQL, table structure, and documentation together, which makes review and maintenance much easier as the schema grows.

References

1. SQLite Documentation: CREATE TRIGGER
2. SQLite Documentation: RAISE() function
3. DbSchema Documentation: Schema documentation
4. DbSchema: SQLite JDBC driver