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 + <nombre de tabla>) 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:

USE [AdventureWorks]
GO;

DECLARE
  -- Variables para obtener datos de tablas
  @cursor_tablas CURSOR,			-- Variable de tipo cursor, para recorrer el listado de tablas de la base de datos
  @t_object_id INT,				-- Object_id de cada tabla
  @t_name SYSNAME,				-- Nombre de cada tabla
  @t_schema_name SYSNAME,			-- Nombre del esquema al que pertenece cada tabla

  -- Variables para elaborar sentencia SQL de creación/modificación del trigger
  @tgr_nombre NVARCHAR(100),		-- Nombre del trigger
  @cmd_sql NVARCHAR(MAX),			-- Sentencia SQL de creación/modificación del trigger
  @cmd_cond_join NVARCHAR(MAX),	-- Condición del JOIN, a usar en la sentencia UPDATE dentro de cada trigger

  -- Otras variables
  @columna_fechamod SYSNAME;		-- Nombre de la columna que contendrá la fecha de modificación de la tabla

-- Estableciendo nombre de la columna de fecha de modificación.
-- Basta cambiarlo según sea el caso.
SET @columna_fechamod = 'ModifiedDate';

-- Obteniendo listado de tablas a actualizar
SET @cursor_tablas = CURSOR FOR
SELECT
  t.object_id,
  t.name,
  SCHEMA_NAME(t.schema_id)
FROM sys.tables t
  INNER JOIN sys.columns c ON t.object_id =c.object_id AND c.name = @columna_fechamod
WHERE t.type='U'
  AND t.name NOT LIKE 'sys%'	-- Para no tomar en cuenta la tabla dbo.sysdiagrams
ORDER BY t.schema_id, t.name;

OPEN @cursor_tablas;

FETCH NEXT FROM @cursor_tablas INTO @t_object_id, @t_name, @t_schema_name;

WHILE @@fetch_status = 0
  BEGIN
    -- Creando nombre del trigger. El nombre de todos los trigger tiene el mismo prefijo,
    -- y se le añade el esquema y nombre de la tabla a la que pertenece, para hacerlo único.
    SET @tgr_nombre = @t_schema_name + '.Tg_FechaMod_' + @t_schema_name + '_' + @t_name;

    -- Listando columnas de PK de tabla, para condición del JOIN en la sentencia UPDATE del trigger
    SET @cmd_cond_join = NULL;

    SELECT @cmd_cond_join = CASE WHEN @cmd_cond_join IS NULL THEN 'T.' ELSE @cmd_cond_join + ' AND T.' END + c.name + '=I.' + c.name
    FROM sys.indexes AS i
      INNER JOIN sys.index_columns AS ic
        ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
      INNER JOIN sys.columns AS c
        ON ic.OBJECT_ID = c.OBJECT_ID AND ic.column_id = c.column_id
    WHERE ic.object_id = @t_object_id AND i.is_primary_key = 1

    -- Creando la sentencia de creación/modificación del trigger
    SET @cmd_sql =
-- Si el trigger no existe, se creará, pero si existe, se modificará
CASE WHEN OBJECT_ID(@tgr_nombre) IS NULL THEN 'CREATE ' ELSE 'ALTER ' END
+ 'TRIGGER ' + @tgr_nombre + '
ON ' + @t_schema_name + '.' + @t_name + '
AFTER UPDATE NOT FOR REPLICATION
AS
BEGIN
  SET NOCOUNT ON;

  IF (@@NESTLEVEL > 0) RETURN;

  UPDATE T
  SET ' + @columna_fechamod + ' = GETDATE()
  FROM ' + @t_schema_name + '.' + @t_name + ' T
    INNER JOIN INSERTED I ON
      ' + @cmd_cond_join + ';
END';

    -- Mostrando sentencia SQL a ejecutar
    PRINT '';
    PRINT @cmd_sql;
    PRINT '';

    -- Ejecutando sentencia SQL
    EXEC(@cmd_sql);

    -- Continuando con la siguiente tabla
    FETCH NEXT FROM @cursor_tablas INTO @t_object_id, @t_name, @t_schema_name;
END

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_<esquema>_<tabla>.

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:

CREATE TRIGGER Purchasing.Tg_FechaMod_Purchasing_PurchaseOrderDetail
ON Purchasing.PurchaseOrderDetail
AFTER UPDATE NOT FOR REPLICATION
AS
BEGIN
  SET NOCOUNT ON;

  IF (@@NESTLEVEL > 0) RETURN;

  UPDATE T
  SET ModifiedDate = GETDATE()
  FROM Purchasing.PurchaseOrderDetail T
    INNER JOIN INSERTED I ON
      T.PurchaseOrderID=I.PurchaseOrderID AND T.PurchaseOrderDetailID=I.PurchaseOrderDetailID
END

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.

Saludos.

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s