Automatizando a criação de triggers de Auditoria (SQL Server)
Leonel Fraga de Oliveira 29/11/2010 17:55

Olá leitor! Depois de muito tempo sem escrever aqui, que tal que eu complemente aquele artigo de auditoria de dados no SQL Server, hein? ;)

Imagine só o cenário: Você quer auditar as tabelas no banco de dados conforme está no artigo sobre, e são dezenas, e até centenas de tabelas para escrever a trigger. Imagine só o trampo que vai dar!

Para isso, vamos automatizar a coisa. Vou colocar o código de uma Stored Procedure que criará as triggers de auditoria! Vamos a ele:

create procedure [dbo].[spCriaTriggersAuditoria]
as
begin
	declare @isql varchar(8000);
	declare @TABLE_NAME sysname;
	SELECT  @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
	WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME not in ('TABELA_AUDITORIA');

	while @TABLE_NAME is not null
	begin
		EXEC('IF OBJECT_ID (''TRG_AUD_' + @TABLE_NAME + ''', ''TR'') IS NOT NULL DROP TRIGGER TRG_AUD_' + @TABLE_NAME);
		set @isql = 'create TRIGGER dbo.TRG_AUD_' + @TABLE_NAME + ' 
				   ON  dbo.' + @TABLE_NAME + '
				   AFTER INSERT,DELETE,UPDATE
				AS 
				BEGIN
					declare @Operacao char(1);
					declare @OLD_DATA xml;
					declare @NEW_DATA xml;
					declare @ID_USUARIO int;
					/*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

					/*Pega o ID do usuário baseando-se no login passado no host_name(). Isto evita caso o usuário mude o login*/
					select @ID_USUARIO = ID_USUARIO from TB_USUARIO where LOGIN = host_name();

					/*Grava os dados na tabela de auditoria*/
					if (@Operacao = ''I'')
					begin
						set @NEW_DATA = (select * from INSERTED for xml raw(''INSERIDO''), BINARY BASE64, ELEMENTS XSINIL);
						set @OLD_DATA = null;
						insert into TB_AUDIT (DATA_OPERACAO,USUARIO,TABELA,OPERACAO,OLD_DATA,NEW_DATA,ID_USUARIO) values
						(getdate(),host_name(),''' + @TABLE_NAME + ''',@Operacao,@OLD_DATA,@NEW_DATA,@ID_USUARIO);
					end

					if (@Operacao = ''D'')
					begin
						set @NEW_DATA = null;
						set @OLD_DATA = (select * from DELETED for xml raw(''EXCLUIDO''), BINARY BASE64, ELEMENTS XSINIL);
						insert into TB_AUDIT (DATA_OPERACAO,USUARIO,TABELA,OPERACAO,OLD_DATA,NEW_DATA,ID_USUARIO) values
						(getdate(),host_name(),''' + @TABLE_NAME + ''',@Operacao,@OLD_DATA,@NEW_DATA,@ID_USUARIO);
					end

					if (@Operacao = ''U'')
					begin
						set @NEW_DATA = (select * from INSERTED for xml raw(''DADO_NOVO''), BINARY BASE64, ELEMENTS XSINIL);
						set @OLD_DATA = (select * from DELETED for xml raw(''DADO_ANTIGO''), BINARY BASE64, ELEMENTS XSINIL);
						insert into TB_AUDIT (DATA_OPERACAO,USUARIO,TABELA,OPERACAO,OLD_DATA,NEW_DATA,ID_USUARIO) values
						(getdate(),host_name(),''' + @TABLE_NAME + ''',@Operacao,@OLD_DATA,@NEW_DATA,@ID_USUARIO);
					end
				END'
		exec (@isql);
		if @@ERROR > 0 begin
			print 'Tabela: ' + @TABLE_NAME + '' + @isql;
		end
		SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
		WHERE TABLE_NAME > @TABLE_NAME AND TABLE_TYPE = 'BASE TABLE' 
		AND TABLE_NAME not in ('TABELA_AUDITORIA');
	end
end

Como você pode ver, ele é simples: Criamos duas variáveis, sendo uma o código SQL que cria a trigger e outra com o nome da tabela em que a trigger será criada.

Recuperamos a primeira tabela através do Select na tabela de sistema INFORMATION_SCHEMA.TABLES, onde o tipo de tabela (campo TABLE_TYPE) seja BASE_TABLE (tabelas do usuário) e podemos utilizar uma cláusula NOT IN para determinar as tabelas que NÃO criaremos a trigger de auditoria. Exemplo? A própria tabela de auditoria!

Veja que utilizamos uma iteração com WHILE até que a variável @TABLE_NAME seja nula. Mas como isso, se não temos um cursor?

Inicializamos a variável @TABLE_NAME com a primeira tabela em ordem alfabética com a função MIN(), em seguida entramos no looping e damos um “execute immediate” na instrução SQL que cria a trigger. Em seguida a isso, dentro do looping, damos um novo SELECT MIN() para alimentar de novo a variável @TABLE_NAME com a tabela seguinte na ordem alfabética, através de sua cláusula Where.

Quando o looping chega na última tabela e vai fazer o select para reinicializar a variável @TABLE_NAME, não haverá nenhuma correspondência e a nossa variável assumirá o estado NULL, saindo do WHILE na próxima iteração.

Simples, não é? Mas, tenho algumas considerações para fazer:

- A trigger de auditoria NÃO contempla tipos TEXT e IMAGE. Isto se deve a uma limitação do SQL Server que não consegue transformar estes tipos de dados na cláusula FOR XML que utilizamos na trigger. Estes tipos de dados devem ser substituídos por VARCHAR(MAX) ou VARBINARY(MAX), respectivamente.

- Um registro na tabela de auditoria pode conter alterações para N registros das tabelas auditadas. O motivo disso é que as triggers do SQL Server trabalham sobre o conjunto de dados como um todo, e não com cada registro individual. Por exemplo, damos uma instrução de UPDATE que afeta 1000 registros em uma tabela X. As tabelas INSERTED e DELETED que são utilizadas em uma trigger AFTER UPDATE conterão estes 1000 registros e a trigger disparará apenas uma vez. A transformação em XML do select que pega as alterações na trigger de auditoria transforma estes 1000 registros em um único registro XML.

E é isso! Finalizamos (finalmente!) essa parte de auditoria de dados de forma simplificada.

Um abraço ;)

Leonel Fraga de Oliveira Leonel Fraga de Oliveira é formado em Processamento de Dados na Faculdade de Tecnologia de São Paulo (FATEC-SP - 2002) e anteriormente em Técnico em Eletrônica, pela ETE Professor Aprígio Gonzaga (lá em 1999).
Atualmente trabalha como Analista de Sistemas na Prefeitura Municipal de São Caetano do Sul - SP
Tem como hobbies DJing (também trabalha como DJ freelancer) e ciclismo, além da manutenção dos sites NeoMatrix Light e NeoMatrix Tech.
Gosta de música eletrônica, tecnologia, cinema (super fã de Jornada nas Estrelas), gastronomia e outras coisas mais.


Compartilhe nas redes sociais

   

Deixe seu comentário

comments powered by Disqus