Audit Rows - SQL Programming

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.

audit_insert_log.sql

CREATE TABLE audit_choice AS WITH params(actor_name) AS (VALUES ()) 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 ()) 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;

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.