viernes, 29 de mayo de 2009

Truco rápido: Histórico de cambios en una tabla usando un trigger

No me voy a enrollar explicando que es un trigger ni para que sirve, tenéis información de sobra en este link: CREATE TRIGGER (Transact-SQL)

Lo que voy a explicar aquí es como usar un trigger para que nos guarde en una tabla de históricos los cambios (INSERT y UPDATE) que se producen en una tabla. Para ello supongamos que tenemos una tabla con un identificador (campo Id) y un nombre (campo nombre), y queremos almacenar en la tabla Historico (Id,nombre_antiguo,nombre_nuevo), los cambios que se van produciendo.

Dado que se pueden ejecutar updates sobre varios registros a la vez, será necesario que nuestro trigger obtenga todos los cambios a la vez y los procese uno a uno, eso lo haremos con un cursor.

El truco está en cómo distinguiremos que se trata de un INSERT o de un UPDATE, es muy sencillo, en caso de ser un INSERT el motor de base de datos almacena un registro en la tabla temporal "INSERTED", y en el caso de un update, habrá 2 registros: uno en la tabla "DELETED" y otro en "INSERTED", es como si para actualizarlo, primero se borrase y después se volviese a insertar, cosas del SQL Server :P

Vamos con el código:


CREATE TRIGGER [dbo].[prueba_insert_update]
ON [dbo].[Pruebas]
AFTER INSERT, UPDATE
AS
BEGIN

SET NOCOUNT ON;


-- Identificador del registro
DECLARE @Id int


-- Variable para almacenar el nombre anterior
DECLARE @nombre_antiguo varchar(20)


-- Declaramos el cursor "Cursorito" para que contenga únicamente
-- los ID de la tabla temporal "INSERTED"
DECLARE Cursorito CURSOR FORWARD_ONLY
FOR
SELECT Id FROM INSERTED


-- Abrimos el cursor
OPEN Cursorito


-- Movemos el puntero al primer registro del cursor
FETCH NEXT FROM Cursorito into @Id


-- Recorremos el cursor, cuando @@Fetch_Status sea diferente de 0
-- habremos llegado al final
WHILE @@Fetch_Status = 0
BEGIN


SET @nombre_antiguo = ''


-- Buscamos en la tabla temporal "DELETED" si hay un registro con
-- el Id que toca en esta pasada, lo que significaría que
-- se ha producido un UPDATE
SELECT @nombre_antiguo = ISNULL(nombre,'')
FROM DELETED
WHERE Id = @Id


-- Si no hay ningún registro que cumpla la condición la
-- variable @nombre_antiguo contendrá '' y se tratará
-- de una inserción


-- Añadimos un registro en la tabla de históricos
INSERT INTO Historico
(Id,nombre_antiguo,nombre_nuevo)
SELECT @Id, @nombre_antiguo, nombre
FROM INSERTED
WHERE Id = @Id


-- Nos movemos al siguiente registro
FETCH NEXT FROM Cursorito into @Id

END


-- Cerramos el cursor y liberamos el recurso
CLOSE Cursorito
DEALLOCATE Cursorito

END



Happy coding ;)