Trigger Introduction

A trigger is SQL that runs automatically in response to data changes.

You use triggers to enforce rules, keep audit logs, or maintain derived tables without repeating logic in every query.

Core Concepts

flowchart LR
  A[INSERT / UPDATE / DELETE on table] --> B{Trigger defined for event?}
  B -->|Yes| C[Trigger body executes]
  B -->|No| D[No extra action]
  C --> E[Validation, logging, or side effects]

Trigger timing	Meaning
BEFORE	Runs before row change
AFTER	Runs after row change
INSTEAD OF	Replaces action (commonly on views)

Good Fits

• Data validation close to the schema
• Lightweight audit/event logging
• Maintaining summary counters

Use Caution

• Complex business workflows with many hidden side effects
• Logic that is hard to test or reason about

Code Examples

-- Setup: base table and audit log table.
CREATE TABLE products_trg (
  product_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  price      REAL NOT NULL
);

CREATE TABLE product_audit_trg (
  audit_id    INTEGER PRIMARY KEY,
  product_id  INTEGER NOT NULL,
  action      TEXT NOT NULL,
  created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Expected output
Base and audit tables are created successfully.

-- Trigger: log every inserted product row.
CREATE TRIGGER trg_products_after_insert
AFTER INSERT ON products_trg
FOR EACH ROW
BEGIN
  INSERT INTO product_audit_trg (product_id, action)
  VALUES (NEW.product_id, 'INSERT');
END;

Expected output
Trigger created successfully.

-- Insert into base table; trigger logs automatically.
INSERT INTO products_trg (name, price)
VALUES ('Keyboard', 99.0);

SELECT product_id, action
FROM product_audit_trg;

product_id	action
1	INSERT

SQLite-Specific Nuances

SQLite Nuance

SQLite triggers are always defined FOR EACH ROW (row-level behavior).

There is no statement-level trigger mode.

Common Pitfalls / Best Practices

Pitfall

Packing too much hidden logic into triggers and making write behavior hard to trace.

Best Practice

Keep trigger logic short, deterministic, and focused on one responsibility.

Quick Challenge

Create an AFTER INSERT trigger on products_trg that writes NEW.product_id and action NEW_ROW to product_audit_trg.

View Solution

CREATE TRIGGER trg_products_new_row
AFTER INSERT ON products_trg
FOR EACH ROW
BEGIN
  INSERT INTO product_audit_trg (product_id, action)
  VALUES (NEW.product_id, 'NEW_ROW');
END;

Expected output
New trigger created and ready to log insert events.