audit_triggers.sql in SQL

A price-change audit the application cannot forget to write: a trigger writes it.

-- An audit trail: every price change recorded by a trigger, automatically.
CREATE TABLE price_audit (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sku TEXT NOT NULL,
    old_price NUMERIC(10, 2),
    new_price NUMERIC(10, 2),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION log_price_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_audit (sku, old_price, new_price)
        VALUES (OLD.sku, OLD.price, NEW.price);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER price_history
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();

-- Two real changes and one stock-only touch; only the changes land.
UPDATE products
SET price = 1999.00
WHERE sku = 'KAYAK-17';

UPDATE products
SET price = 279.00
WHERE sku = 'PADDLE-C';

UPDATE products
SET stock = stock + 5
WHERE sku = 'DRYBAG-20';

SELECT sku, old_price, new_price
FROM price_audit
ORDER BY id;

How it works

1. The price_audit table and log_price_change() capture old and new prices.
2. The IF NEW.price <> OLD.price guard skips writes that touch other columns.
3. Three updates land two audit rows; the closing query reads the history.

Keywords and builtins used here

• AFTER
• AS
• BEGIN
• BY
• CREATE
• DEFAULT
• EACH
• END
• EXECUTE
• FOR
• FROM
• FUNCTION
• GENERATED
• IDENTITY
• IF
• INSERT
• INTEGER
• INTO
• KEY
• LANGUAGE
• NEW
• NOT
• NULL
• NUMERIC
• OLD
• ON
• ORDER
• PRIMARY
• RETURN
• RETURNS
• ROW
• SELECT
• SET
• TABLE
• TEXT
• THEN
• TRIGGER
• UPDATE
• VALUES
• WHERE
• trigger

The run, in numbers

Lines

43

Characters to type

931

Tokens

172

Three-star pace

65 tpm

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

Fastest run

1. 🇺🇸 brendancol37 tpm

Type this snippet

Step 1 of 3 in Encore, step 21 of 23 in PostgreSQL Stored Procedures.

← Previous Next →