Triggers de auditoría

Un trigger es código SQL que la base de datos ejecuta sola cuando ocurre un INSERT, UPDATE o DELETE. Con ellos puedes llevar un historial de cambios (quién cambió qué, cuándo y cuál era el valor anterior) sin tocar el código de tu aplicación, y sin que nadie pueda saltárselo.

Casos reales: "¿quién cambió el precio de este producto?", "¿cuándo se canceló este pedido?", "recupera la descripción que había ayer".

Tabla de auditoría genérica

CREATE TABLE auditoria (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tabla       VARCHAR(64) NOT NULL,
  registro_id INT UNSIGNED NOT NULL,
  accion      ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  antes       JSON NULL,           -- valores previos
  despues     JSON NULL,           -- valores nuevos
  usuario_id  INT UNSIGNED NULL,   -- usuario de la APLICACIÓN (ver más abajo)
  usuario_db  VARCHAR(100) NULL,   -- usuario de MySQL
  creado_en   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY idx_aud_registro (tabla, registro_id),
  KEY idx_aud_fecha (creado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

¿Quién hizo el cambio?

La base de datos no sabe qué usuario de tu app inició sesión. La solución: antes de cada operación, tu aplicación define una variable de sesión de MySQL que los triggers leen.

PHP

// Justo después de conectar (o al inicio de cada petición autenticada)
db()->prepare('SET @usuario_app = ?')->execute([$_SESSION['usuario_id'] ?? null]);

Node (mysql2)

// Con pool, usa la misma conexión para el SET y la consulta
const conn = await pool.getConnection();
try {
  await conn.query('SET @usuario_app = ?', [req.usuario.id]);
  await conn.execute('UPDATE productos SET precio = ? WHERE id = ?', [199, 5]);
} finally {
  conn.release();
}

Triggers para la tabla productos (MySQL)

DELIMITER $$

CREATE TRIGGER productos_ai AFTER INSERT ON productos
FOR EACH ROW
BEGIN
  INSERT INTO auditoria (tabla, registro_id, accion, despues, usuario_id, usuario_db)
  VALUES ('productos', NEW.id, 'INSERT',
          JSON_OBJECT('nombre', NEW.nombre, 'precio', NEW.precio, 'stock', NEW.stock, 'activo', NEW.activo),
          @usuario_app, CURRENT_USER());
END$$

CREATE TRIGGER productos_au AFTER UPDATE ON productos
FOR EACH ROW
BEGIN
  -- Sólo registra si cambió algo relevante (<=> compara también NULLs)
  IF NOT (OLD.nombre <=> NEW.nombre AND OLD.precio <=> NEW.precio
          AND OLD.stock <=> NEW.stock AND OLD.activo <=> NEW.activo) THEN
    INSERT INTO auditoria (tabla, registro_id, accion, antes, despues, usuario_id, usuario_db)
    VALUES ('productos', NEW.id, 'UPDATE',
            JSON_OBJECT('nombre', OLD.nombre, 'precio', OLD.precio, 'stock', OLD.stock, 'activo', OLD.activo),
            JSON_OBJECT('nombre', NEW.nombre, 'precio', NEW.precio, 'stock', NEW.stock, 'activo', NEW.activo),
            @usuario_app, CURRENT_USER());
  END IF;
END$$

CREATE TRIGGER productos_ad AFTER DELETE ON productos
FOR EACH ROW
BEGIN
  INSERT INTO auditoria (tabla, registro_id, accion, antes, usuario_id, usuario_db)
  VALUES ('productos', OLD.id, 'DELETE',
          JSON_OBJECT('nombre', OLD.nombre, 'precio', OLD.precio, 'stock', OLD.stock, 'activo', OLD.activo),
          @usuario_app, CURRENT_USER());
END$$

DELIMITER ;

¿Qué es DELIMITER?

Dentro del trigger hay varios ;. DELIMITER $$ le dice al cliente de MySQL que la instrucción termina en $$, no en ;. En phpMyAdmin, escribe $$ en el campo "Delimitador" bajo el editor SQL.

Triggers de reglas de negocio

Además de auditar, los triggers sirven para mantener datos coherentes:

DELIMITER $$

-- Historial de estados de un pedido
CREATE TABLE IF NOT EXISTS pedido_historial (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pedido_id INT UNSIGNED NOT NULL,
  estado_anterior VARCHAR(20) NULL,
  estado_nuevo VARCHAR(20) NOT NULL,
  usuario_id INT UNSIGNED NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (pedido_id)
)$$

CREATE TRIGGER pedidos_estado_au AFTER UPDATE ON pedidos
FOR EACH ROW
BEGIN
  IF NOT (OLD.estado <=> NEW.estado) THEN
    INSERT INTO pedido_historial (pedido_id, estado_anterior, estado_nuevo, usuario_id)
    VALUES (NEW.id, OLD.estado, NEW.estado, @usuario_app);
  END IF;
END$$

-- Impide stock negativo con un error claro
CREATE TRIGGER productos_stock_bu BEFORE UPDATE ON productos
FOR EACH ROW
BEGIN
  IF NEW.stock < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El stock no puede ser negativo';
  END IF;
END$$

-- Devuelve el stock al cancelar un pedido
CREATE TRIGGER pedidos_cancelar_au AFTER UPDATE ON pedidos
FOR EACH ROW
BEGIN
  IF NEW.estado = 'cancelado' AND OLD.estado <> 'cancelado' THEN
    UPDATE productos pr
    JOIN pedido_items pi ON pi.producto_id = pr.id
    SET pr.stock = pr.stock + pi.cantidad
    WHERE pi.pedido_id = NEW.id;
  END IF;
END$$

DELIMITER ;

Consultar el historial

-- Historial completo de un producto
SELECT a.creado_en, a.accion, u.nombre AS usuario,
       a.antes->>'$.precio'   AS precio_antes,
       a.despues->>'$.precio' AS precio_despues
FROM auditoria a
LEFT JOIN usuarios u ON u.id = a.usuario_id
WHERE a.tabla = 'productos' AND a.registro_id = 5
ORDER BY a.creado_en DESC;

-- Cambios de precio de la última semana
SELECT registro_id, antes->>'$.nombre' AS producto,
       antes->>'$.precio' AS antes, despues->>'$.precio' AS despues, creado_en
FROM auditoria
WHERE tabla = 'productos' AND accion = 'UPDATE'
  AND JSON_EXTRACT(antes, '$.precio') <> JSON_EXTRACT(despues, '$.precio')
  AND creado_en >= NOW() - INTERVAL 7 DAY;

-- Ver los triggers existentes / eliminar uno
SHOW TRIGGERS LIKE 'productos';
DROP TRIGGER IF EXISTS productos_au;

Versión PostgreSQL (una función para todas las tablas)

En PostgreSQL puedes escribir una sola función genérica y conectarla a cualquier tabla:

CREATE TABLE auditoria (
  id          BIGSERIAL PRIMARY KEY,
  tabla       TEXT NOT NULL,
  registro_id BIGINT,
  accion      TEXT NOT NULL,
  antes       JSONB,
  despues     JSONB,
  usuario_id  INT,
  creado_en   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION fn_auditar() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO auditoria (tabla, registro_id, accion, antes, despues, usuario_id)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    NULLIF(current_setting('app.usuario_id', true), '')::INT  -- SET app.usuario_id = '7' desde tu app
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Conectarla a las tablas que quieras auditar
CREATE TRIGGER trg_auditar_productos AFTER INSERT OR UPDATE OR DELETE ON productos
FOR EACH ROW EXECUTE FUNCTION fn_auditar();

CREATE TRIGGER trg_auditar_pedidos AFTER INSERT OR UPDATE OR DELETE ON pedidos
FOR EACH ROW EXECUTE FUNCTION fn_auditar();

Desde Node con pg, dentro de una transacción: await cliente.query("SELECT set_config('app.usuario_id', $1, true)", [String(req.usuario.id)]).

Cuidado con el rendimiento

Cada trigger añade trabajo a cada escritura. Audita sólo las tablas importantes (productos, pedidos, usuarios, pagos), no tablas de mucho tráfico como logs o sesiones. Y programa una limpieza: DELETE FROM auditoria WHERE creado_en < NOW() - INTERVAL 1 YEAR.

Hosting compartido

Algunos hostings no permiten crear triggers (falta el privilegio TRIGGER o SUPER con binlog activo). Si es tu caso, registra la auditoría desde el código de tu aplicación con una función auditar($tabla, $id, $accion, $antes, $despues).