Triger Log

[luca.paiva 0]

Boa dia

 

Estou com um problema, gostaria de criar uma trigger de log que me retornasse quais campos foram alterados, excluidos e incluidos, mesmo que a tabela possua um campo text ou image, o que impossibilita o uso das tabelas inserted e deleted.

 

Por enquanto tenho a seguinte trigger, que funciona muito bem nas tabelas com os campos mais "comuns".

 

CREATE TRIGGER [dbo].[LOG_TESTE] ON [dbo].[TESTE]
		AFTER INSERT, UPDATE, DELETE
		AS
		SET NOCOUNT ON

		--Buscando a qry que foi executata
		DECLARE @ExecStr varchar(max), @Qry nvarchar(max) 
		CREATE TABLE #inputbuffer 
		( 
			EventType nvarchar(max), 
			Parameters int, 
			EventInfo nvarchar(max) 
		) 
		SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' 
		INSERT INTO #inputbuffer 
		EXEC (@ExecStr) 
		SET @Qry = (SELECT EventInfo FROM #inputbuffer)

		DECLARE @IdTabela INT, @Operacao VARCHAR(45)
		DECLARE @Inserted VARCHAR(MAX)
		DECLARE @Deleted VARCHAR(MAX)
		DECLARE @XML AS VARCHAR(MAX)
		DECLARE @LinhasAfetadas INT
		--Buscando id da tabela
		SELECT @IdTabela = parent_object_id
		FROM sys.objects
		WHERE object_id = @@PROCID
		
		SET @Inserted = ''
		SET @Deleted = ''
		
		--Verificando tipo do trigger
		IF EXISTS(SELECT 1 FROM inserted)
		BEGIN
			SET @LinhasAfetadas = (select count(*) from inserted)
			SET @Operacao = 'INSERT'
			SET @Inserted = (SELECT * FROM INSERTED AS Inserido 
				ORDER BY 1 FOR XML RAW('Inserido'), ROOT('Inseridos'))
		END
	
		IF EXISTS(SELECT 1 FROM deleted) 
		BEGIN
			IF @Operacao IS NULL
				SET @Operacao = 'DELETE'
			ELSE
				SET @Operacao = 'UPDATE'
		
			SET @LinhasAfetadas = (select count(*) from deleted)	
			SET @Deleted = (SELECT * FROM DELETED AS Excluído 
				ORDER BY 1 FOR XML RAW('Excluído'), ROOT('Excluídos'))
		END
		
		--Montando xml
		--Nó raiz (com informações extra)
		SET @XML = '<Log Tabela="TESTE" Operação="' + @Operacao + '" Data="' + 
			REPLACE(CONVERT(CHAR(19), GETDATE(), 121), ' ', 'T') +
			'" Usuário="' + SUSER_NAME() + '">'
		--Dados
		SET @XML = @XML + @Inserted + @Deleted + '</Log>'
		
		--Inserindo
		if (not @deleted = '') OR (not @inserted = '')
		BEGIN
			BEGIN TRY
				INSERT INTO LogGeral(DtLog, TbLog, usuario, operacao, LinhasAfetadas, qry, DsLog) 
			    VALUES (GETDATE(), 'TESTE', SUSER_NAME(), @operacao, @LinhasAfetadas, @qry, @XML)
			END TRY
			BEGIN CATCH
				DECLARE @Erro VARCHAR(max) 
				SET @Erro = 'Erro ao executar operação de log. Mensagem: ' + 
					ERROR_MESSAGE()
				ROLLBACK
				INSERT INTO ErroLog (tabela, usuario, dtLog, msgerrro, qry)
				VALUES ('a', SUSER_NAME(), GETDATE(), @Erro, @qry)			
				--se der erro durante a trigger, a mesma é desabilitada e o comando é rodado novamente para depoir ser reabilitada	
			END CATCH
	    END

 

Grato,

Lucas Paiva

[advaldomesquita 93]

lucas, no seu caso nao rola fazer uma auditoria? De uma pesquisada no forum para verificar.

 

Abçs