Temporal Databases: Auditoría de tablas con SQL Server (1)

Desde hace un tiempo he estado mirando con cariño la funcionalidad de manejo de tiempo en tablas de las base de datos.

Uno de los escenarios mas fácil de implementar es el de Auditoría de tablas.

Suponiendo que tenemos una KB con una aplicación que usa SQL Server 2016 o superior, y tiene una tabla Productos

PRODUCTOS
*ProductoID
 ProductoNombre
 ProductoFamila
 UsuarioQueModifico 

y quiero tener una auditoria de como cambia dicha tabla a lo largo del tiempo.

Una forma fácil de lograr esto, es pasar la tabla a una tabla temporal de SQL Server .

Lo que se puede hacer es ejecutar:

Lo que hice fue un procedure AgregarTemporalidad que recibe un parámetro &TableName y ejecuta:

&Sentence = format("ALTER TABLE %1 ADD %1_ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN constraint %1_DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME()), %1_ValidTo datetime2 GENERATED ALWAYS AS ROW END   HIDDEN constraint %1_DF_ValidTo DEFAULT '9999.12.31 23:59:59.99' , PERIOD FOR SYSTEM_TIME (%1_ValidFrom, %1_ValidTo)",&TableName)

sql [!&Sentence!] 

&Sentence = format("ALTER TABLE %1 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.%1_History))",&TableName)

sql [!&Sentence!] 

Con esto se cambia la tabla agregando dos campos datetime de la forma: 

PRODUCTOS
*ProductoID
 ProductoNombre
 ProductoFamila
 UsuarioQueModifico
 Productos_ValidFrom  DateTime2
 Productos_ValidTo    DateTime2

y otra tabla adicional 

PRODUCTOS_History
*ProductoID
 ProductoNombre
 ProductoFamila
 UsuarioQueModifico
 Productos_ValidFrom  DateTime2
 Productos_ValidTo    DateTime2

Y queda relacionadas 

Cuando actualizo un registro en la tabla Productos, automáticamente salva un registro en la tabla Productos_History y ajusta el rango de vigencia.

Cuando borro el registro ProductoID=4 de PRODUCTOS, se salva el registro en el PRODUCTO_HISTORY.

De esta forma, vamos a tener una auditoria sencilla para las tablas que elijamos. Tal vez no sea apta para todos los proyectos, pero para algunos casos puede ser conveniente.

Esto no debería ser usado en tablas que tengan muchos cambios de estructura, pues hay operaciones que no pueden realizarse si la tabla esta marcada como System_versioning. 

Espero que esto pueda implementarse en forma mas facil con GeneXus en próximas versiones.