audit_trail.sql in SQL

Three triggers and a view: every change to a table, recorded where nobody has to remember to.

-- An audit log the application cannot forget to write to.

DROP VIEW IF EXISTS product_history;
DROP TRIGGER IF EXISTS products_audit_insert;
DROP TRIGGER IF EXISTS products_audit_update;
DROP TRIGGER IF EXISTS products_audit_delete;
DROP TABLE IF EXISTS product_audit;

CREATE TABLE product_audit (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    action TEXT NOT NULL CHECK (action IN ('insert', 'update', 'delete')),
    before_row TEXT,
    after_row TEXT,
    changed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER products_audit_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (product_id, action, after_row)
    VALUES (
        NEW.id,
        'insert',
        JSON_OBJECT('sku', NEW.sku, 'name', NEW.name, 'price', NEW.price)
    );
END;

CREATE TRIGGER products_audit_update
AFTER UPDATE ON products
FOR EACH ROW
WHEN OLD.price <> NEW.price OR OLD.name <> NEW.name
BEGIN
    INSERT INTO product_audit (product_id, action, before_row, after_row)
    VALUES (
        NEW.id,
        'update',
        JSON_OBJECT('name', OLD.name, 'price', OLD.price),
        JSON_OBJECT('name', NEW.name, 'price', NEW.price)
    );
END;

CREATE TRIGGER products_audit_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (product_id, action, before_row)
    VALUES (
        OLD.id,
        'delete',
        JSON_OBJECT('sku', OLD.sku, 'name', OLD.name, 'price', OLD.price)
    );
END;

CREATE VIEW product_history AS
SELECT
    a.id AS audit_id,
    a.changed_at,
    a.action,
    a.product_id,
    a.before_row ->> '$.price' AS price_before,
    a.after_row ->> '$.price' AS price_after,
    COALESCE(a.after_row ->> '$.name', a.before_row ->> '$.name') AS name
FROM product_audit AS a;

UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE sku LIKE 'FX-%';

SELECT *
FROM product_history
ORDER BY changed_at DESC, audit_id DESC;

How it works

One trigger per write kind; each records the row as JSON.
OLD and NEW give you the before and after inside the same trigger.
The view reads the log back as a readable timeline.

Keywords and builtins used here

AFTER
AS
BEGIN
BY
CHECK
COALESCE
CREATE
CURRENT_TIMESTAMP
DEFAULT
DELETE
DESC
DROP
EACH
END
EXISTS
FOR
FROM
IF
IN
INSERT
INTEGER
INTO
KEY
LIKE
NEW
NOT
NULL
OLD
ON
OR
ORDER
PRIMARY
ROW
SELECT
SET
TABLE
TEXT
TRIGGER
UPDATE
VALUES
VIEW
WHEN
WHERE

The run, in numbers

Lines: 73
Characters to type: 1756
Tokens: 364
Three-star pace: 100 tpm

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

Type this snippet

Step 2 of 2 in Encore, step 16 of 16 in Schema & constraints.

← Previous