Artigo da .net Magazine 41 - Auditando operações DML com OUTPUT

Clique aqui para ler esse artigo em PDF.

Clique aqui para ler todos os artigos desta edição

SQL Server

Auditando operações DML com OUTPUT

Imagine o seguinte cenário: Você possui uma tabela muito importante em seu banco de dados e gostaria de implantar uma auditoria de modificação de dados para armazenar todas as informações referente a operações DML (Data Manipulation Language), executadas sobre a tabela. O objetivo é manter um histórico dos dados alterados e excluídos da tabela e principalmente quem executou a ação.

Bom, para quem já precisou implantar auditoria de modificações de dados no SQL Server 2000 ou posterior, sabe que a maneira mais simples, é utilizando uma técnica conhecida como Mirror Table ou “tabela espelho”.

O problema dessa técnica é que exige a criação sobre a tabela auditada, de uma trigger que monitore as operações de Insert, Update e Delete, e dependendo da utilização da tabela o desempenho do seu sistema pode ser significativamente prejudicado.

Para resolver esse problema o SQL Server 2005 possui a nova cláusula OUTPUT, que pode ser usada em conjunto com os comandos DML de Insert, Update ou Delete, para retornar os registros afetados pela operação.

Neste artigo mostrarei como você pode usar a OUTPUT para implantar uma auditoria de operações DML em uma tabela.

Conhecendo o método de espelhamento de tabela

Até a versão 2000 do SQL Server, a maneira mais simples de implementar uma auditoria de operações DML é utilizando uma técnica conhecida como Mirror Table ou tabela espelho. A técnica consiste em criar duas tabelas idênticas e criar na tabela principal, triggers de Insert, Update e Delete para que na execução de uma dessas operações, a trigger seja disparada e armazene na tabela espelho os dados afetados pela operação.

Para demonstrar como essa técnica funciona, vamos supor que precisamos monitorar todas as alterações realizadas sobre a tabela Products do banco de dados Northwind. O primeiro passo então é criar uma tabela espelho que tenha exatamente as mesmas colunas da tabela Products.

O script da Listagem 1 é o de criação da tabela Products, porém alterei o nome da tabela para “Products_Espelho”. Por se tratar apenas de um exemplo, estarei desconsiderando a criação de possíveis índices, constraints etc.

Listagem 1. Script para criação da tabela Products_Espelho

CREATE TABLE [dbo].[Products_Espelho](

  [ProductID] [int] NOT NULL,

  [ProductName] [nvarchar](40) NOT NULL,

  [SupplierID] [int] NULL,

  [CategoryID] [int] NULL,

  [QuantityPerUnit] [nvarchar](20) NULL,

  [UnitPrice] [money] NULL,

  [UnitsInStock] [smallint] NULL,