SQL Server Snippets - Columna de fecha de última modificación en SQL Server

Supongamos que por cuestiones de control de modificaciones y auditoría, se quiere contar con un campo de fecha de última modificación en todas las tablas de una base de datos de SQL Server.

El inconveniente es que SQL Server no posee nativamente un tipo de dato que guarde la fecha de última modificación de un registro, el cual se actualice automáticamente al modificar una fila de datos. Por ello, una alternativa a esto es definir una columna de tipo datetime en cada tabla que se actualice cada vez que se modifique uno o más registros de esta, a través de un trigger.

A veces se piensa que el tipo de dato timestamp sirve para este propósito, ya que el tipo de dato del mismo nombre presenta este comportamiento en otros sistemas gestores de bases de datos (como MySQL). Sin embargo, en SQL Server, el tipo de datos timestamp en realidad es un número autogenerado que sirve para identificar si una fila ha sido modificada. Para la versión 2012, este tipo de dato está obsoleto, y ha sido sustituido por rowversion, el cual posee un nombre más acorde a su funcionamiento.

Cabe mencionar que el esquema previo es el adoptado en la base de datos de muestra AdventureWorks, en la que se puede observar que casi todas las tablas (excepto las tablas de bitácora y la sysdiagrams) poseen un campo llamado ModifiedDate, el cual se actualiza mediante un trigger (cuyo nombre cumple con el formato u + ) cada vez que se modifica alguna fila. Nótese que se ha tomado la convención de nombrar el campo de igual forma en todas las tablas, para identificarlo fácilmente.

El detalle está en cómo hacer para poner un trigger en cada tabla de la base de datos. Ir escribiendo el código para cada una de las tablas sería una tarea larga y tediosa. Por lo tanto lo mejor sería tener un proceso automático que creara, o bien actualizara, todos los triggers para cada una de las tablas de la base de datos.

Y como la pereza es la madre de la invención (son bromas, jejeje...), a continuación les muestro un script para crear los triggers de actualización del campo de fecha de modificación, tomando como ejemplo el caso de la base de datos AdventureWorks:



Nótese que el script básicamente lo que hace es buscar en las vistas del sistema para la base de datos actual, las tablas que contienen la columna de fecha de modificación (cuyo nombre se especifica a través de la variable @columna_fechamod), y para cada una de las tablas elabora el script de creación del trigger (o modificación, si el trigger ya existe). Ello se hace dentro de la sentencia WHILE, en la que primeramente se define el nombre del trigger, con el formato Tg_FechaMod__.

Luego, se obtiene una cadena de caracteres que contiene el listado de las columnas que componen la llave primaria de la tabla en cuestión, en la variable @cmd_cond_join, de la siguiente forma:

T.campo1=I.campo1 AND T.campo2=I.campo2 ...

Después se arma el script completo de creación o modificación del trigger, en la variable @cmd_sql. En este script, la cadena previamente formada sirve como condición del INNER JOIN realizado entre la tabla a la que pertenece el trigger y la pseudo-tabla de filas insertadas/modificadas, INSERTED, en la sentencia UPDATE que actualiza el campo de fecha de modificación para las filas actualizadas.

Finalmente se imprime el script en pantalla, y se ejecuta. El proceso se repite luego para la siguiente tabla de la lista, y así sucesivamente.

Pueden verse los comentarios del script para comprender con mayor detalle el funcionamiento de este.

A continuación se muestra una de las sentencias de creación de trigger generado para la tabla Purchasing.PurchaseOrderDetail, como resultado de ejecutar el script previo en la base de datos AdventureWorks:



ACTUALIZACIÓN: Cabe mencionar algo importante: La línea IF (@@NESTLEVEL > 0) RETURN; sirve para evitar que el trigger se ejecute recursivamente, ya que solo se procede si el nivel de anidamiento de ejecución (@@NESTLEVEL) es cero, que es la primera vez que se ejecuta el trigger dentro de la misma sentencia. Esto debido a que cuando se realice el UPDATE dentro del trigger sobre la misma tabla para actualizar la fecha de modificación, se volverá a ejecutar el trigger, y así podría seguir en un ciclo infinito (bueno, en realidad no infinito, sino solamente hasta 32 veces, que es el límite que permite SQL Server, después del cual se genera un error).

Nótese también en la sentencia previa que el trigger se crearía para la operación UPDATE específicamente, y que en él se actualiza el campo ModifiedDate de la tabla. Esto se hace sólo para las filas modificadas, haciendo un INNER JOIN entre la tabla en cuestión y la pseudo-tabla de filas insertadas (INSERTED), mediante los campos que componen la llave primaria de la tabla.

El script propuesto ha sido probado en SQL Server 2005 y 2012, pero muy probablemente funcione en versiones posteriores.

Saludos.

Publicado originalmente el 2013-03-20, en https://itsouvenirs.wordpress.com/2013/03/20/sql-server-snippets-columna-de-fecha-de-ultima-modificacion-en-sql-server/.