Audit and History Tables

Audit Rows

Record a Change

An audit table stores who changed a row, what changed, and the before/after values that explain the change.

Program

Play the script to choose the actor recorded for a product price update.

actor_name
audit_insert_log.sql
Replay: real traced execution (multi-file project)
CREATE TABLE audit_choice AS WITH params(actor_name) AS (VALUES ('admin')) SELECT actor_name FROM params;
CREATE TABLE products (sku TEXT, name TEXT, price INTEGER);
CREATE TABLE product_audit (event_id INTEGER, sku TEXT, actor TEXT, action TEXT, old_price INTEGER, new_price INTEGER);
INSERT INTO products VALUES ('pen', 'Pen', 3), ('bag', 'Bag', 25);
UPDATE products SET price = 5 WHERE sku = 'pen';
INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);
SELECT event_id, sku, actor, action, old_price, new_price FROM product_audit ORDER BY event_id;

CREATE TABLE audit_choice AS WITH params(actor_name) AS (VALUES ('system')) SELECT actor_name FROM params;
CREATE TABLE products (sku TEXT, name TEXT, price INTEGER);
CREATE TABLE product_audit (event_id INTEGER, sku TEXT, actor TEXT, action TEXT, old_price INTEGER, new_price INTEGER);
INSERT INTO products VALUES ('pen', 'Pen', 3), ('bag', 'Bag', 25);
UPDATE products SET price = 5 WHERE sku = 'pen';
INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);
SELECT event_id, sku, actor, action, old_price, new_price FROM product_audit ORDER BY event_id;

  1. choice ← 1 row

    1CREATE TABLE audit_choice AS WITH params(actor_name) AS (VALUES ('admin')) SELECT actor_name FROM params;2CREATE TABLE products (sku TEXT, name TEXT, price INTEGER);
    values this step1 rowchoice

  2. products ← 2 rows

    3CREATE TABLE product_audit (event_id INTEGER, sku TEXT, actor TEXT, action TEXT, old_price INTEGER, new_price INTEGER);4INSERT INTO products VALUES ('pen', 'Pen', 3), ('bag', 'Bag', 25);5UPDATE products SET price = 5 WHERE sku = 'pen';
    values this step2 rowsproducts

  3. products ← 2 rows

    4INSERT INTO products VALUES ('pen', 'Pen', 3), ('bag', 'Bag', 25);5UPDATE products SET price = 5 WHERE sku = 'pen';6INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);
    values this step2 rowsproducts

  4. product_audit ← 1 row

    5UPDATE products SET price = 5 WHERE sku = 'pen';6INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);7SELECT event_id, sku, actor, action, old_price, new_price FROM product_audit ORDER BY event_id;
    values this step1 rowproduct_audit

  5. result ← 1 row

    6INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);7SELECT event_id, sku, actor, action, old_price, new_price FROM product_audit ORDER BY event_id;
    values this step1 rowresult

  1. choice ← 1 row

    1CREATE TABLE audit_choice AS WITH params(actor_name) AS (VALUES ('system')) SELECT actor_name FROM params;2CREATE TABLE products (sku TEXT, name TEXT, price INTEGER);
    values this step1 rowchoice

  2. products ← 2 rows

    3CREATE TABLE product_audit (event_id INTEGER, sku TEXT, actor TEXT, action TEXT, old_price INTEGER, new_price INTEGER);4INSERT INTO products VALUES ('pen', 'Pen', 3), ('bag', 'Bag', 25);5UPDATE products SET price = 5 WHERE sku = 'pen';
    values this step2 rowsproducts

  3. products ← 2 rows

    4INSERT INTO products VALUES ('pen', 'Pen', 3), ('bag', 'Bag', 25);5UPDATE products SET price = 5 WHERE sku = 'pen';6INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);
    values this step2 rowsproducts

  4. product_audit ← 1 row

    5UPDATE products SET price = 5 WHERE sku = 'pen';6INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);7SELECT event_id, sku, actor, action, old_price, new_price FROM product_audit ORDER BY event_id;
    values this step1 rowproduct_audit

  5. result ← 1 row

    6INSERT INTO product_audit VALUES (1, 'pen', (SELECT actor_name FROM audit_choice), 'price_change', 3, 5);7SELECT event_id, sku, actor, action, old_price, new_price FROM product_audit ORDER BY event_id;
    values this step1 rowresult
audit table `product_audit` stores a row describing the change.
before and after `old_price` and `new_price` make the update explainable later.
actor The selector changes who is recorded as making the change.