Crear un Log de Transacciones en SQL Server

Cuando Necesitamos realizar una auditoría de datos en alguna base de datos en sql server es necesario tener un registro
sin embargo cuando se utiliza la versión express del SQL Server no se cuenta con las herramientas para realizar esta tarea comparto una forma alternativa para realizar un registro de cambios realizados en una base de datos, para que este proceso se realice de forma automática es necesario crear una base de datos la cual le podemos llamar Auditoría y crear en ella una tabla para registrar los cambios en la bases de datos que necesitemos guardar registro de los cambios, el script para crear la tabla es el siguiente:

USE [Auditoria]
GO
/****** Object: Table [dbo].[LogTransacciones] Script Date: 17/11/2019 07:35:36 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LogTransacciones](
[TipoTrn] char NULL,
[TranID] varchar NULL,
[Tabla] varchar NULL,
[PK] varchar NULL,
[Campo] varchar NULL,
[ValorOriginal] varchar NULL,
[ValorNuevo] varchar NULL,
[FechaTrn] varchar NULL,
[Usuario] varchar NULL,
[Base] varchar NULL
) ON [PRIMARY]
GO

Esta tabla contiene columnas para almacenar el tipo de transacción que se realizará (insertar, actualizar, eliminar), el número de transacción el cual asigno el manejador, la tabla que se actualizo, la llave primaria, el campo afectado de la tabla afectada, valor anterior, valor nuevo, fecha de la transacción, usuario que realizó la transacción y nombre de la base de datos.

El siguiente paso es crear un trigger en las tablas las cuales se necesite registrar cambios en los datos, el trigger esta modificado para ser generico, guarda de forma dinamica el nombre de la base, el nombre del usuario el cual realizó la transacción y el número de transacción asignado por el manejador, este trigger está basado en varios encontrados en la red, sin embargo esta modificado para tablas mayores a 128 columnas el cual es un error en la mayoría, el siguiente trigger elimina esta limitación además que puede utilizar en diferentes bases y puede leer información transferida cuando se realiza un transacción desde alguna aplicación de .NET, a continuación el trigger.

USE [MiBase]
GO
/****** Object: Trigger [dbo].[Audit] Script Date: 17/11/2019 07:55:52 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Audit] ON [dbo].[MiTabla] FOR INSERT, UPDATE, DELETE
AS

DECLARE @bit int ,
@field int ,
@maxfield int ,
@char int ,
@Base varchar(50),
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKSELECT varchar(1000),
@TranName varchar(36),
@TranID varchar(36),
@TableID int

--Identificamos el nombre de la tabla que se afecto
SELECT @TableID = parent_id FROM sys.triggers WHERE object_id = @@PROCID
SELECT @TableName = OBJECT_NAME(@TableID)

--Obtenemos el nombre de la base afectada
Set @Base = DB_NAME()

--obtenemos el número de transacción realizada
select @TranID = transaction_id FROM sys.dm_tran_current_transaction

--Obtenemos el nombre de usuario el cual se obtiene pasando desde nuestra aplicación en el parámetro
--del nombre de la transacción
SELECT @TranName = [Transaction Name] FROM fn_dblog(NULL, NULL) WHERE [Xact ID] = @TranID
AND [Operation] = 'LOP_BEGIN_XACT'

-- Fecha y Usuario
SELECT @UserName = LOWER(@TranName),
@UpdateDate = convert(varchar(8), getdate(), 112) +
' ' +
convert(varchar(12), getdate(), 114)

SET NoCount ON

-- Identificar que evento se está ejecutando (Insert, Update o Delete)
--en base a cursores especiales (inserted y deleted)
if exists (SELECT * FROM inserted)
if exists (SELECT * FROM deleted) --Si es un update
SELECT @Type = 'U'
else --Si es un insert
SELECT @Type = 'I'
else --si es un delete
SELECT @Type = 'D'

-- Obtenemos la lista de columnas de los cursores
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

-- Obtener las columnas de llave primaria
SELECT @PKCols = coalesce(@PKCols + ' and', ' on') +
' i.' +
c.COLUMN_NAME + ' = d.' +
c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
WHERE pk.TABLE_NAME = @TableName AND
pk.CONSTRAINT_TYPE = 'PRIMARY KEY'

-- Obtener la llave primaria y columnas para la inserción en la tabla de auditoria
SELECT
@PKSELECT = coalesce(@PKSelect+'+','') +
'''<' +
COLUMN_NAME +
'=''+convert(varchar(100),coalesce(i.' +
COLUMN_NAME +',d.' +
COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'

if @PKCols is null --<-- Este trigger solo funciona si la tabla tiene llave primaria
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END

--Loop para armar el query de inserción en la tabla de log.
--Un registro por cada campo afectado.
set @field = 0

while @field < (select max(colid) from syscolumns where id = (select id from sysobjects where name = @TableName))
BEGIN
SELECT @field = @field + 1
SELECT @bit = (@field - 1) % 8 + 1
SELECT @bit = power(2, (@bit - 1))
SELECT @char = ((@field - 1) / 8) + 1

if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
BEGIN
select @fieldName = name from syscolumns
where colid = @field and id = (select id from sysobjects where name = @TableName)

SELECT @sql = 'insert Auditoria.dbo.LogTransacciones (TipoTrn, TranID, Tabla, PK, Campo, ValorOriginal, ValorNuevo, >FechaTrn, Usuario,Base)'
SELECT @sql = @sql + ' SELECT ''' + @Type + ''''
SELECT @sql = @sql + ',''' + @TranID + ''''
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',' + @PKSelect
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @Base + ''''
SELECT @sql = @sql + ' from #ins i full outer join #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
exec (@sql)

END
END

SET NoCount OFF

Con este trigger cualquier cambio en la tablas donde se utilice, para obtener el nombre de las transacciones de donde se obtiene el nombre de usuario, es necesario otorgar permisos al usuario en la función fn_dblog de la base master, lo cual se hace de la siguiente manera:

use master;
GRANT SELECT ON FN_DBLOG TO MiUser;

Siguiendo estos pasos ya podemos registrar cambios realizados en las tablas de las bases que necesitemos.