PROGRAMANDO EN .NET: 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