A trigger that stamps a change in SQL

The classic: keep an updated_at column honest without trusting every caller.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    updated_at TEXT
);

CREATE TRIGGER articles_touch
AFTER UPDATE OF title, body ON articles
FOR EACH ROW
WHEN OLD.body <> NEW.body OR OLD.title <> NEW.title
BEGIN
    UPDATE articles
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END;

How it works

1. WHEN narrows a trigger to the updates that actually changed something.
2. OLD and NEW hold the row before and after the write.
3. Guarding on the column stops the update inside the trigger from looping.

Keywords and builtins used here

• AFTER
• BEGIN
• CREATE
• CURRENT_TIMESTAMP
• EACH
• END
• FOR
• INTEGER
• KEY
• NEW
• NOT
• NULL
• OF
• OLD
• ON
• OR
• PRIMARY
• ROW
• SET
• TABLE
• TEXT
• TRIGGER
• UPDATE
• WHEN
• WHERE

The run, in numbers

Lines

16

Characters to type

324

Tokens

71

Three-star pace

90 tpm

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

Type this snippet

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

← Previous Next →