Ayuda con trigger para auditoría
 

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.

Modificaciones para el Insert y Delete, solo falta el Update
 

Saludos, he logrado realizar el trigger para los eventos Insert y Delete, pero aún no para el Update, pues solo necesito guardar las columnas que se han modificado realmente, no a todas.

Les dejo los cambios para cualquier aporte.

Código SQL [-]
IF Exists ( SELECT * FROM sysobjects WHERE Name = 'Paises' )
  DROP TABLE Paises
GO

CREATE TABLE Paises(
  [id] [int] IDENTITY(1,1) NOT NULL,
  [pais] [varchar](250) NOT NULL,
  [abreviatura] [char](10) NULL,
  [LastUserUpdate] [varchar](100) NULL,
 CONSTRAINT [PK40] PRIMARY KEY NONCLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

IF Exists ( SELECT * FROM sysobjects WHERE Name = 'AuditLogs' )
  DROP TABLE AuditLogs
GO

CREATE TABLE AuditLogs(
    id                int             IDENTITY(1,1),
    SysDate           datetime2(7)    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    SysUser           varchar(100)    NOT NULL,
    FullName      varchar(250)    NULL,
    TableName         varchar(250)    NULL,
    Action            varchar(250)    NULL,
  OldValue      text        NULL,
  NewValue      text        NULL,
    CONSTRAINT pk_AuditLogs PRIMARY KEY NONCLUSTERED (id)
)
go

IF Exists ( SELECT * FROM sysobjects WHERE Name = 'Users' )
  DROP TABLE Users
GO

CREATE TABLE Users(
  [id] [int] IDENTITY(1,1) NOT NULL,
  [UserName] [varchar](50) NOT NULL,
  [FullName] [varchar](250) NULL,
  [Password] [varchar](250) NULL,
 CONSTRAINT [pk_Users] PRIMARY KEY NONCLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

IF Exists( SELECT A.Name FROM SysObjects A WHERE A.Name = 'TR_Audit_Paises')
  DROP TRIGGER TR_Audit_Paises
GO

CREATE TRIGGER TR_Audit_Paises ON dbo.Paises
    FOR INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @SysUser  varchar(100);
    DECLARE @FullName  varchar(250);
    DECLARE @TableName  varchar(250);
    DECLARE @Action     varchar(50);  
  DECLARE @OldValue  nvarchar(Max);
  DECLARE @NewValue  nvarchar(Max);
  DECLARE @Count    INT;
  
  SET @TableName = 'Paises';
  SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN  
      IF EXISTS ( SELECT 0 FROM Deleted )
        BEGIN
          IF EXISTS ( SELECT 0 FROM Inserted )
            BEGIN
              --UPDATE
              SET @OldValue = ''
            END
          ELSE
            BEGIN
              --DELETE
              SELECT @Count = COUNT(*) FROM Deleted;

              IF ( @Count > 0 )
              BEGIN
                SELECT @SysUser = LastUserUpdate FROM Deleted;
                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

                SELECT * INTO #_TmpDeleted from Deleted;

                WHILE (Exists(SELECT * FROM #_TmpDeleted))
                BEGIN
                  SET @OldValue = REPLACE( (SELECT TOP(1) * FROM #_TmpDeleted FOR XML AUTO), '/>', '' );
                  SET @OldValue = SUBSTRING(@OldValue, CHARINDEX(' ', @OldValue) + 1, LEN(@OldValue));
                  SET @OldValue = REPLACE(@OldValue,'" ' ,'": ' ); 
                  SET @OldValue = SUBSTRING(@OldValue, 0, CHARINDEX('": LastUserUpdate="', @OldValue) + 1 );
          
                  INSERT  INTO dbo.AuditLogs ( SysUser, FullName, TableName, Action, OldValue, NewValue)
                      VALUES ( @SysUser, @FullName, @TableName, 'DELETE', @OldValue, '' );

                  DELETE TOP(1) FROM #_TmpDeleted;
                END
              END

            END  
        END
      ELSE
        BEGIN
          --INSERT
          SELECT @Count = COUNT(*) FROM Inserted;

          IF ( @Count > 0 )
          BEGIN
            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

            SELECT * INTO #_TmpInserted from Inserted;

            WHILE (Exists(SELECT * FROM #_TmpInserted))
            BEGIN
              SET @NewValue = REPLACE( (SELECT TOP(1) * FROM #_TmpInserted FOR XML AUTO), '/>', '' );
              SET @NewValue = SUBSTRING(@NewValue, CHARINDEX(' ', @NewValue) + 1, LEN(@NewValue));
              SET @NewValue = REPLACE(@NewValue,'" ' ,'": ' ); 
              SET @NewValue = SUBSTRING(@NewValue, 0, CHARINDEX('": LastUserUpdate="', @NewValue) + 1 );
          
              INSERT  INTO dbo.AuditLogs ( SysUser, FullName, TableName, Action, OldValue, NewValue)
                  VALUES ( @SysUser, @FullName, @TableName, 'INSERT', '', @NewValue );

              DELETE TOP(1) FROM #_TmpInserted;
            END

          END
        END
        COMMIT TRAN;
 
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN; 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
   
        SELECT  
            @ErrorMessage = ERROR_MESSAGE(),  
            @ErrorSeverity = ERROR_SEVERITY(),  
            @ErrorState = ERROR_STATE();  
   
        RAISERROR (@ErrorMessage,
                   @ErrorSeverity,
                   @ErrorState 
                   );    
    END CATCH
END  
GO



INSERT INTO dbo.Users (UserName, FullName, Password) VALUES ('User1', 'User test 1', '*111*')
INSERT INTO dbo.Users (UserName, FullName, Password) VALUES ('User2', 'User test 2', '*111*')
GO

select * from paises
delete from paises
select * from AuditLogs
delete from AuditLogs

insert into paises (pais, abreviatura, LastUserUpdate) values ('Mexico', 'Mex', 'User1');
insert into paises (pais, abreviatura, LastUserUpdate) values ('China', 'Chi', 'User3');
select * from paises
select * from AuditLogs

insert into paises (pais, abreviatura, LastUserUpdate) values ('Ecuador', 'ECU', 'User4');
insert into paises (pais, abreviatura, LastUserUpdate) values ('Brasil', 'BRA', 'User2');
insert into paises (pais, abreviatura, LastUserUpdate) values ('España', 'ESP', 'User2');
select * from paises
select * from AuditLogs

delete from paises where id = 100;
select * from paises
select * from AuditLogs

delete from paises where abreviatura IN ('ECU', 'BRA');
select * from paises
select * from AuditLogs

delete from paises where abreviatura = 'CHI';
select * from paises
select * from AuditLogs

Select * into #_paises from paises;
select * from #_paises

Insert into paises (pais, abreviatura, LastUserUpdate) 
  select pais, abreviatura, LastUserUpdate from #_paises;

DROP TABLE #_paises
select * from paises
select * from AuditLogs

Gracias.