Auditando los cambios mediante tablas temporales con versión del sistema

Hoy en día, las aplicaciones tecnológicas deben presentar soluciones compatibles con los sistemas y procedimientos de auditoría y control. Pues es imperativo cumplir con estrictas reglamentaciones, especialmente en todo aquello referente a la seguridad y los datos. Es justamente por esto, por lo que me he lanzado a preparar este breve ejemplo de como conseguir implementar esta capacidad mediante el uso del versionado gracias a las tablas temporales en Azure SQL Server.

Lo primero es disponer de una base de datos en Azure, y acto seguido crear una tabla destino donde recogeremos todos los cambios de estado y otra tabla transaccional sobre la que queramos ejecutar el control de versiones. En nuestro caso, vincularemos directamente por código la tabla origen y la tabla destino.

CREATE TABLE dbo.CustomerHistory
(
  CustomerId		INT NOT NULL
  , FirstName		VARCHAR(100) NOT NULL
  , LastName		VARCHAR(100) NOT NULL
  , DocumentNumber	VARCHAR(10) NOT NULL
  , DocumentType	VARCHAR(10) NOT NULL
  , ValidFrom		DATETIME2 NOT NULL
  , ValidTo		DATETIME2 NOT NULL
);
GO

CREATE TABLE dbo.Customer
(
    CustomerId          INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
  , FirstName		VARCHAR(100) NOT NULL
  , LastName		VARCHAR(100) NOT NULL
  , DocumentNumber	VARCHAR(10) NOT NULL
  , DocumentType	VARCHAR(10) NOT NULL
  , ValidFrom		DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , ValidTo		DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));

Si insertamos dos registros en nuestra tabla dbo.Customer y hacemos un simple select vemos que nuestros registros están ahí.

Si se realizara una modificación del campo DocumentNumber debido a un error en el proceso de alta y volviéramos a realizar el select sobre la tabla observaríamos que ha sido actualizado.

Pero no se ha modificado el número del DNI, también la ha hecho el ValidFrom. Como observamos que el campo de la letra del DNI está en minúscula, volvemos a actualizar desde nuestra aplicación el registro y ya por fin, lo dejaremos presumiblemente correcto. ¡Vamos a ello!

Justo, el valor es correcto y el ValidFrom ha vuelto a cambiar. Pero y si ahora quisiéramos poder hacer un seguimiento de las modificaciones que ha sufrido el cliente Filemón, ¿qué haríamos?

SELECT
	CustomerId
	, FirstName
	, LastName
	, DocumentNumber
	, DocumentType
	, ValidFrom
	, ValidTo
FROM 
	dbo.Customer
FOR SYSTEM_TIME 
	BETWEEN '2021-07-15 13:01:40.5089166' 
        AND '9999-12-31 23:59:59.9999999'
WHERE 
	CustomerId = 2 
ORDER BY 
	ValidFrom;

Con esta sencilla consulta somos capaces de visualizar todas las versiones de nuestro cliente Filemón. Sin duda una ayuda muy útil y sencilla de implementar en Microsoft SQL Server a partir de la versión 2016 (13.x)

Foto de portada gracias a David McEachan en Pexels