[Badillo]

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.

El motor de Base de datos es SQL Server.

Código SQL [-]
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

Gracias de antemano.