Iniciar sesiónCerrar
Correo:
Contraseña:
Entrar
Recordar sesión en este navegador
Iniciar sesiónIniciar sesiónCrear cuentaCrear cuenta
LWP » Foros » SQL » Ayuda con trigger para auditoría

SQL - Ayuda con trigger para auditoría

Volver
Nuevo Tema
<<>>
 
Vista:
sin imagen de perfil

Ayuda con trigger para auditoría

Publicado por Juan Carlos Badillo Goy (3 intervenciones) el 23/01/2024 15:16:42
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
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.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Ayuda con trigger para auditoría

Publicado por Juan Carlos Badillo Goy (3 intervenciones) el 30/01/2024 17:03:30
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.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
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
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias
Val: 2.542
Oro
Ha mantenido su posición en SQL (en relación al último mes)
Gráfica de SQL

Ayuda con trigger para auditoría

Publicado por Isaias (1921 intervenciones) el 31/01/2024 15:10:17
¿En que motor de base de datos estas trabajando?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Ayuda con trigger para auditoría

Publicado por Juan Carlos Badillo Goy (3 intervenciones) el 31/01/2024 15:45:04
Saludos,

El trigger es para trabajar en SqlServer 2014.

Gracias
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias
Val: 2.542
Oro
Ha mantenido su posición en SQL (en relación al último mes)
Gráfica de SQL

Ayuda con trigger para auditoría

Publicado por Isaias (1921 intervenciones) el 27/02/2024 02:46:33
SQL Server tiene su propio sistema de AUDITORIA, ¿que es lo que quieres auditar?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar