SQL Server: Auditoría de datos personalizada mediante triggers
SQL Server: Auditoría de datos personalizada mediante triggers il_masacratore 31 Marzo, 2014 - 13:08Los 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.
Muy interesante el articulo,
Subido por Anonimo (no verificado) el 23 Abril, 2015 - 00:47
El código tiene algunos
Subido por Anonimo2 (no verificado) el 30 Junio, 2017 - 04:35
El código tiene algunos detalles, ya lo corregí
Agradezco el tiempo que se
Subido por webJose (no verificado) el 8 Marzo, 2018 - 09:27
Agradezco el tiempo que se toma para enseñar a otros y admito que solamente ojeé muy ligeramente el código aquí presentado, pero debo hacer notar que el uso de cursores en esos triggers es muy innecesario. Además parece no concordar con los nombres de campos. En una sentencia la clave primaria de la tabla es EmployeeID y en otra es ID. Update EmpTable Set RowModifier = SUSER_NAME() , RowModifiedDate = GETUTCDATE() From EmpTable As e Inner Join inserted As i On e.EmployeeID = i.EmployeeID ; Y listo. Todos en un único UPDATE. Cero cursores.