Olá meu leitor! Depois de um longo hiato, eis que surjo com uma coisa interessante, seguindo aquela linha de publicar artigos que relatem alguma experiência que passei.

Em vários sistemas necessitamos de rastrear as alterações que foram feitas nos dados. Quem fez, quando, e o que foi alterado são as perguntas que podem ser feitas com o procedimento de auditoria de dados. De forma geral, criamos na base de dados uma tabela de auditoria que receberá os dados citados acima.

Mas quem gravará estes dados na tabela de auditoria: a aplicação ou o banco de dados?

A lógica nos sugere que quem cuide dessa parte de auditoria de banco de dados seja a o próprio banco de dados. É simples implementar uma trigger em cada tabela que capture se a operação que foi feita foi um Insert, um Update ou um Delete e o que foi alterado e quando foi alterado.

Mas e QUEM fez a alteração, #comofas? Vou fazer uma outra pergunta: Como é feito o gerenciamento de usuários da sua aplicação? Os usuários são cadastrados em uma tabela do BD ou cada usuário da aplicação é um usuário de banco de dados?

O caso mais complexo é o do gerenciamento de usuários pela aplicação e que não se relaciona com os usuários da base de dados, ou seja, na string de conexão ao banco de dados o usuário e a senha são fixos. Então vamos estudar esse cenário, belê!

O SQL Server tem uma função que se chama user_name() e que retorna o usuário que está conectado ao banco de dados. Só que, dado o cenário acima, independentemente de quem esteja autenticado na aplicação a função user_name() retornará sempre o usuário da string de conexão do banco de dados, fixa no nosso caso, então não servindo para o nosso propósito.

Hum... queremos fazer o gerenciamento por trigger no banco, deixando essa parte de auditoria invisível para a aplicação e não podemos usar essa função user_name() que era tão óbvia...

Maaaaaaaaas, graças à POG (sempre ela!) temos uma solução!

Na string de conexão ao SQL Server (estou tomando a conexão através de um objeto SqlConnection do ADO.NET para referência) temos um parâmetro chamado “Workstation ID” (geralmente é usado para passar o nome da máquina que está se conectando ao banco), e que pode ser recuperado pelo banco de dados através da função host_name().

Então acho que deu para sacar qual será a nossa gambiarra: Passar o usuário logado na aplicação nesse parâmetro Workstation ID e nas triggers de auditoria usar a função host_name()! É isso mesmo, meu querido!

Então vamos logo ao código da trigger, mas antes vamos criar nossa tabela de auditoria:

/*Tabela de Auditoria*/
create table TB_AUDIT (
	ID int identity not null,
	DATA_OPERACAO datetime,
	USUARIO varchar(100),
	TABELA varchar(100),
	OPERACAO char(1),
	OLD_DATA xml,
	NEW_DATA xml
);

/*Modelo de Trigger para auditar os dados. Fazer uma para cada tabela que deseja ter os dados rastreados*/
CREATE TRIGGER dbo.TRG_AUD_MINHA_TABELA
   ON  dbo.MINHA_TABELA
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	declare @Operacao char(1);
	declare @OLD_DATA xml;
	declare @NEW_DATA xml;
	/*Verifica a operação realizada*/
	if ( (select count(*) from INSERTED) > 0 and (select count(*) from DELETED) > 0)
	begin
		set @Operacao = 'U';
	end
	if ( (select count(*) from INSERTED) = 0 and (select count(*) from DELETED) > 0)
	begin
		set @Operacao = 'D';
	end
	if ( (select count(*) from INSERTED) > 0 and (select count(*) from DELETED) = 0)
	begin
		set @Operacao = 'I';
	end

	/*Grava os dados na tabela de auditoria*/
	if (@Operacao = 'I')
	begin
		set @NEW_DATA = (select * from INSERTED for xml auto);
		set @OLD_DATA = null;
		insert into TB_AUDIT (DATA_OPERACAO,USUARIO,TABELA,OPERACAO,OLD_DATA,NEW_DATA) values
		(getdate(),host_name(),'MINHA_TABELA',@Operacao,@OLD_DATA,@NEW_DATA);
	end

	if (@Operacao = 'D')
	begin
		set @NEW_DATA = null;
		set @OLD_DATA = (select * from DELETED for xml auto);
		insert into TB_AUDIT (DATA_OPERACAO,USUARIO,TABELA,OPERACAO,OLD_DATA,NEW_DATA) values
		(getdate(),host_name(),'MINHA_TABELA',@Operacao,@OLD_DATA,@NEW_DATA);
	end

	if (@Operacao = 'U')
	begin
		set @NEW_DATA = (select * from INSERTED for xml auto);
		set @OLD_DATA = (select * from DELETED for xml auto);
		insert into TB_AUDIT (DATA_OPERACAO,USUARIO,TABELA,OPERACAO,OLD_DATA,NEW_DATA) values
		(getdate(),host_name(),'MINHA_TABELA',@Operacao,@OLD_DATA,@NEW_DATA);
	end
END
GO

Na tabela TB_AUDIT temos os campos básicos como um sequencial numérico, a data, hora, usuário, nome da tabela e a operação que foi feita. Os dados que foram alterados serão transformados em XML e colocados nos campos OLD_DATA e NEW_DATA. Optei por gravar esses dados em XML para simplificar a nossa trigger e poder rastrear todos os campos de uma só vez, independente da tabela ter ou não chave primária (vi por aí soluções em que a tabela a ser auditada necessitava ter uma PK) e consolidar as alterações feitas.

Em uma trigger do SQL Server, temos as tabelas temporárias INSERTED e DELETED, onde:

- Em uma trigger de AFTER DELETE, a tabela INSERTED é vazia e a DELETED contém o registro que foi alterado.

- Em uma trigger AFTER INSERT, a tabela INSERTED contém o registro que foi inserido e a DELETED está vazia.

- Em uma trigger AFTER UPDATE, a tabela INSERTED contém o registro novo e a DELETED contém o antigo.

Primeiramente utilizando as condições acima “calculamos” o valor do campo OPERACAO, que pode ser “I” para Insert, “D” para Delete e “U” para Update.

Após isso, verificamos qual é a operação e populamos as variáveis @NEW_DATA e @OLD_DATA conforme a necessidade, utilizando uma instrução SELECT nas tabelas INSERTED e DELETED com a cláusula FOR XML AUTO, que retorna os campos em um único registro no formato XML, onde cada campo é um atributo da tag XML, com o dado sendo o valor deste atributo.

Em seguida inserimos o dado na tabela de auditoria, com o usuário da aplicação que fez a alteração sendo capturado através da função host_name(), pois passamos esse valor no parâmetro Workstation ID da string de conexão.

Do lado da aplicação, a sua string de conexão deve ser dinâmica, tendo que passar o parâmetro Workstation ID a cada conexão. Por exemplo, você pode implementar uma classe que cuida dos objetos de conexão ao banco e nela ter uma variável que receberá o Workstation ID. O ideal aí é que você tenha uma única função que formate a string de conexão e informe-a aos objetos SqlConnection, algo como do tipo:

Como deve estar a sua string de conexão no arquivo de configuração:

<connectionStrings>
	<!--O parâmetro Workstation ID será preenchido pela aplicação cada vez que ela requisitar a string de conexão-->
	<add name="Cnx" connectionString="Server=servidor;Database=MEU_BANCO;User Id=USUARIO; Password=SENHA; Trusted_Connection=False;Workstation ID={0}"/>
</connectionStrings>

Função para montar a string de Conexão

public static string WorkID = ""; //Passar aqui o login do usuário que se autenticou na aplicação!

public string getConnectionString()
{
	return String.Format(ConfigurationManager.ConnectionStrings["Cnx"].ConnectionString, WorkID);
}

Fazendo desta forma, não precisaremos fazer com que a aplicação fique criando usuários na base de dados somente para esse fim de auditoria. Talvez o DBA da sua empresa não goste de algo do gênero hehe.

Um abraço e até!