CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
Package common joins and projections into named queries.
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');Views are great for simplifying repeated report queries or exposing app-friendly shapes.
SELECT * FROM recent_posts ORDER BY created_at DESC LIMIT 20;Useful for dashboards and administrative pages.
Automate timestamps, audit rows, and derived fields.
CREATE TRIGGER users_ai
AFTER INSERT ON users
BEGIN
INSERT INTO audit_log(entity, entity_id, action)
VALUES ('users', NEW.id, 'insert');
END;A classic pattern for audit trails and event capture.
CREATE TRIGGER posts_au
AFTER UPDATE ON posts
BEGIN
UPDATE posts
SET updated_at = CURRENT_TIMESTAMP
WHERE id = NEW.id;
END;Be careful to avoid unintended recursive behavior depending on trigger design and settings.
CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
email_normalized TEXT GENERATED ALWAYS AS (lower(email)) STORED
);Generated columns can simplify lookups and enforce repeatable derivations close to the data.
CREATE INDEX idx_contacts_email_normalized ON contacts(email_normalized);High-value when the generated value is used frequently in filters or joins.