Guia Linguagem SQL

Artigo SQL Magazine 73 - Usando CDC e Trigger em Auditorias no SQL Server 2008

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Nesse artigo será demonstrado o recurso Change Data Capture (CDC) presente no MS SQL Server 2008 e como utilizar Trigger para registrar as alterações ocorridas nas tabelas. Abordaremos a melhor forma de utilizá-los em auditorias no banco de dados.

[lead]

De que trata o artigo?

Nesse artigo será demonstrado o recurso Change Data Capture (CDC) presente no MS SQL Server 2008 e como utilizar Trigger para registrar as alterações ocorridas nas tabelas. Abordaremos a melhor forma de utilizá-los em auditorias no banco de dados.

Para que serve?

Para identificar as alterações ocorridas nas tabelas, fornecendo condições de comparação e recuperação de dados que foram excluídos e/ou alterados. Possibilita identificar também a inclusão de novos registros nas tabelas.

Em que situação o tema é útil?

Quando se deseja armazenar históricos das alterações ocorridas nos dados das tabelas. Esse recurso permite o registro das atividades ocorridas nas tabelas das bases de dados, fornecendo informações importantes para uma auditoria.[/lead]

Será abordado nesse artigo o Change Data Capture (CDC), recurso que permite o registro de todas as modificações ocorridas nos dados das tabelas. Será demonstrada também a utilização de trigger para o registro das alterações nos dados das tabelas.

O CDC foi implementado no SQL Server com o objetivo de identificar os dados que sofreram alterações. Sua principal utilização é no processo de ETL (Extração Transformação e Carga), processo responsável por efetuar a carga de dados em DW (Data Warehouse).

A sintaxe utilizada nos exemplos de Trigger é do SQL Server, mas a lógica da utilização de Trigger para armazenar informações sobre alterações nas tabelas pode ser utilizada em outros bancos de dados.

Nesse artigo vamos apresentar dois exemplos, o primeiro utilizando trigger, e o segundo, o recurso CDC.

[subtitulo]Exemplo usando Trigger[/subtitulo]

O SQL Server tem três tipos de trigger: DML, DDL e de Logon. Utilizaremos as triggers DML, que serão disparadas quando ocorrer a execução de instruções DML (insert, delete ou update) nas tabelas em que elas estiverem vinculadas.

Em nosso exemplo criaremos uma base de dados chamada Teste, uma tabela na qual conterá os dados de exemplo, que chamaremos de Cliente, uma tabela para registrar as alterações, que chamaremos de Cliente_Log, e três triggers que serão responsáveis por atualizar a tabela Cliente_Log sempre que ocorrer alterações nos dados da tabela Cliente (Figura 1).

Figura 1. Exemplo usando Trigger

Para criar o ambiente necessário para o exemplo, abra o Microsoft SQL Server Management Studio (SSMS) e efetue o logon no SQL Server. Feito isso, teremos uma tela semelhante a da Figura 2.

Figura 2. Tela do SSMS

Criaremos inicialmente uma base de dados chamada Teste e duas tabelas chamadas Cliente e Cliente_Log (Listagem 1).

A tabela Cliente é a nossa tabela principal, Cliente_Log é uma tabela que contém todos os campos da tabela principal, mais quatro campos adicionados no final. Esses campos serão utilizados para guardar informações sobre as operações ocorridas na tabela principal, são eles:

· Log Usuário – contém o usuário que estava conectado na instância do SQL Server quando ocorreu a operação;

· Log Data – data e hora que ocorreu a operação;

· Log Operação – tipo da operação que ocorreu, podendo ser Insert, Delete ou Update;

· Log ID – identificador sequencial.

Listagem 1. Código de criação da base de dados e das tabelas.

use master
  go
   
  -- Criar a base de dados
  create database Teste
  go
  use Teste
  go
   
   
  -- Criar a tabela Cliente
  CREATE TABLE dbo.Cliente
  (
        ID_Cliente int NOT NULL IDENTITY (1, 1),
        Nome varchar(50) NOT NULL,
        Idade int NOT NULL
  ) 
  GO
  ALTER TABLE dbo.Cliente -- Adiciona ID_Cliente como chave primaria
        ADD CONSTRAINT PK_Cliente_1 PRIMARY KEY CLUSTERED (ID_Cliente)
  GO
         
   
  -- Criar a tabela Cliente_Log
  CREATE TABLE dbo.Cliente_Log
  (
        ID_Cliente int NOT NULL,
        Nome varchar(50) NOT NULL,
        Idade int NOT NULL,
        Log_Usuario varchar(30) NOT NULL,
        Log_Data datetime NOT NULL,
        Log_Operacao char(1) NOT NULL,
        Log_ID int NOT NULL IDENTITY (1, 1)
  ) 
  GO
  ALTER TABLE dbo.Cliente_Log -- Adiciona LOG_ID como chave primaria
        ADD CONSTRAINT PK_Cliente_Log_1 PRIMARY KEY CLUSTERED (LOG_ID)
  GO
"

A exibição deste artigo foi interrompida :(
Este post está disponível para assinantes MVP

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?