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.
23-01-2024
Temas Similares
Mode Lineal
