SQL Server - Cómo crear un historial de cambios de datos para sus tablas (logs de auditoría)

Hola, chicos,
¡Buen día!

En la publicación de hoy, demostraré cómo crear un historial de cambios de datos, ya sea mediante INSERTAR, ELIMINAR o ACTUALIZAR en una tabla, para que pueda saber quién es el usuario y cuándo se cambió la información y cuál era la información en ese momento. Esto es muy útil para auditorías o informes de BI que necesitan obtener el escenario actual, por ejemplo, el salario del cliente en el momento en que compró un producto, hace 5 años.

Para esta operación crearé una tabla “clon” de la tabla original, pero con fecha de actualización, información del usuario que realizó el cambio, nombre de host, operación realizada y crearé un disparador en la tabla, para que cada operación DML registre un registro en la tabla de historial.

¿Crear historial usando Trigger o mediante el Sistema?

Antes de presentar esta solución, me gustaría demostrar algunos puntos de vista sobre la creación de activadores en tablas para este propósito. Durante mucho tiempo, estuve totalmente en contra de crear disparadores en la base de datos, porque pensé que esto los haría menos eficientes, una actualización o eliminación muy grande podría generar bloqueos en producción, etc. y pensé que era el desarrollador quien debería preocuparse, a través del código fuente del sistema, con esta parte de los registros y no crear disparadores en la base de datos.

Esto cambió cuando comencé a ver el lado del programador y a pensar en la trazabilidad de la información, y cuánto puede ayudar esto a otros sectores de una empresa, como Auditoría, BI y Contraloría. Hoy en día, con la introducción de la filosofía DevOPS, el DBA no puede pensar dentro de la caja negra, centrándose únicamente en la disponibilidad de la base de datos. Es necesario centrarse en el negocio y aportar todo el valor que TI pueda a la empresa, pero por supuesto, siempre en base a lo posible, sin comprometer la disponibilidad de los recursos TI.

Teniendo esto en cuenta, enumeraré las diferencias entre crear este historial de datos usando un disparador o vía sistema:

Generando historial mediante trigger en la base de datos:

• Una vez desarrollado, implementar el recurso solo implica crear una tabla y un disparador en la base de datos.
• No importa qué rutina o usuario esté manipulando la tabla, todos los cambios siempre quedarán registrados.
• Las ACTUALIZAR, INSERTAR y ELIMINAR realizadas manualmente en la base de datos serán registradas y auditadas por el activador, y se generará un historial para esto.
• Tanto el DBA como el Desarrollador tienen visibilidad de la existencia de la rutina y su código fuente.
• Si es necesario desactivar temporalmente el disparador para alguna operación, el DBA puede hacerlo en unos segundos.
• La gestión de la rutina de auditoría está en manos del DBA
• Si la tabla sufre un gran cambio de datos manual, ya sea mediante INSERTAR, ELIMINAR o ACTUALIZAR, todos los cambios se escribirán en la tabla de historial, lo que puede generar un volumen muy grande de escrituras en la tabla de historial y provocar lentitud en el entorno. Esto se puede solucionar desactivando el disparador mientras se realizan estos cambios masivos y activándolo nuevamente cuando termine.
• Si el cambio lo realiza el sistema y el sistema utiliza un usuario fijo, el activador registrará el usuario del sistema y no el usuario de la persona que realizó el cambio.

Generando historial a través del sistema:

• La implementación implica realizar cambios en el código fuente de todos los fragmentos de código de la aplicación y pantallas que manipulan datos en la tabla involucrada (además de los archivos dependientes), donde generalmente existen ventanas rígidas para cualquier modificación en el sistema.
• Sólo las pantallas que se hayan modificado para registrar el historial lo harán de forma efectiva.
• Las ACTUALIZAR, INSERTAR y ELIMINAR realizadas manualmente en la base de datos NO se registrarán y no habrá historial de estos cambios.
• Sólo el desarrollador sabe que esta característica existe y cómo funciona. El DBA generalmente no tiene acceso a este tipo de información y mucho menos al código fuente para entender cómo se está generando este historial.
• Si es necesario desactivar temporalmente esta característica, el desarrollador tendrá que cambiar el código fuente de la aplicación y desplegarla en producción, consumiendo mucho tiempo para dos equipos y con la posibilidad de desconectar sesiones activas en el servidor de la aplicación.
• La gestión de la rutina de auditoría está en manos del Desarrollador
• Si la tabla sufre un cambio manual importante en los datos, ya sea mediante INSERTAR, ELIMINAR o ACTUALIZAR, el entorno no se verá afectado, ya que los cambios manuales en la base de datos no se registrarán.
• Si el cambio lo realiza el sistema, es posible identificar al usuario que inició sesión en la aplicación y registrar el inicio de sesión o incluso realizar consultas en la base de datos y devolver un User_Id de la tabla Usuarios, por ejemplo, para registrar en la tabla de historial.

Como observó en los elementos mencionados anteriormente, cada uno de los enfoques tiene ventajas y desventajas. Por lo tanto, debes decidir cuál se adapta mejor a tu negocio e infraestructura.

Creando el proceso de auditoría

Para las pruebas de activación, crearé una tabla de clientes e insertaré algunos datos para ver el registro funcionando:

IF (OBJECT_ID('Testes.dbo.Cliente') IS NOT NULL) DROP TABLE Testes.dbo.Cliente
CREATE TABLE Testes.dbo.Cliente (
    Id_Cliente INT IDENTITY(1, 1),
    Nome VARCHAR(100),
    Data_Nascimento DATETIME,
    Salario FLOAT
)

INSERT INTO Testes.dbo.Cliente
VALUES 
    ('João', '1981-05-14', 4521),
    ('Marcos', '1975-01-07', 1478.58),
    ('André', '1962-11-11', 7151.45),
    ('Simão', '1991-12-18', 2584.97),
    ('Pedro', '1986-11-20', 987.52),
    ('Paulo', '1974-08-04', 6259.14),
    ('José', '1979-09-01', 5272.13)


SELECT * FROM Testes.dbo.Cliente

Finalmente, es hora de crear nuestra auditoría:

-- Criando a tabela com a mesma estrutura da original, mas adicionando colunas de controle
IF (OBJECT_ID('Testes.dbo.Cliente_Log') IS NOT NULL) DROP TABLE Testes.dbo.Cliente_Log
CREATE TABLE Testes.dbo.Cliente_Log (
    Id INT IDENTITY(1, 1),
    Dt_Atualizacao DATETIME DEFAULT GETDATE(),
    [Login] VARCHAR(100),
    Hostname VARCHAR(100),
    Operacao VARCHAR(20),

    -- Dados da tabela original
    Id_Cliente INT,
    Nome VARCHAR(100),
    Data_Nascimento DATETIME,
    Salario FLOAT
)
GO

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('Testes.dbo.Cliente')) > 0) DROP TRIGGER trgHistorico_Cliente
GO

CREATE TRIGGER trgHistorico_Cliente ON Testes.dbo.Cliente -- Tabela que a trigger será associada
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    
    SET NOCOUNT ON

    DECLARE 
        @Login VARCHAR(100) = SYSTEM_USER, 
        @HostName VARCHAR(100) = HOST_NAME(),
        @Data DATETIME = GETDATE()
        

    IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
    BEGIN
        
        INSERT INTO Testes.dbo.Cliente_Log
        SELECT @Data, @Login, @HostName, 'UPDATE', *
        FROM Inserted

    END
    ELSE BEGIN

        IF (EXISTS(SELECT * FROM Inserted))
        BEGIN

            INSERT INTO Testes.dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'INSERT', *
            FROM Inserted

        END
        ELSE BEGIN

            INSERT INTO Testes.dbo.Cliente_Log
            SELECT @Data, @Login, @HostName, 'DELETE', *
            FROM Deleted

        END

    END

END
GO

Y ahora simulemos algunos cambios en la base:

INSERT INTO Testes.dbo.Cliente
VALUES ('Bartolomeu', '1975-05-28', 6158.74)

UPDATE Testes.dbo.Cliente
SET Salario = Salario * 1.5
WHERE Nome = 'Bartolomeu'

DELETE FROM Testes.dbo.Cliente
WHERE Nome = 'André'

UPDATE Testes.dbo.Cliente
SET Salario = Salario * 1.1
WHERE Id_Cliente = 2

UPDATE Testes.dbo.Cliente
SET Salario = 10, Nome = 'Judas Iscariodes', Data_Nascimento = '06/06/2066'
WHERE Id_Cliente = 1

Como puedes ver, estoy usando el objeto Insertado en la parte que maneja la ACTUALIZACIÓN. Con esto registro la nueva información en la tabla del historial, la cual se va actualizando mediante el comando ACTUALIZAR. Si desea registrar los valores antiguos, que se están reemplazando, simplemente cambie el activador para usar el objeto Eliminado.

Cambios en la tabla original.

Si necesita incluir más columnas en su tabla original, recuerde cambiar también la tabla de historial de su activador, como usé * al insertar los datos para que no tenga que escribir el nombre de las columnas. Una alternativa a esto es eliminar el * del activador y especificar manualmente el nombre de todas las columnas, lo cual no supone ningún problema.

Simulando que cambias tu tabla original, sin cambiar la tabla del historial, tenemos esta situación:

Donde se generó el mensaje de error:

Msg 8101, Nivel 16, Estado 1, Trámite trgHistorico_Cliente, Línea 17
Solo se puede especificar un valor explícito para la columna de identidad en la tabla 'Testes.dbo.Cliente_Log' cuando se utiliza una lista de columnas y IDENTITY_INSERT está activado.

Para solucionar este problema, simplemente agregue el mismo cambio de estructura que realizó a la tabla de registro:

¡Y eso es todo amigos!
Gracias por visitarnos y si tienes alguna duda déjala aquí en los comentarios.

¡Abrazo!