Respuesta.

Las tablas de auditoría de cambios (log/audit tables) son tablas comunes en las que se almacenan eventos de cambio de datos en tablas de producción: quién, cuándo y qué se cambió. Es relevante para el sector financiero, comercio electrónico, instituciones gubernamentales.

Enfoques para la implementación:

1. Registro explícito en el registro a través de la aplicación o procedimiento.
2. Uso de disparadores para rastrear cualquier cambio.
3. En nuevas bases de datos — mecanismos del sistema Change Data Capture (CDC) o Temporal Tables.

Composición típica:

• audit_id (PK), TableName, OperationType (I/U/D), RecordID, OldValue, NewValue, ChangedBy, ChangeDT

Ejemplo de código (disparador para auditar UPDATE)

CREATE TABLE ProductAudit (
  AuditID int IDENTITY(1,1) PRIMARY KEY,
  ProductID int,
  OldPrice money,
  NewPrice money,
  ChangedBy sysname,
  ChangeDT datetime,
  OperationType char(1)
);
GO
CREATE TRIGGER trg_ProductAudit
ON Products
AFTER UPDATE
AS
BEGIN
  INSERT INTO ProductAudit(ProductID, OldPrice, NewPrice, ChangedBy, ChangeDT, OperationType)
    SELECT d.ProductID, d.Price, i.Price, SYSTEM_USER, GETDATE(), 'U'
    FROM inserted i JOIN deleted d ON i.ProductID = d.ProductID
    WHERE i.Price <> d.Price;
END

Trampas subyacentes:

• Un gran volumen de la tabla de auditoría puede ralentizar la consulta.
• Se requiere particionado adecuado/eliminación de datos antiguos.
• Es necesario monitorear bloqueos y deduplicación de cambios.

Pregunta engañosa.

P: ¿Se revierte automáticamente la entrada en la tabla de log al revertir la transacción principal?

R: Si el disparador es parte de la transacción, sí, la inserción en el registro se revierte junto con la operación principal. Sin embargo, al registrar a nivel de aplicación/transacción separada, puede no haber consistencia.

Ejemplos de errores reales debido a la falta de conocimiento de los matices del tema.

---

Historia

En la institución de crédito no se implementó el mecanismo de limpieza de la tabla de auditoría. El volumen superó los cientos de millones de filas, lo que llevó a copias de seguridad largas, falta de espacio y degradación del rendimiento de toda la base de datos.

---

Historia

El desarrollador realizaba la auditoría a través de una conexión separada fuera de la transacción. Como resultado, aparecían registros de eventos que no existían (la transacción de la lógica de aplicación se revertía, pero la entrada en el registro no).

---

Historia

Al diseñar el registro de log de cambios se asignó a todos los campos el tipo NVARCHAR(MAX) por universalidad. Esto no fue necesario, solo sobrecargó el almacenamiento y la indexación — los datos no podían ser analizados de manera eficiente.