Club Delphi  
    FTP   CCD     Buscar   Trucos   Trabajo   Foros

Retroceder   Foros Club Delphi > Bases de datos > MS SQL Server
Registrarse FAQ Miembros Calendario Guía de estilo Temas de Hoy

Respuesta
 
Herramientas Buscar en Tema Desplegado
  #1  
Antiguo 23-01-2024
Badillo Badillo is offline
Miembro
 
Registrado: jun 2021
Posts: 43
Poder: 0
Badillo Va por buen camino
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.

Última edición por Badillo fecha: 23-01-2024 a las 16:36:35.
Responder Con Cita
  #2  
Antiguo 30-01-2024
Badillo Badillo is offline
Miembro
 
Registrado: jun 2021
Posts: 43
Poder: 0
Badillo Va por buen camino
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.
Responder Con Cita
Respuesta



Normas de Publicación
no Puedes crear nuevos temas
no Puedes responder a temas
no Puedes adjuntar archivos
no Puedes editar tus mensajes

El código vB está habilitado
Las caritas están habilitado
Código [IMG] está habilitado
Código HTML está deshabilitado
Saltar a Foro

Temas Similares
Tema Autor Foro Respuestas Último mensaje
Procedimiento Almacenado para Auditoria elrodrix SQL 6 06-05-2013 06:53:10
Trigger auditoria fjcg02 Firebird e Interbase 16 07-04-2011 23:47:03
Trigger dinámico para Auditoría de Tablas jwmoreira Firebird e Interbase 6 11-03-2010 23:53:07
ayuda con trigger josi SQL 1 27-03-2008 07:03:39
Ayuda con TRIGGER Programer Oracle 1 22-03-2004 06:45:16


La franja horaria es GMT +2. Ahora son las 01:11:24.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Traducción al castellano por el equipo de moderadores del Club Delphi
Copyright 1996-2007 Club Delphi