audit_trail.sql en SQL

Tres triggers y una vista: cada cambio a una tabla, registrado sin que nadie deba acordarse.

-- Una bitácora de auditoría que la aplicación no puede olvidar escribir.

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;

Cómo funciona

Un trigger por tipo de escritura; cada uno registra la fila como JSON.
OLD y NEW te dan el antes y el después dentro del mismo trigger.
La vista lee el log de vuelta como una línea de tiempo legible.

Palabras clave y builtins usados aquí

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

El intento, en números

Líneas: 73
Caracteres a escribir: 1771
Tokens: 364
Ritmo de tres estrellas: 100 tpm

Al ritmo de tres estrellas de 100 tokens por minuto, este intento toma unos 218 segundos.

Escribe este fragmento

Paso 2 de 2 en Bis; paso 16 de 16 en Esquema y restricciones.

← Anterior