¿Cómo implementar un mecanismo de auditoría o seguimiento de cambios (historia de cambios / registro) en tablas SQL? ¿Cuáles son los enfoques, sus ventajas y desventajas, y qué es importante considerar al diseñar tal funcionalidad?

Respuesta

El mecanismo de auditoría (registro o historia de cambios) se implementa para rastrear todos los cambios de datos y garantizar la transparencia. Existen varios enfoques principales:

1. Disparadores de auditoría (Audit Triggers): Disparadores AFTER/BEFORE INSERT/UPDATE/DELETE especiales que guardan el estado antiguo y/o nuevo de la fila en una tabla de auditoría separada. Ventaja: transparencia para la aplicación; desventaja: posible desaceleración de las operaciones DML.

2. Aislamiento de la historia en una tabla separada (History Table): Se aplica la versionado de registros: al cambiar, se copia la "fila antigua" en la tabla de historia con una marca de tiempo. La tabla principal contiene solo datos actuales.

3. Mecanismos integrados (Change Data Capture, Temporal Tables): Por ejemplo, en SQL Server se puede activar la SYSTEM_VERSIONED TEMPORAL TABLE, y la base de datos almacenará automáticamente la historia de cambios.

Ejemplo (MySQL, disparador para auditoría):

CREATE TABLE user_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(10),
  old_value TEXT,
  new_value TEXT,
  changed_at DATETIME
);

DELIMITER //
CREATE TRIGGER audit_user_update
  AFTER UPDATE ON users
  FOR EACH ROW BEGIN
    INSERT INTO user_audit (user_id, action, old_value, new_value, changed_at)
    VALUES (OLD.id, 'UPDATE', OLD.name, NEW.name, NOW());
END//
DELIMITER ;

Pregunta trampa

Pregunta: ¿Siempre es suficiente tener un disparador de auditoría solo en UPDATE, si se requiere un protocolo completo de historia de cambios?

Respuesta: No. La historia completa solo se puede obtener combinando disparadores en todos los tipos de operaciones: INSERT (registrar nuevas filas), UPDATE (registrar cambios) y DELETE (registrar registros eliminados).

Ejemplo:

-- Es necesario crear 3 disparadores separados para cada operación; de lo contrario, habrá "vacíos" en la historia.

Ejemplos de errores reales debido a la falta de conocimiento sobre los detalles del tema

---

Historia 1: La empresa usó solo el disparador de UPDATE para la auditoría y durante un año no se dio cuenta de que los registros eliminados no se registraban de ninguna manera. Al investigar un incidente, no pudieron determinar cuándo se eliminó una cuenta de usuario importante.

---

Historia 2: El proyecto almacenaba los valores antiguos y nuevos de los cambios en un solo campo de texto en forma de JSON serializado, lo que complicó mucho el análisis: se necesitaron analizadores complejos y una réplica separada de la base de datos para informes.

---

Historia 3: En una base de datos con alta carga, los desarrolladores implementaron auditoría a través de disparadores, sin tener en cuenta el crecimiento del volumen de la tabla de registros. Con el tiempo, la tabla de historia superó a la tabla base en 50 veces, lo que llevó a la saturación del almacenamiento y fallos en todo el proyecto.

---