none
Problema com tipo de dados XML RRS feed

  • Pergunta

  • 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.

    • Movido Gustavo Maia Aguiar quinta-feira, 9 de agosto de 2012 03:03 (De:SQL Server - Desenvolvimento Geral)
    terça-feira, 31 de julho de 2012 16:42

Respostas

  • 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

    • Sugerido como Resposta Gustavo Maia Aguiar terça-feira, 31 de julho de 2012 22:30
    • Marcado como Resposta Harley Araujo quinta-feira, 2 de agosto de 2012 13:34
    terça-feira, 31 de julho de 2012 22:30

Todas as Respostas

  • 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

    • Sugerido como Resposta Gustavo Maia Aguiar terça-feira, 31 de julho de 2012 22:30
    • Marcado como Resposta Harley Araujo quinta-feira, 2 de agosto de 2012 13:34
    terça-feira, 31 de julho de 2012 22:30
  • Excelente.
    segunda-feira, 6 de agosto de 2012 15:59
  • Excelente.

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

    segunda-feira, 6 de agosto de 2012 16:00
  • Obrigado :)

    Classifique as respostas. O seu feedback é imprescindível

    quinta-feira, 9 de agosto de 2012 03:03