AFTER Triggers

AFTER triggers run after a row is inserted, updated, or deleted.

They are ideal for side effects like audit logs and summary counters.

Core Concepts

flowchart TD
  A[Row change occurs] --> B[AFTER trigger runs]
  B --> C[Execute side-effect SQL]
  C --> D[Derived data stays in sync]

Common pattern	Example
Audit logging	Write to events table after insert
Counter maintenance	Increment/decrement totals table

Insert Counter

CREATE TRIGGER trg_posts_after_insert
AFTER INSERT ON posts_at
FOR EACH ROW
BEGIN
  UPDATE user_stats_at
  SET post_count = post_count + 1
  WHERE user_id = NEW.user_id;
END;

Expected output
Trigger created to increment post counts.

Delete Counter

CREATE TRIGGER trg_posts_after_delete
AFTER DELETE ON posts_at
FOR EACH ROW
BEGIN
  UPDATE user_stats_at
  SET post_count = post_count - 1
  WHERE user_id = OLD.user_id;
END;

Expected output
Trigger created to decrement post counts.

Code Examples

-- Setup tables for counter-maintenance example.
CREATE TABLE users_at (
  user_id INTEGER PRIMARY KEY,
  name    TEXT NOT NULL
);

CREATE TABLE user_stats_at (
  user_id    INTEGER PRIMARY KEY,
  post_count INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE posts_at (
  post_id   INTEGER PRIMARY KEY,
  user_id   INTEGER NOT NULL,
  title     TEXT NOT NULL
);

INSERT INTO users_at (user_id, name)
VALUES (1, 'Ada');

INSERT INTO user_stats_at (user_id, post_count)
VALUES (1, 0);

Expected output
Base, stats, and posts tables created with one user initialized.

-- Create and test AFTER INSERT trigger.
CREATE TRIGGER trg_posts_after_insert
AFTER INSERT ON posts_at
FOR EACH ROW
BEGIN
  UPDATE user_stats_at
  SET post_count = post_count + 1
  WHERE user_id = NEW.user_id;
END;

INSERT INTO posts_at (user_id, title)
VALUES (1, 'First Post');

SELECT user_id, post_count
FROM user_stats_at;

user_id	post_count
1	1

SQLite-Specific Nuances

SQLite Nuance

AFTER triggers see the final row values (NEW for insert/update, OLD for update/delete) after the change is applied.

Common Pitfalls / Best Practices

Pitfall

Creating multiple triggers with overlapping side effects on the same table and losing track of write behavior.

Best Practice

Document trigger side effects and keep each trigger responsibility narrow (one audit stream or one counter pattern).

Quick Challenge

Create an AFTER DELETE trigger on posts_at that decrements user_stats_at.post_count for the deleted row's user.

View Solution

CREATE TRIGGER trg_posts_after_delete
AFTER DELETE ON posts_at
FOR EACH ROW
BEGIN
  UPDATE user_stats_at
  SET post_count = post_count - 1
  WHERE user_id = OLD.user_id;
END;

Expected output
Trigger created and delete operations can keep counters in sync.