Criando uma auditoria de dados com SQL Server

Criando uma auditoria de dados com SQL Server

A primeira opção que os programadores utilizam é fazer essa atualização por código. O problema disso é simples de entender : Está sendo mantida uma regra de negócio no client. Assim sendo, fica-se vinculado a todos os problemas de manutenção de uma regra de negócios no client, a saber :

• Os clients ficam mais complexos. É comum que uma empresa utilize 2 ou 3 clients para a mesma aplicação. A construção de tais clients se torna mais complexa.
• O sistema fica sujeito a erro. Se um dos clients utilizados pela empresa contiver um erro de programação todo o sistema fica comprometido.
• Alterações feitas por fora do client não registram os 2 campos adicionais.

Desta forma o ideal é mantermos esta regra de negócios no servidor, para desta forma não estarmos sujeitos a esses 3 problemas. Neste casso essa regra terá que ser mantida em um trigger no servidor de dados. Tal trigger deverá estar ligado tanto ao evento de insert quanto ao evento de update, pois em ambos os casos os 2 campos de auditoria precisam ser atualizados.

Supondo que temos uma tabela chamada "tabteste" com os campos de auditoria criados como "usuario" e "dataAlterado" e um campo chave chamado código. Vejamos um exemplo de uma tabela :

Create table tabteste (
codigo int primary key,
nome varchar(40) not null,
endereco varchar(50) not null,
usuario varchar(30) null,
dataAlterado smalldatetime )

E o código do trigger :

Create Trigger tru_audit on tabteste for insert,update as
update tabteste set usuario=suser_name(),dataAlterado=getdate() where codigo in (select codigo from inserted)

Com apenas uma instrução o trigger resolve o problema : utilizando uma subquery é feito um update na tabela tabteste em todos os registros que acabaram de ser alterados (os registros que estão na tabela inserted) atualizando o usuario e a data de alteração. Assim sendo toda atualização ou inserção nesta tabela terá automaticamente estes dois campos atualizados, sem necessidade de que a aplicação faça nada.

Os mais observadores irão notar que a aplicação ainda terá que lidar com a existência de dois campos adicionais na tabela. Para contornar isso, basta que a aplicação esteja utilizando uma view. Veja :

Create View vwTabTeste as
select codigo, nome,endereco from tabteste

Pode-se fazer um insert, por exemplo, na view, como se fosse uma tabela :

insert into vwTabTeste Values (02,'teste','rua a')

Isso possibilita inclusive que o DBA utilize este recurso com uma aplicação que já está pronta, sem que seja necessário alterar nada no código da aplicação. Se aplicação usa uma tabela TabTeste, pode-se renomear a tabela para TabTesteOriginal, adicionar os 2 campos nesta tabela TabTesteOriginal e criar uma view chamada TabTeste sem estes 2 campos. A aplicação irá chamar a view sem saber que trata-se de uma view, nenhuma diferença será notada na aplicação.

Mas estz solução, comum até a versão 7 do SQL Server, tem um ponto fraco : O Trigger faz uma atualização na própria tabela na qual ele está rodando. Assim sendo, ao fazer essa atualização ele seria chamado novamente, e assim continuamente, entrando em loop.

Será mesmo ?

Entre as opções de um banco de dados no SQL Server vocês encontram a opção recursive triggers. Sabendo o perigo do loop dentro de um trigger a Microsoft transformou isso em uma opção configuravel dentro do banco de dados : Quando recursive triggers está marcado o trigger pode chamar ele mesmo. Quando não está, não pode.

Menos mal. Se recursive triggers estiver desmarcado o trigger acima funciona, se estiver marcado não (entra em loop).

Só isso já manteria o Trigger funcionando, mas sejamos perfeccionistas : Depender de uma configuração do banco que pode ou não estar setada pode nos causar problemas. Se algum desavisado ativar a opção Recursive Triggers, ao rodar um insert equivalente ao que mostramos acima teremos o seguinte erro :

Server: Msg 217, Level 16, State 1, Procedure tru_audit, Line 3
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

O Trigger ficaria chamando a si mesmo eternamente se não fosse pelo limite máximo de chamadas no sql server (nesting level) que é de 32.

Temos então duas opções :

• No SQL Server 7.0 utilizar a variável @@NestLevel
• No SQL Server 2000 usar os triggers do tipo INSTEAD

@@NestLevel

A variável @@Nestlevel contém um número que indica o nível de recursividade que o trigger já sofreu. Se estiver em 0, então ainda não passou por recursividade alguma. Assim sendo, podemos só fazer o update quando o @@Nestlevel for 0. Continuaremos tendo duas chamadas do trigger, mas na segunda ele não irá fazer nada e consequentemente parará a recursividade.

Veja como fica nosso Trigger :

Create Trigger tru_audit on tabteste for insert,update as
If @@NestLevel=0
update tabteste set usuario=suser_name(),dataAlterado=getdate() where codigo in (select codigo from inserted)

Com este trigger não importa mais a configuração da opção Recursive Triggers : Se estiver desmarcada, o trigger roda apenas uma vez

Instead Of

No SQL Server 2000, porém, o problema pode ser resolvido de uma forma melhor. Existe um tipo especial de trigger chamado trigger de Instead. Este trigger não acontece após o evento (insert,update), mas em substituição ao evento. Assim sendo, o insert e o update não serão feitos pelo SQL Server, o trigger é que deverá decidir se irá faze-los ou não. Esse tipo de trigger não sofre recursividade (um update feito pelo trigger não dispara novamente o trigger) e consequentemente resolve nosso problema.

Precisaremos porém de 2 triggers de Instead : Um de Update e outro de Insert, não podemos mais fazer as 2 operações no mesmo código.

Veja o trigger de Instead para Insert :

Create Trigger tru_auditIns on tabteste instead of insert as
insert into tabteste select codigo,nome,endereco,suser_name(),getdate() from inserted

Mais uma vez com uma única instrução, um insert/select, resolvemos nosso problema. Observe a forma como o select foi montado : Ele pega os 3 campos que a tabela tem, mas ao invés de pegar os 2 campos usados na auditoria ele traz o nome do usuário e a data para que o insert já seja feito de forma atualizada na tabela tabteste.

Para o trigger de update teremos que fazer um update relacionando as duas tabelas : A tabela inserted e a tabela tabteste.

Veja como fica :

Create Trigger tru_auditUPD on tabteste instead of update as

update tabteste
set nome=i.nome,
endereco=i.endereco,
usuario=system_user,
dataAlterado=getdate()
from tabteste inner join inserted i on tabteste.codigo=i.codigo

Observe que a chave não sofre atualização e que no momento da atualização aproveitamos para gravar as duas colunas de auditoria.

Desta forma resolvemos de forma simples e confiável o problema de auditoria em alterações de dados utilizando os recursos do servidor.

Dennes Torres
MCSD,MCSE,MCDBA

Extraído do site : http://www.bufaloinfo.com.br