[Badillo]

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.