SQLite Triggers, Views, and Generated Columns Cheatsheet

CREATE VIEW recent_posts AS
SELECT p.id, p.title, u.email AS author_email, p.created_at
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at >= date('now', '-30 day');

# Define a reusable read-only query projection.

SELECT * FROM recent_posts ORDER BY created_at DESC LIMIT 20;

# Read from the named projection like a table.

CREATE TRIGGER users_ai
AFTER INSERT ON users
BEGIN
  INSERT INTO audit_log(entity, entity_id, action)
  VALUES ('users', NEW.id, 'insert');
END;

# Write to an audit log when a row is created.

CREATE TRIGGER posts_au
AFTER UPDATE ON posts
BEGIN
  UPDATE posts
  SET updated_at = CURRENT_TIMESTAMP
  WHERE id = NEW.id;
END;

# Maintain a timestamp on row changes.

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  email_normalized TEXT GENERATED ALWAYS AS (lower(email)) STORED
);

# Compute a lowercased email for indexing or search.

CREATE INDEX idx_contacts_email_normalized ON contacts(email_normalized);

# Speed up queries over a derived value.