sexta-feira, 17 de julho de 2015

SQL Server e o uso de Gatilhos para auditoria de dados




Neste mês resolvi escrever um pouco sobre o assunto Auditoria de dados utilizando os denominados Gatinhos (Triggers) no Banco de Dados SQL Server. Explicarei e criarei toda a regra necessária para esta tarefa. Farei o uso da versão 2008 do SQL Server Express Edition, servindo também de base para as outras versões. Utilizaremos também como ferramenta para gerenciamento de dados o “Microsoft SQL Management Studio”, versão 10. Antes de começarmos a implementar a ideia de auditoria de dados em tabelas do SQL Server, o tópico abaixo “Pré-requisitos” irá abranger um assunto considerado primordial para a leitura deste artigo, ressaltando que este tópico já se encontra publicado em um de nossos artigos.

Pré-Requisitos

Este tópico foi baseado no artigo “Trabalhando com o SQL Server 2008 Express Edition – Parte 2” do mês de Abril de 2011, para maiores detalhes favor realizar a leitura do artigo na íntegra.

O que seria um Gatilho?

O gatilho é um objeto que é automaticamente executado assim que efetuado um INSERT, DELETE ou UPDATE na tabela. Podemos criá-los de duas maneiras, BEFORE (antes) e AFTER (após). Os gatilhos BEFORE disparam antes das modificações da instrução serem aplicadas, e antes de qualquer restrição ser aplicada. Já os AFTER disparam após todas as restrições terem sido satisfeitas, e após todas as alterações terem sido aplicadas à tabela de destino.

Como implementar?

Uma das formas mais rápidas e práticas seria com o Microsoft SQL Management Studio. Com o mesmo aberto deveremos clicar na tabela desejada e escolher a Opção “Gatilho” e em seguida “Novo Gatilho”, Ver Imagem 01.

 
Figura 01: Criar um Gatilho.


Teremos abaixo o código base para criação das TRIGGERS e em seguida a explicação detalhada, Ver Listagem 01.

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
    
     SET NOCOUNT ON;
END
Listagem 01: Código para criação de Triggers.

Detalhando o código da Listagem 01.

CREATE TRIGGER <Nome do Gatilho a ser utilizado>
ON <Nome da Tabela afetada>
AFTER <Momento(Antes ou Depois) / da Operação (Inserir,Deletar ou Atualizar)>
AS
BEGIN   
            <Código a ser programado>
END

Como funcionará a auditoria dos dados em nossa tabela do SQL Server?

O exemplo se baseará na auditoria em uma tabela, por exemplo a de Clientes. Teremos todos os detalhes dos comandos de Inserção, Atualização e Exclusão dos registros. Nossos dados serão armazenados em mais outras duas tabelas, sendo a de LOGS e LOGS_DATA, um clássico relacionamento Mestre-Detalhe. Teremos os campos detalhados abaixo:

Tabela LOGS

ID_LOGS: campo auto-incremento (chave primária) para se relacionar com a tabela LOGS_DATA;
CODIGO: código do Cliente;
DATA_HORA: Data/Hora da operação;
OPERACAO: tipo de operação [(I)nclusão,(A)lteração, (E)xclusão];
USUARIOID: código do usuário.
Tabela LOGS_DATA
ID_LOGS_DATA: campo auto-incremento (chave primária);
ID_LOGS: chave estrangeira, campo resposnsável pelo relacionamento com a tabela LOGS;
NOME CAMPO: Nome do Campo da tabela de Clientes;
TIPO_CAMPO: Tipo de campo utilizado;
VALOR: Conteúdo do campo.
Para exemplificar melhor, veremos maiores detalhes do esquema na Imagem 02.


Os Comandos de
Insert, Update e Delete
serão executados com
base na tabela de
CLIENTES.

O resultado da execução dos Gatilhos serão armazenados nas tabelas LOGS e LOGS_DATA.

Figura 02: Esquema de funcionamento.

Definindo a Tabela Principal (CLIENTES)

O script de criação da tabela de clientes poderemos conferir na Listagem 02.

CREATE TABLE [dbo].[CLIENTES](
     [CODIGO] [int] IDENTITY(1,1) NOT NULL,
     [NOME] [varchar](50) NULL,
     [TIPO] [int] NULL,
     [CIDADE] [varchar](50) NULL,
     [UF] [varchar](2) NULL,
     [DOCUMENTO] [varchar](20) NULL,
     [DATANASCIMENTO] [date] NULL,
     [SEXO] [varchar](1) NULL,
     [TELEFONE] [varchar](20) NULL,
     [OBSERVACAO] [varchar](80) NULL,
     [USUARIOID] [int] NULL,
 CONSTRAINT [PK_CLIENTES] PRIMARY KEY CLUSTERED
(
     [CODIGO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Listagem 02: Script da tabela Clientes.

Definindo as Tabelas de Logs (LOGS e LOGS_DATA)

Para a tabela de Logs teremos o script de criação conforme Listagem 03.

CREATE TABLE [dbo].[LOGS](
     [ID_LOGS] [int] IDENTITY(1,1) NOT NULL,
     [CODIGO] [int] NULL,
     [DATA_HORA] [datetime] NULL,
     [OPERACAO] [varchar](1) NULL,
     [USUARIOID] [int] NULL,
 CONSTRAINT [PK_CADASTRO_UNICO_LOGS] PRIMARY KEY CLUSTERED
(
     [ID_LOGS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Listagem 03: Script da tabela Logs.

Já para a tabela Detalhe Logs_Data, o script responsável pela criação da tabela poderá ser consultado na Listagem 04.

CREATE TABLE [dbo].[LOGS_DATA](
     [ID_LOGS_DATA] [int] IDENTITY(1,1) NOT NULL,
     [ID_LOGS] [int] NULL,
     [NOME_CAMPO] [varchar](50) NULL,
     [TIPO_CAMPO] [varchar](50) NULL,
     [VALOR] [varchar](80) NULL,
 CONSTRAINT [PK_LOGS_DATA] PRIMARY KEY CLUSTERED
(
     [ID_LOGS_DATA] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
Listagem 04: Script da tabela Logs_Data.

Criando o Gatilho de “Insert”

O gatilho “INSERT_LOGS” fará sempre o trabalho após a inserção de algum registro, Ver código comentado na listagem 05.

CREATE TRIGGER [dbo].[INSERT_LOGS]
   ON  [dbo].[CLIENTES]
   AFTER INSERT
AS
BEGIN
      DECLARE @CODIGO INT
      DECLARE @NOME VARCHAR(50)
      DECLARE @TIPO INT
      DECLARE @CIDADE VARCHAR(50)
      DECLARE @UF VARCHAR(2)
      DECLARE @DOCUMENTO VARCHAR(20)
      DECLARE @DATANASCIMENTO DATE
      DECLARE @SEXO VARCHAR(1)
      DECLARE @TELEFONE varchar(20)
      DECLARE @OBSERVACAO varchar(80)
      DECLARE @USUARIOID int
      DECLARE @ID_LOGS int
                 
Para cada campo a ser inserido foi necessário criar uma variável.

      SELECT
      @CODIGO = CODIGO,
      @NOME = NOME,
      @TIPO = TIPO,
      @CIDADE = CIDADE,
      @UF = UF,
      @DOCUMENTO = DOCUMENTO,
      @DATANASCIMENTO = DATANASCIMENTO,
      @SEXO = SEXO,
      @TELEFONE = TELEFONE,
      @OBSERVACAO = OBSERVACAO,
      @USUARIOID = USUARIOID
      FROM Inserted

O próximo passo será de alimentar todas as variáveis com os registros inseridos na tabela de Clientes. Podemos realizar esta tarefa com o comando “Inserted”, o mesmo responsável pelo estado de inserção dos dados.

      INSERT INTO LOGS (CODIGO, DATA_HORA, OPERACAO, USUARIOID)
      VALUES
      (@CODIGO,GETDATE(),'I', @USUARIOID)

Para inserir na tabela “Mestre” denominada Logs faremos um simples INSERT, passando por parâmetro o código oriundo da variável declarada acima, a data atual utilizando o comando “GetDate()”, a operação “I” que significa modo de inserção e o usuário ativo no momento.


    SELECT @ID_LOGS = ID_LOGS FROM LOGS INSERTED

Já para inserirmos os dados na tabela “Detalhe” Logs_data o primeiro passo deveremos realizar um select usando o ID_LOGS para localizarmos o registro corrente, com o auxílio do comando “Inserted”.

    IF (@CODIGO IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'CODIGO','INT',@CODIGO)
      END

    IF (@NOME IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'NOME','VARCHAR',@NOME)
      END
     
      IF (@TIPO IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'TIPO','INT',@TIPO)
      END
     
      IF (@CIDADE IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'CIDADE','VARCHAR',@CIDADE)
      END
     
      IF (@UF IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'UF','VARCHAR',@UF)
      END
     
      IF (@DOCUMENTO IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'DOCUMENTO','VARCHAR',@DOCUMENTO)
      END
     
      IF (@DATANASCIMENTO IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'DATANASCIMENTO','DATE',@DATANASCIMENTO)
      END
     
      IF (@SEXO IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'SEXO','VARCHAR',@SEXO)
      END
     
      IF (@TELEFONE IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'TELEFONE','VARCHAR',@TELEFONE)
      END
     
      IF (@OBSERVACAO IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'OBSERVACAO','VARCHAR',@OBSERVACAO)
      END
     
      IF (@USUARIOID IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'USUARIOID','INT',@USUARIOID)
      END
END
Listagem 05: Gatilho Insert_Logs.

Nos próximos passos faremos uma verificação campo a campo se os mesmos não se encontram nulos e logo em seguida executaremos um Insert na tabela Logs_data, respeitando a ordem dos campos Id_Logs, Nome, Tipo e o Valor do campo.

Criando o Gatilho de “Update”

O gatilho “UPDATE_LOGS” fará sempre o trabalho após a atualização de algum registro, Ver código comentado na listagem 06.

CREATE TRIGGER [dbo].[UPDATE_LOGS]
   ON  [dbo].[CLIENTES]
   AFTER UPDATE
AS
BEGIN
      DECLARE @CODIGO INT
      DECLARE @NOME VARCHAR(50)
      DECLARE @TIPO INT
      DECLARE @CIDADE VARCHAR(50)
      DECLARE @UF VARCHAR(2)
      DECLARE @DOCUMENTO VARCHAR(20)
      DECLARE @DATANASCIMENTO DATE
      DECLARE @SEXO VARCHAR(1)
      DECLARE @TELEFONE varchar(20)
      DECLARE @OBSERVACAO varchar(80)
      DECLARE @USUARIOID int
      DECLARE @ID_LOGS int

Para cada campo a ser atualizado foi necessário criar uma variável.
     
      SELECT
      @CODIGO = ISNULL(CODIGO,0),
      @NOME = ISNULL(NOME,' '),
      @TIPO = ISNULL(TIPO,' '),
      @CIDADE = ISNULL(CIDADE,' '),
      @UF = ISNULL(UF,' '),
      @DOCUMENTO = ISNULL(DOCUMENTO,' '),
      @DATANASCIMENTO = ISNULL(DATANASCIMENTO,' '),
      @SEXO = ISNULL(SEXO,' '), 
      @TELEFONE = ISNULL(TELEFONE, ' '),
      @OBSERVACAO = ISNULL(OBSERVACAO,' '), 
      @USUARIOID = ISNULL(USUARIOID,' ')
      FROM INSERTED

O próximo passo será de alimentar todas as variáveis com os registros atualizados na tabela de Clientes. Podemos realizar esta tarefa com o comando “Inserted” e analisando se o campo está nulo com a função “ISNULL”.

     
      DECLARE @NOME_OLD VARCHAR(40)
      DECLARE @TIPO_OLD INT
      DECLARE @CIDADE_OLD VARCHAR(50)
      DECLARE @UF_OLD VARCHAR(2)
      DECLARE @DOCUMENTO_OLD VARCHAR(20)
      DECLARE @DATANASCIMENTO_OLD DATE
      DECLARE @SEXO_OLD VARCHAR(1)
      DECLARE @TELEFONE_OLD VARCHAR(20)
      DECLARE @OBSERVACAO_OLD VARCHAR(80)
      DECLARE @USUARIOID_OLD int

Nas variáveis acima deveremos armazenar todos os valores antigos dos campos que foram atualizados.
     
      SELECT
      @NOME_OLD = ISNULL(NOME,' '),
      @TIPO_OLD = ISNULL(TIPO,' '),
      @CIDADE_OLD = ISNULL(CIDADE,' '),
      @UF_OLD = ISNULL(UF,' '),
      @DOCUMENTO_OLD = ISNULL(DOCUMENTO,' '),
      @DATANASCIMENTO_OLD = ISNULL(DATANASCIMENTO,' '),
      @SEXO_OLD = ISNULL(SEXO,' '), 
      @TELEFONE_OLD = ISNULL(TELEFONE, ' '),
      @OBSERVACAO_OLD = ISNULL(OBSERVACAO,' '), 
      @USUARIOID_OLD = ISNULL(USUARIOID,' ')
      FROM DELETED
           
Poderemos recuperar estes campos utilizando o comando “Deleted”.
     

INSERT INTO LOGS (CODIGO, DATA_HORA, OPERACAO, USUARIOID)
VALUES
(@CODIGO,GETDATE(),'A', @USUARIOID)

Seguindo a mesma lógica descrita anteriormente, a inserção na tabela “Mestre” denominada Logs faremos um simples INSERT, passando por parâmetro o código oriundo da variável declarada acima, a data atual utilizando o comando “GetDate()”, a operação “A” que significa modo de atualização e o usuário ativo no momento.


    SELECT @ID_LOGS = ID_LOGS FROM LOGS INSERTED

Para inserirmos os dados na tabela “Detalhe” Logs_data o primeiro passo deveremos realizar um select usando o ID_LOGS para localizarmos o registro corrente, com o auxílio do comando “Inserted”.

  
    IF (UPDATE(NOME) AND (@NOME <> @NOME_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'NOME','VARCHAR',@NOME)
      END
     
      IF (UPDATE(TIPO) AND (@TIPO <> @TIPO_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'TIPO','INT',@TIPO)
      END
     
      IF (UPDATE(CIDADE) AND (@CIDADE <> @CIDADE_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'CIDADE','VARCHAR',@CIDADE)
      END
     
      IF (UPDATE(UF) AND (@UF <> @UF_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'UF','VARCHAR',@UF)
      END
     
      IF (UPDATE(DOCUMENTO) AND (@DOCUMENTO <> @DOCUMENTO_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'DOCUMENTO','VARCHAR',@DOCUMENTO)
      END
     
      IF (UPDATE(DATANASCIMENTO) AND (@DATANASCIMENTO <> @DATANASCIMENTO_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'DATANASCIMENTO','DATE',@DATANASCIMENTO)
      END
     
      IF (UPDATE(SEXO) AND (@SEXO <> @SEXO_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'SEXO','VARCHAR',@SEXO)
      END
     
      IF (UPDATE(TELEFONE) AND (@TELEFONE <> @TELEFONE_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'TELEFONE','VARCHAR',@TELEFONE)
      END
     
      IF (UPDATE(OBSERVACAO) AND (@OBSERVACAO <> @OBSERVACAO_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'OBSERVACAO','VARCHAR',@OBSERVACAO)
      END
     
      IF (UPDATE(USUARIOID) AND (@USUARIOID <> @USUARIOID_OLD))
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'USUARIOID','INT',@USUARIOID)
      END
END
Listagem 06: Gatilho Update_Logs.

Por final faremos uma verificação campo a campo se os mesmos estão em modo de Update e comparamos o valor antigo com o valor novo, caso encontre alguma divergência executaremos um Insert na tabela Logs_data, respeitando a ordem dos campos ID_LOGS, Nome, Tipo e o Valor do campo.

Criando o Gatilho de “Delete”

O gatilho “DELETE_LOGS” fará sempre o trabalho após a Exclusão de algum registro, Ver código comentado na listagem 07.

CREATE TRIGGER [dbo].[DELETE_LOGS]
   ON  [dbo].[CLIENTES]
   AFTER DELETE
AS
BEGIN
    DECLARE @ID_LOGS INT
    DECLARE @CODIGO_OLD INT
    DECLARE @NOME_OLD VARCHAR(40)
    DECLARE @TIPO_OLD INT
    DECLARE @CIDADE_OLD VARCHAR(50)
    DECLARE @UF_OLD VARCHAR(2)
    DECLARE @DOCUMENTO_OLD VARCHAR(20)
    DECLARE @DATANASCIMENTO_OLD DATE
    DECLARE @SEXO_OLD VARCHAR(1)
    DECLARE @TELEFONE_OLD VARCHAR(20)
    DECLARE @OBSERVACAO_OLD VARCHAR(80)
    DECLARE @USUARIOID_OLD int

Para cada campo a ser atualizado foi necessário criar uma variável.

     
      SELECT
      @CODIGO_OLD = CODIGO,
      @NOME_OLD = NOME,
      @TIPO_OLD = TIPO,
      @CIDADE_OLD = CIDADE,
      @UF_OLD = UF,
      @DOCUMENTO_OLD = DOCUMENTO,
      @DATANASCIMENTO_OLD = DATANASCIMENTO,
      @SEXO_OLD = SEXO,
      @TELEFONE_OLD = TELEFONE,
      @OBSERVACAO_OLD = OBSERVACAO,
      @USUARIOID_OLD = USUARIOID
      FROM DELETED
     
O próximo passo será de alimentar todas as variáveis com os registros excluídos na tabela de Clientes. Podemos realizar esta tarefa com o comando “Deleted”.

      INSERT INTO LOGS (CODIGO, DATA_HORA, OPERACAO, USUARIOID)
      VALUES
      (@CODIGO_OLD,GETDATE(),'E', @USUARIOID_OLD)

Novamente faremos a inserção na tabela “Mestre” com o comando INSERT, passando por parâmetro o código oriundo da variável declarada acima, a data atual utilizando o comando “GetDate()”, a operação “E” que significa modo de exclusão e o usuário ativo no momento.


    SELECT @ID_LOGS = ID_LOGS FROM LOGS INSERTED

Para a tabela “Detalhe” Logs_data deveremos realizar um select usando o ID_LOGS para localizarmos o registro corrente, com o auxílio do comando “Inserted”.

       
    IF (@CODIGO_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'CODIGO','INT',@CODIGO_OLD)
      END
     
      IF (@NOME_OLD  IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'NOME','VARCHAR',@NOME_OLD)
      END
     
      IF (@TIPO_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'TIPO','INT',@TIPO_OLD)
      END

    IF (@CIDADE_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'CIDADE','VARCHAR',@CIDADE_OLD)
      END

    IF (@UF_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'UF','VARCHAR',@UF_OLD)
      END

    IF (@DOCUMENTO_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'DOCUMENTO','VARCHAR',@DOCUMENTO_OLD)
      END
     
      IF (@DATANASCIMENTO_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'DATANASCIMENTO','DATE',@DATANASCIMENTO_OLD)
      END

    IF (@SEXO_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'SEXO','VARCHAR',@SEXO_OLD)
      END

    IF (@TELEFONE_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'TELEFONE','VARCHAR',@TELEFONE_OLD)
      END

    IF (@OBSERVACAO_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'OBSERVACAO','VARCHAR',@OBSERVACAO_OLD)
      END
     
      IF (@USUARIOID_OLD IS NOT NULL)
    BEGIN
     INSERT INTO LOGS_DATA (ID_LOGS, NOME_CAMPO, TIPO_CAMPO, VALOR) VALUES
       (@ID_LOGS,'USUARIOID','INT',@USUARIOID_OLD)
      END
END
Listagem 07: Gatilho Delete_Logs.

Esta etapa será idêntica aos gatilhos criados anteriormente, pois realizamos a verificação se o campo não encontra-se nulo realizando a inserção dos dados corretamente.

Poderemos conferir no Banco os objetos criados, Ver Imagem 03.
Figura 03: Objetos criados com sucesso.

Testando os gatilhos

Os gatilhos são objetos executados diretamente no Banco de Dados, de acordo com a operação desejada. Para dispará-los poderíamos utilizar qualquer interface mas para nosso teste a forma mais rápida para realizar esta tarefa seria diretamente pelo SSMSE (SQL Server Management Studio Express).
Abrindo a tabela de Clientes poderemos inserir, alterar ou excluir alguns dados e após uma consulta nas tabelas Logs e Logs_Data teremos um resultado idêntico ao da Figura 04.

Figura 04: Resultado final.

Conclusões

O uso de Triggers(gatilhos) em auditoria de sistemas é uma prática muito usual, pois todo o trabalho fica a cargo do servidor, livrando a interface do lado cliente para esta tarefa. Procurei neste artigo explorar um exemplo de como deveremos implementar esta técnica utilizando o SQL Server. Fiquem a vontade para modificar e adaptar estas rotinas conforme a necessidade do momento.
Um abraço a todos e até o mês que vem!


Postado por às
Marcadores: , ,

Nenhum comentário:

Postar um comentário

Assinar: Postar comentários (Atom)