Problema com tipo de dados XML

Question

Olá amigos,

Estou utilizando a seguinte rotina para gravação de log de alterações:

USE Northwind
GO

CREATE TABLE Log
(
	LogID		INT IDENTITY(1, 1) NOT NULL,
	DataHora	DATETIME2(0)	   NOT NULL,
	Ip		VARCHAR(20) 	   NOT NULL,
	UsuarioID	INT	           NOT NULL,
	NomeTabela	VARCHAR(50)	   NOT NULL,
	Operacao	CHAR(1)		   NOT NULL,
	Historico	XML   		   NOT NULL
)
GO

ALTER TABLE Log ADD CONSTRAINT PK_Log_LogID PRIMARY KEY (LogID)
GO

ALTER TABLE Log ADD CONSTRAINT DF_Log_DataHora DEFAULT GETDATE() FOR DataHora
GO


-------------------------------------------------------------


CREATE PROCEDURE sp_Log 
  @Inserted   XML
 ,@Deleted    XMl
 ,@NomeTabela VARCHAR(50)                 
AS
BEGIN
    DECLARE  @Ip         VARCHAR(20)
            ,@UsuarioID  INT
	    ,@Operacao   CHAR(1)
	    ,@Historico  XML
	
	SET @Ip = ( SELECT client_net_address 
		    FROM   sys.dm_exec_connections 
		    WHERE  session_id = @@SPID
		  )
			  
    SET @UsuarioID = 1 --Teste
		
	IF (@Inserted IS NOT NULL) AND (@Deleted IS NULL)          --Insert
		SET @Operacao = 'I'	
	ELSE IF (@Inserted IS NOT NULL) AND (@Deleted IS NOT NULL) --Update
		SET @Operacao = 'U'
	ELSE IF (@Inserted IS NULL) AND (@Deleted IS NOT NULL)     --Delete
		SET @Operacao = 'D'
		
	IF @Operacao IS NULL --Nenhuma operação realizada
	    RETURN  
		
	SET CONCAT_NULL_YIELDS_NULL OFF
	
	SET @Historico = 	
	N'
	  <Historico>
		<Inseridos>' + CAST( @Inserted AS VARCHAR(MAX) ) + '</Inseridos>
		<Deletados>' + CAST( @Deleted  AS VARCHAR(MAX) ) + '</Deletados> 
	  </Historico>
	 '
	 
	SET CONCAT_NULL_YIELDS_NULL ON
	
	BEGIN TRY
		INSERT Log (  UsuarioID 
			    , Ip	      
			    , Operacao  
			    , NomeTabela
			    , Historico 
		          )
		    VALUES(   @UsuarioID
			    , @Ip
			    , @Operacao
			    , @NomeTabela
			    , @Historico
			  )
    END TRY
    BEGIN CATCH
	RAISERROR('Erro ao tentar gravar log', 10, 1)
    END CATCH
END

GO

-------------------------------------------------------------

CREATE TRIGGER trg_Log ON Customers
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  DECLARE   @Inserted XML = (SELECT * FROM inserted FOR XML RAW)
          , @Deleted  XML = (SELECT * FROM deleted  FOR XML RAW)
  
  EXEC sp_Log @Inserted, @Deleted, 'Customers'
END

Funciona normalmente, porem para alterações de grande escala, como 50.000 registros,

a procedure não esta armazenando o xml inteiro.

Answer 1

Boa Noite,

É preciso lembrar que o XML tem limitações e o tamanho máximo é 2GB. Não sei o tamanho dos seus registros, mas é preciso lembrar que converter uma tabela inteira em XML irá aumentar significativamente o tamanho já que as TAGs consomem muito espaço. É possível que esse volume esteja estourando o espaço e truncando o resultado.

Honestamente não sei se essa é uma boa solução para auditar registros. Uma alteração que mude todas as linhas da tabela irá por exemplo triplicar o tamanho da tabela (você terá a versão atual e as duas versões geradas pela trigger). Não vejo isso escalável a longo prazo, além do que você irá gerar uma massa de dados gigantesca com tantos dados XML. O uso de um CDC seria muito mais recomendável nesse caso.

Se o objetivo for apenas auditar sem a necessidade do dado alterado, o uso de recursos de auditoria seria muito mais indicado.

[ ]s,

Gustavo Maia Aguiar
Blog: http://gustavomaiaaguiar.wordpress.com
Vídeos:http://www.youtube.com/user/gmasql

---

Classifique as respostas. O seu feedback é imprescindível

Answer 2

Excelente.

Answer 3

Excelente.

"Assisti as vídeo aluas que você fez, "MSDN Experience SQL Server 2005". Parabêns pelo trabalho.

Answer 4

Obrigado :)

---

Classifique as respostas. O seu feedback é imprescindível