Cómo puedo solucionar este trigger en SQLServer

Question

Saludos, necesito ayuda con este trigger que su objetivo sería guardar una auditoría de los datos de la tabla paises, donde quisiera guardar en el formato NombreColumna:-:Valor::-::NombreColumna:-:Valor, de todas las columnas que su DATA_TYPE sea ('INT', 'VARCHAR', 'CHAR').

Cualquier ayuda o sugerencia la agradecería, estoy tratando de hacer el insert primero, la sería para las tres accciones, en el update solo guardaría las columnas que fueron modificadas, no todas.  


CREATE TRIGGER TR_Audit_Paises ON dbo.Paises
    FOR INSERT, UPDATE, DELETE
AS
    DECLARE @SysUser	varchar(100)
    DECLARE @FullName	varchar(250)
    DECLARE @TableName  varchar(250)
    DECLARE @Action     varchar(50)	
	DECLARE @OldValue	varchar(Max)
	DECLARE @NewValue	varchar(Max)
	DECLARE @COLUMN_NAME AS varchar(100)
	DECLARE @DATA_TYPE AS varchar(100)
	DECLARE @SQLTEXT AS nvarchar(Max)
	DECLARE @SEPARATOR AS VARCHAR(5)
	DECLARE @SEPARATORCOLUMN AS VARCHAR(5)
	DECLARE @COLUMN_VALUE AS nvarchar(Max)
	DECLARE @DATAFULLTEXT AS NVARCHAR(Max) 
	
	SET @TableName = 'Paises';

	DECLARE ColumnInfo CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='paises' AND DATA_TYPE IN ('INT', 'VARCHAR', 'CHAR') ORDER BY ORDINAL_POSITION

	SET @SQLTEXT = ''
	SET @SEPARATOR = ':::'
	SET @SEPARATORCOLUMN = '::::'
	SET @DATAFULLTEXT = ''
	
    IF EXISTS ( SELECT 0 FROM Deleted )
        BEGIN
            IF EXISTS ( SELECT 0 FROM Inserted )
                BEGIN
                    --UPDATE
					SET @OldValue = ''
                END
            ELSE
                BEGIN
                    --DELETE
					SET @OldValue = ''
                END  
        END
    ELSE
        BEGIN
            --INSERT
			SELECT @SysUser = I.LastUserUpdate FROM Inserted I;
			IF @SysUser <> '' 
				BEGIN
					SELECT @FullName = FullName FROM dbo.Users WHERE UserName = @SysUser	
				END
			ELSE
				BEGIN
					SELECT  @SysUser = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
					SET @FullName = 'Admin for console'	
				END

			OPEN ColumnInfo
			FETCH NEXT FROM ColumnInfo INTO @COLUMN_NAME, @DATA_TYPE
			WHILE @@fetch_status = 0
			BEGIN
				SET @SQLTEXT = 'DECLARE @SEPARATOR AS VARCHAR(5);';
				SET @SQLTEXT = @SQLTEXT + 'DECLARE @SEPARATORCOLUMN AS VARCHAR(5);';
				SET @SQLTEXT = @SQLTEXT + 'DECLARE @COLUMN_VALUE AS nvarchar(Max);';
				SET @SQLTEXT = @SQLTEXT + 'DECLARE @DATAFULLTEXT AS NVARCHAR(Max);';

				SET @SQLTEXT = @SQLTEXT + 'SET @SEPARATOR = '':-:'';';	
				SET @SQLTEXT = @SQLTEXT + 'SET @SEPARATORCOLUMN = ''::-::'';';
				
				SET @SQLTEXT = CONCAT(@SQLTEXT, N'SELECT @COLUMN_VALUE = Coalesce(CAST( ', @COLUMN_NAME, ' AS VARCHAR(Max)), '''') FROM Inserted WHERE id = 1 ;');
				SET @SQLTEXT = CONCAT(@SQLTEXT, N' SET @DATAFULLTEXT = @DATAFULLTEXT + @SEPARATORCOLUMN +''', @COLUMN_NAME,''' + @SEPARATOR + @COLUMN_VALUE;');

				EXECUTE sp_executesql @SQLTEXT;
				
				FETCH NEXT FROM ColumnInfo INTO @COLUMN_NAME, @DATA_TYPE
			END
			CLOSE ColumnInfo
			DEALLOCATE ColumnInfo

			INSERT  INTO dbo.AuditLogs ( SysUser, FullName, TableName, Action, OldValue, NewValue)
                    SELECT  @SysUser, @FullName, @TableName, 'INSERT', '', @DATAFULLTEXT FROM Inserted
        END
GO

Answer 1

Creo que existen formas mucho mejores de hacer una auditoria de tabla, sin recurrir a lo que estás intentando implementar. Lo primero sería el versionado de tabla. Enlace Otra opción con triggers. Enlace Incluso si lo prefieres, utilizando json, sería tambien bastante interesante.

Create table dbo.Paises (id int, name varchar(100), size bigint, population int);
Create table dbo.AuditLogs (id int identity(1,1), dataInfo nvarchar(max), triggerName varchar(25));
go
Create or alter trigger paises_insert on dbo.Paises 
After insert
As
Begin

Declare @json nvarchar(max);
Set @json = (
	SELECT * FROM inserted 
		FOR JSON PATH, 
			ROOT('data'), 
			INCLUDE_NULL_VALUES
		)

Insert into dbo.AuditLogs(dataInfo, triggerName)
Select @json, 'paises_insert'

End
go

Y su ejecución Insert into dbo.paises (name, size, population) Values ('España',504000,48000000), ('Rumania',null,null); Luego lo puedes explotar, usando funciones json. Pero creo que eso es otro capítulo

Answer 2

Saludos, Javi Me ayudó mucho la idea de utilizar JSON, aunque no pude porque la versión de mi SQL no lo soporta, pero pude solucionar una parte importante del problema y es la de lograr obtener NombreCampo:Valor de toda la fila en una sola cadena.

        BEGIN
            --INSERT
			SELECT @SysUser = LastUserUpdate FROM Inserted;
			IF @SysUser <> '' 
				BEGIN
					SELECT @FullName = FullName FROM dbo.Users WHERE UserName = @SysUser	
				END
			ELSE
				BEGIN
					SELECT  @SysUser = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
					SET @FullName = 'Admin for console'	
				END

			SET @NewValue = REPLACE(REPLACE(REPLACE( (SELECT * FROM paises WHERE id=1 FOR XML AUTO), '/>', ''), '<paises ', ''),'" ' ,'": ' ); 

			INSERT INTO dbo.AuditLogs ( SysUser, FullName, TableName, Action, OldValue, NewValue)
                   VALUES ( @SysUser, @FullName, @TableName, 'INSERT', '', @NewValue );
        END

Ahora solo me faltaría obtener una consulta solo con los campos que fueron modificados, para el caso del UPDATE, si puede ayudarme con alguna idea se lo agradecería.