SQL Server: Auditoría de datos personalizada mediante triggers

 Los triggers o desencadenadores son disparadores que saltan cuando realizamos la acción o evento al que van asociados. En MS SQL Server, además de los triggers clásicos relacionados con acciones DML (insert, update, delete) que se ejecutan en su lugar (instead of) y después (after triggers), desde SQL Server 2008 (por lo menos) existe otro tipo que son los triggers asociados a acciones que se producen por consultas DDL. Este segundo tipo de trigger está más pensado para labores administrativas como la propia auditoria, para el control de cierto tipo de operaciones e incluso evitar esos cambios.

Con la combinación de ambos tipos de trigger podemos conseguir una auditoria bastante completa para saber quién se conecta, cuando lo hace, que objetos modifica e incluso que registros ha modificado y/o guardar una copia del registro anterior si hablamos de una tabla sensible.

Primero empezamos por hacer un seguimiento de los cambios básico a nivel de tabla. Para ello podemos usar los triggers que se lanzan a causa de consultas DML (insert, update) para hacer tracking de quien inserta o modifica cada registro. Luego elegiremos que tipo de trigger usaremos, el que se desencadena posteriormente al evento (AFTER, equivalente a FOR de versiones anteriores) o el que desencadena en lugar de la acción (INSTEAD OF). Tanto en un tipo de trigger como en el otro, podemos hacer referencia a los nuevos valores mediante una tabla inserted u otra tabla deleted para acceder a los registros anteriores a la modificación.

Vamos con un primer ejemplo donde queremos hacer tracking de usuarios de creación y el de la última modificación, además de fechas, en la misma tabla donde están los datos y queremos hacerlo de forma sencilla. Partimos de una tabla básica de empleados, a la que le añadimos los campos de auditoría:

-- Partimos de una tabla de empleados

CREATE TABLE [dbo].[EmpTable](
  [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](100) NOT NULL,
  [JobDescription] [nvarchar](30) NULL,
  PRIMARY KEY (EmployeeID) ON [PRIMARY])

-- Añadimos los campos básicos de auditoría (RowCreator, RowCreationDate, RowModifier, RowModifiedDate)

ALTER TABLE [dbo].[EmpTable] ADD
  RowCreator [nvarchar](20),
  RowCreationDate datetime,
  RowModifier [nvarchar](20),
  RowModifiedDate datetime

-- Creamos el trigger primero para alimentar los campos de auditoria en la inserción de nuevos registros.

ALTER TRIGGER EmpTable_InstOfInsert ON [EmpTable] 
INSTEAD OF INSERT
AS

BEGIN

SET NOCOUNT ON

DECLARE @User nvarchar(30)

SELECT @User = SUSER_NAME()

INSERT INTO [EmpTable] (EmployeeID, Name, JobDescription, RowCreator, RowCreationDate, RowModififer, RowModifiedDate)
   SELECT i.EmployeeID, i.Name, i.JobDescription, @User, GETDATE(), @User, GETDATE()
   FROM inserted i
END

Ahora creamos el trigger de actualización. A diferencia del trigger de inserción, al hacer UPDATE es posible qu estemos actualizando más de un registro a la vez de la misma tabla por una misma sentencia. Por ello deberemos usar un cursor para recorrer cada uno que se encuentre en la tabla "inserted" para ir alimentando los datos de auditoría en la tabla original.

-- Creamos el trigger para la actualización

CREATE TRIGGER EmpTable_AfterUpdate ON EmpTable
AFTER UPDATE
AS

BEGIN

SET NOCOUNT ON

DECLARE @User nvarchar(30)
DECLARE @SysDate datetime

DECLARE cInserted CURSOR FOR SELECT EmployeeID FROM inserted;
DECLARE @ID int

SELECT @SysDate = GETDATE()
SELECT @User = SUSER_NAME()

OPEN cInserted

FETCH NEXT FROM cInserted
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE EmpTable
SET RowModififer = @User, RowModifiedDate = @SysDate
WHERE ID = @ID

FETCH NEXT FROM cInserted
INTO @ID
END

CLOSE cInserted
DEALLOCATE cInserted

END

En el ejemplo anterior, tanto para la actualización como para la inserción estamos capturando solo los últimos cambios y solo datos de auditoría. En otro ámbito o tipo de datos, además nos puede interesar guardar en otra tabla anexa los antiguos valores del registro modificado (Imaginaros una tabla sensible con datos de configuración de la que queremos guardar el historial de cambios). Lo podemos conseguir con un trigger similar al siguiente:

-- Esta sería una posible estructura de la tabla original de configuración
CREATE TABLE [dbo].[ConfigurationTable](
  [KeyID] [int] IDENTITY(1,1) NOT NULL,
  [ParameterCode] [nvarchar](30)NOT NULL,
  [ParameterDescription] [nvarchar](100) NULL,
  [ParameterValue] [nvarchar](30) NULL
  PRIMARY KEY (ParameterCode) ON [PRIMARY])

-- Nosotros querríamos saber el historial de cambios. Crearíamos otra tabla con una estrucutra similar...
CREATE TABLE [dbo].[ConfigurationTable_OldValues](
  [KeyID] [int] NOT NULL,
  [ParameterCode] [nvarchar](30)NOT NULL,
  [ParameterDescription] [nvarchar](100) NULL,
  [NewParameterValue] [nvarchar](30) NULL,
  [OldParameterValue] [nvarchar](30) NULL,
  [RowModifier] [nvarchar](20),
  [RowModifiedDate] datetime)

-- Creamos el trigger para controlar la actualización de la tabla original e insertar los datos en la tabla de valores históricos ([ConfigurationTable_OldValues])

CREATE TRIGGER ConfigurationTable_AfterUpdate ON ConfigurationTable
AFTER UPDATE
AS

BEGIN

SET NOCOUNT ON

DECLARE @User nvarchar(30)
DECLARE @SysDate datetime

DECLARE cInserted CURSOR FOR SELECT [KeyID], [ParameterCode], [ParameterDescription], [ParameterValue] FROM inserted
DECLARE @keyId int
DECLARE @parametercode [nvarchar](30)
DECLARE @parameterdescription [nvarchar](100)
DECLARE @parametervalue [nvarchar](30)

SELECT @SysDate = GETDATE()
SELECT @User = SUSER_NAME()

OPEN cInserted
FETCH NEXT FROM cInserted
INTO @keyId , @parametercode , @parameterdescription , @parametervalue

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO [ConfigurationTable_OldValues]
  ([KeyID],
  [ParameterCode],
  [ParameterDescription],
  [NewParameterValue],
  [OldParameterValue] ,
  [RowModifier] ,
  [RowModifiedDate]
  )
  SELECT
  @keyId,
  @parametercode,
  @parameterdescription,
  @parametervalue,
  d.[ParameterValue] ,
  @User,
  @sysdate
  FROM deleted d WHERE d.[ParameterCode] = @parametercode

FETCH NEXT FROM cInserted
INTO @keyId , @parametercode , @parameterdescription , @parametervalue
END

CLOSE cInserted

DEALLOCATE cInserted

END

-- Hacemos un insert de prueba y un update para ver los resultados

INSERT INTO [dbo].[ConfigurationTable]
  ([ParameterCode]
  ,[ParameterDescription]
  ,[ParameterValue])
VALUES
  ('Processes'
  ,'Numero de procesos'
  ,'10'
  )
GO

UPDATE [dbo].[ConfigurationTable]
SET [ParameterValue] = '20'
WHERE [ParameterCode] = 'Processes'
GO

Si probamos todo el código anterior y consultamos la última tabla veremos el "tracking" del cambio de valor para el registro. Podríamos usar un trigger similar para guardar datos de registros borrados e insertarlos en otra tabla. Por último, un par de comentarios a tener en cuenta a nivel general cuando trabajamos con triggers:

• Se definen sobre una tabla especifica. Un trigger no sirve para dos o más tablas (aunque tengan el mismo código, debemos crear uno por cada una de las tablas...).
• El trigger se crea en la base de datos que de trabajo pero desde un trigger puedes hacer referencia a otras bases de datos.
• Un Trigger devuelve resultados al programa (ouput, mensaje de "filas afectadas") como lo puede hacer un procedure. Para impedir outputs no deseados utilizamos la sentencia SET NOCOUNT al principio del trigger.

En resumen...

... esta es una manera de hacer auditoría a medida para controlar los cambios en los datos de tabla. Nos puede ir bien hacerlo con las que contienen datos sensibles. Es posible que cuando queramos hacerlo, las tablas que queremos controlar sean de una aplicación de terceros y que la compatibilidad no sea al 100% ya que si añadimos campos provocaremos errores en sus sentencias SQL y eso nos impide modificar sus tablas. En ese caso la manera de hacerlo es la segunda, es decir, otra tabla donde guardamos cambios o solo ids. porque de alguna manera no altera la estructura, sino que informamos una tabla anexa a medida con los cambios que queremos guardar, ya sea solo auditoria o valores antiguos.