SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)

Olá pessoal,
Bom dia!

No post de hoje, vou demonstrar como criar um histórico de alterações de dados, seja por INSERT, DELETE ou UPDATE em uma tabela, de forma que você consiga saber qual o usuário e quando uma informação foi alterada e qual era a informação da época. Isso é muito útil para auditoria ou relatórios de BI que precisam obter o cenário da época, como por exemplo, o salário do cliente na época em que ele adquiriu um produto, 5 anos atrás.

Para esse operação, vou criar uma tabela “clone” da tabela original, mas com uma data da atualização, a informação do usuário que realizou a alteração, hostname, operação realizada e vou criar uma trigger na tabela, para que a cada operação de DML grave um registro na tabela de histórico.

Criar o histórico utilizando Trigger ou pelo Sistema?

Antes de apresentar essa solução, gostaria antes de demonstrar alguns pontos de vista sobre a criação de triggers em tabelas para este fim. Por muito tempo, eu fui totalmente contra a criação de triggers em banco de dados, por achar que isso iria deixá-los menos performáticos, um update ou delete muito grande poderia gerar locks em produção, etc e achava que o desenvolvedor que deveria se preocupar, via código-fonte do sistema, com essa parte de logs e não criando triggers no banco de dados.

Isso mudou quando comecei a ver o lado do programador e pensar em rastreabilidade de informações, e o quanto isso pode ajudar outros setores de uma empresa, como Auditoria, BI e Controladoria. Hoje em dia, com a introdução da filosofia DevOPS, o DBA não pode pensar dentro da caixa preta, focando apenas em disponibilidade do banco de dados. É preciso focar no negócio e agregar o máximo de valor que a TI puder para a empresa, mas é claro, sempre na base do possível, não comprometendo a disponibilidade dos recursos de TI.

Diante disso, vou listar as diferenças entre criar esse histórico de dados utilizando trigger ou via sistema:

Gerando histórico através de trigger no banco de dados:

• Uma vez desenvolvida, a implantação do recurso envolve apenas a criação de uma tabela e uma trigger no banco de dados
• Não importa qual rotina ou usuário esteja manipulando a tabela, todas as alterações sempre serão gravadas
• UPDATE, INSERT e DELETE feitos manualmente no banco de dados serão logados e auditados pela trigger, e será gerado histórico para isso
• Tanto o DBA quanto o Desenvolvedor tem visibilidade sobre a existência da rotina e seu código-fonte
• Se for necessário desativar a trigger temporariamente para alguma operação, isso pode ser feito em poucos segundos pelo DBA
• O gerenciamento da rotina de auditoria fica nas mãos do DBA
• Caso a tabela sofra uma grande alteração de dados manual, seja via INSERT, DELETE ou UPDATE, todas as alterações serão gravadas na tabela de histórico, o que pode gerar um volume de gravações na tabela de histórico muito grande e causar lentidão no ambiente. Isso pode ser contornado desativando a trigger enquanto essas alterações em massa são realizadas e ativando novamente ao término
• Caso a alteração seja realizada pelo sistema, e o sistema utilize um usuário fixo, a trigger irá gravar o usuário do sistema, e não o usuário da pessoa que realizou a alteração

Gerando histórico através do sistema:

• A implementação envolve realizar alterações no código-fonte de todos os trechos de código da aplicação e telas que manipulam dados na tabela envolvida (além de arquivos dependentes), onde geralmente existem janelas rígidas para qualquer modificação em sistema
• Apenas as telas que foram alteradas para gravar histórico efetivamente o farão
• UPDATE, INSERT e DELETE feitos manualmente no banco de dados NÃO serão logados e não haverá histórico para essas alterações
• Apenas o desenvolvedor sabe que esse recurso existe e como ele funciona. O DBA geralmente não tem acesso a esse tipo de informação e muito menos, o código-fonte para entender como esse histórico está sendo gerado
• Se for necessário desativar esse recurso temporariamente, o desenvolvedor terá que alterar no código-fonte da aplicação e fazer o deploy em produção, consumindo bastante tempo de duas equipes e com possibilidade de desconectar sessões ativas no servidor de aplicação
• O gerenciamento da rotina de auditoria fica nas mãos do Desenvolvedor
• Caso a tabela sofra uma grande alteração de dados manual, seja via INSERT, DELETE ou UPDATE, o ambiente não será afetado, pois alterações manuais no banco não serão gravadas
• Caso a alteração seja realizada pelo sistema, é possível identificar o usuário logado na aplicação e gravar o login ou até mesmo realizar queries no banco e retornar um Id_Usuario da tabela Usuarios, por exemplo, para gravar na tabela de histórico

Como vocês observaram nos itens citados acima, existem vantagens e desvantagens em cada uma das abordagens. Sendo assim, você deverá decidir qual se encaixa melhor ao seu negócio e à sua infraestrutura.

Criando o processo de auditoria

Para os testes da trigger, vou criar uma tabela de clientes e inserir alguns dados para visualizarmos o log funcionando:

Enfim, chegou a hora de criarmos nossa auditoria:

E agora vamos simular algumas alterações na base:

Como vocês podem observar, eu estou utilizando o objeto Inserted na parte que trata o UPDATE. Com isso, eu gravo na tabela de histórico as informações novas, que estão sendo atualizadas pelo comando de UPDATE. Caso você queira gravar os valores antigos, que estão sendo substituídos, basta alterar a trigger para utilizar o objeto Deleted.

Alterações na tabela original

Caso vocês precisem incluir mais colunas na sua tabela original, lembrem-se de alterar também a tabela de histórico da sua trigger, uma vez que eu utilizei * na hora de inserir os dados justamente para não precisar ficar digitando o nome das colunas. Uma alternativa a isso, é tirar o * da trigger e especificar manualmente o nome de todas as colunas, que aí não tem problema.

Simulando que você altere a sua tabela original, sem alterar a tabela de histórico, temos essa situação:

Onde foi gerada a mensagem de erro:

Msg 8101, Level 16, State 1, Procedure trgHistorico_Cliente, Line 17
An explicit value for the identity column in table ‘Testes.dbo.Cliente_Log’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Para corrigir isso, basta adicionar a mesma alteração de estrutura realizada na tabela de log:

E é isso aí pessoal!
Obrigado pela visita e em caso de dúvidas, deixem aqui nos comentários.

Abraço!