A trigger that keeps a log in SQL

A trigger is a statement the database runs for you on every write.

CREATE TABLE order_audit (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    action TEXT NOT NULL,
    logged_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER orders_insert_audit
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action)
    VALUES (NEW.id, 'created');
END;

How it works

1. AFTER INSERT fires once per row, with NEW bound to that row.
2. FOR EACH ROW is the only granularity SQLite offers.
3. A trigger is a hidden write, so keep it small and boring.

Keywords and builtins used here

• AFTER
• BEGIN
• CREATE
• CURRENT_TIMESTAMP
• DEFAULT
• EACH
• END
• FOR
• INSERT
• INTEGER
• INTO
• KEY
• NEW
• NOT
• NULL
• ON
• PRIMARY
• ROW
• TABLE
• TEXT
• TRIGGER
• VALUES

The run, in numbers

Lines

14

Characters to type

306

Tokens

57

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 38 seconds.

Type this snippet

Step 1 of 2 in Triggers, step 8 of 16 in Schema & constraints.

← Previous Next →