Criar log genérico e resolver problema ARITHABORT quando executa a trigger

Fonte: http://imasters.com.br/artigo/7714/sql-server/criando-log-de-operacoes-genericos-utilizando-triggers-e-tipo-xml-no-sql-server-2005/

A utilização de log de operações (ou trilha de auditoria) sobre dados de uma tabela é uma necessidade em várias aplicações hoje em dia para se ter conhecimento sobre o que ocorreu com um registro, quando e quem realizou operações nele. Antes do SQL Server 2005 isso poderia ser realizado utilizando estruturas relacionais complexas (para ganhar flexibilidade) ou então utilizando uma tabela espelho para cada tabela que fosse auditada (ganha-se facilidade no desenvolvimento, mas proliferam-se tabelas no banco de dados, ou seja, pouco flexível).

O SQL Server 2005 trouxe um recurso muito interessante que é o tipo XML. XML é uma linguagem de marcação estruturada e de puro conteúdo bastante difundida na internet. É possível ter bancos de dados inteiros dentro de um arquivo XML com várias estruturas diferentes. O tipo de dados XML implementa a XML dentro do banco de dados, ou seja, podemos ter um banco de dados dentro de uma coluna de uma tabela do SQL Server 2005. (É preciso ressaltar que o tipo de dados XML tem suas vantagens, contudo deve ser utilizado em aplicações específicas, como será descrito a seguir, pois o modelo relacional continua sendo muito mais eficiente).

Com o tipo XML se ganha bastante flexibilidade e é possível carregar várias tabelas diferentes dentro de um único campo, que está dentro de uma única tabela. Com isso está resolvido o problema de se ter uma tabela de histórico para cada tabela de produção com uma estrutura relacional bem simples. A criação desta tabela pode ser vista na listagem abaixo:

Função dos campos:

  • IdLog: Número seqüencial para identificar cada item de log
  • DtLog: Data que foi realizado o log
  • TbLog: Tabela sobre qual o log foi realizado. Será o Id da tabela no SQL Server
  • Chave: Não utilizado.
  • DsLog: O histórico no formato XML

Com a tabela de log já projetada, o próximo problema é como adicionar os itens modificados nesta tabela. Isso pode ser feito pela aplicação ou por disparadores (triggers). Neste artigo será demonstrado um disparador genérico para este fim, que deve ser criado em cada tabela que for auditada. Isso pode ser um inconveniente, pois algumas tabelas já podem ter disparadores associados a ela, contudo, isso pode ser resolvido criando um disparador adicional para o processo de log e definindo a ordem de execução usando a procedure de sistema sp_settriggerorder.

Para buscar a tabela sendo alterada e para que o disparador seja realmente genérico, recorre-se ao catálogo do sistema para buscar o id da tabela que será armazenado no campo TbLog. Para isto utilizamos a variável “global” @@PROCID, que retorna o id da procedure sendo executada (no caso, o disparador) e dela buscamos o id do objeto pai (no caso, a tabela a qual o disparador está ligado), conforme listagem abaixo:

Com a opção de utilização do disparador é necessário buscar os dados que estão sendo alterados. Isso pode ser feito buscando dados nas tabelas inserted e deleted, que são tabelas nativas do SQL Server, são válidas apenas no contexto de execução do disparador e funcionam da seguinte forma:

  • Inclusão (Insert): a tabela inserted armazena os registros incluídos e a deleted está vazia;
  • Exclusão (Delete): a tabela deleted armezena os registros excluídos e a inserted está vazia;
  • Alteração (Update): a tabela deleted armazena os registros como eram antes de alteração e a inserted armazena registros após as alterações.

Este conceito pode ser visto na listagem abaixo:

Mesmo conseguindo o id da tabela e a operação executada ainda não foi conseguido o principal, converter os dados das tabelas em XML. Isso pode ser feito facilmente utilizando-se a cláusula FOR XML do SELECT. Para este exemplo simples, utilizaremos a opção FOR XML RAW (vale a pena conhecer as outras opções da cláusula FOR XML no Books On-Line do SQL Server 2005, especialmente a opção PATH que está muito flexível). Desta forma será gerado um XML no formato a seguir:

A geração deste XML pode ser visualizada na listagem abaixo:

Contudo, com este código, foi gerado apenas um fragmento XML sem um nó raiz, que não é o desejado. Assim sendo, os fragmentos XML foram armazenados em variáveis para serem utilizados posteriormente. Eles serão concatenados a outras variáveis para que se tenha um XML bem formado. Neste XML serão adicionadas informações (como atributos) que ficarão no nó raiz, pois caracterizam todo o documento XML, tais como operação, data e usuário. Desta forma, para se obter o resultado desejado, o código anterior é estendido conforme a listagem abaixo:

Por fim, os dados precisam ser incluídos na tabela de log. O código completo do disparador pode ser visualizado na listagem abaixo:

Os comandos para testar o disparador podem ser vistos na listagem abaixo:

Os dados inseridos na tabela de log pode ser visto na figura abaixo.

Um nota importante na figura é o campo TbLog, que tem dados de duas tabelas diferentes (Clientes e Vendas).

Abrindo a coluna XML de um dos registros no SQL Server Management Studio, podemos visualizar o XML na íntegra. Será aberto um dos que tem UPDATE, pois temos uma operação mais interessante, conforme figura abaixo:

O log está gerado e armazenado no banco, contudo pra que uma trilha de auditoria funcione é necessário que se possa consultá-la. Por isso utilizamos algumas das chaves de pesquisa mais utilizadas (data e tabela) no modelo relacional, pois este modelo é mais eficiente que a consulta em XML. Então, uma consulta que busque todas as alterações na tabela de Clientes pode ser visualizada na figura abaixo:

Para encontrar a tabela desejada é utilizada a função interna do SQL Server OBJECT_ID que retorna o ID da tabela desejada e desta forma os dados são retornados com sucesso.

Contudo, por mais que a busca no modelo relacional seja mais indicada, pode haver casos em que se deseje buscar informações dentro do campo XML. Isso pode ser feito com a linguagem de consulta XML, chamada XQUERY, que também foi implementada (e estendida) no SQL Server 2005. Por exemplo, poderia ser desejado trazer todas as operações de DELETE na tabela de Vendas. Não temos o campo operação no modelo relacional (se a busca sobre ele fosse freqüente, talvez fosse uma boa idéia que ele estivesse como um campo relacional), logo será necessário buscá-lo dentro do XML. A busca em questão e seu resultado podem ser visualizados na figura abaixo:

Esse tipo de busca funciona, mas verificando seu plano de execução, percebe-se uma grande utilização de buscas não indexadas (operador da figura). Isso pode ser melhorado, criando-se índices para o campo XML.

Existem dois tipos de índices para tipo XML:

  • Primário: pode existir apenas um índice primário no campo XML e para criá-lo é necessário que a tabela onde está o campo XML tenha uma chave primária. O índice primário criará uma estrutura de índice similar ao índice clustered que será utilizada posteriormente para melhorar o desempenho das pesquisas.
  • Secundário: para melhorar as pesquisas ainda mais, existem os índices secundários, pois eles são utilizados de maneira diferente e de como a busca é feita: por caminho (/root/leaf[@id= “1”]), por propriedade ((/root/leaf)[1]) e por valor (o que foi utilizado na pesquisa).

Para o fim deste artigo, a explicação da XQuery e os tipos de índices secundários excedem o escopo. Mais informações sobre eles podem ser encontrados no Books On-Line do SQL Server 2005 (tópicos XML indexes – secundary e XQuery).

A criação do índice primário pode ser vista na listagem abaixo:

Os operadores para a pesquisa já são alterados realizando-se a mesma consulta, e passam a ser Index Scan e Index Seek (figura):

Desta forma, a busca por XQuery ficará bem mais rápida.

O código apresentado teve o objetivo de ser mais didático. O campo chave que foi criado e nunca utilizado existe já pensando numa busca, que provavelmente seria uma das mais utilizadas: buscar na tabela x todas as alterações do id y, contudo a implementação disso não é tão simples quanto o desejado. Muita coisa nesta rotina pode ser melhorada utilizando a cláusula FOR XML PATH e querys dinâmicas (EXEC). Isto fica como exercício aos leitores.

 

Resolver o problema do ARITHABORT

sp_configure ‘user options’, 64

RECONFIGURE

 

Deixar uma resposta

O seu endereço de email não será publicado. Campos obrigatórios marcados com *