Create Trigger

Creating a trigger means defining three essentials:

1. When it runs (BEFORE/AFTER + event)
2. Where it runs (target table/view)
3. What SQL it executes (BEGIN ... END body)

Core Concepts

flowchart TD
  A[CREATE TRIGGER name] --> B[Timing + Event]
  B --> C[Target table/view]
  C --> D[FOR EACH ROW]
  D --> E[BEGIN ... SQL ... END]

Syntax part	Example
Trigger name	trg_orders_after_insert
Timing/event	AFTER INSERT
Target	ON orders_ct
Body	INSERT INTO audit ...

Code Examples

-- Setup base table and event log.
CREATE TABLE orders_ct (
  order_id INTEGER PRIMARY KEY,
  total    REAL NOT NULL
);

CREATE TABLE order_events_ct (
  event_id   INTEGER PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  event_type TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Expected output
Tables for trigger demo are created.

-- Create trigger that logs new order inserts.
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders_ct
FOR EACH ROW
BEGIN
  INSERT INTO order_events_ct (order_id, event_type)
  VALUES (NEW.order_id, 'INSERT');
END;

Expected output
Trigger created successfully.

-- Test trigger behavior.
INSERT INTO orders_ct (total)
VALUES (120.0);

SELECT order_id, event_type
FROM order_events_ct;

order_id	event_type
1	INSERT

SQLite-Specific Nuances

SQLite Nuance

Inside trigger bodies, SQLite exposes pseudo-records:

• NEW.column for inserted/updated values
• OLD.column for deleted/updated previous values

Common Pitfalls / Best Practices

Pitfall

Using OLD in an INSERT trigger (or NEW in contexts where it does not exist).

Best Practice

Name triggers with pattern <table>_<timing>_<event> so intent is obvious during maintenance.

Quick Challenge

Create an AFTER DELETE trigger on orders_ct that logs deleted order_id with event type DELETE.

View Solution

CREATE TRIGGER trg_orders_after_delete
AFTER DELETE ON orders_ct
FOR EACH ROW
BEGIN
  INSERT INTO order_events_ct (order_id, event_type)
  VALUES (OLD.order_id, 'DELETE');
END;

Expected output
Trigger created and ready to log row deletions.