Usando CDC e Trigger em Auditorias no SQL Server

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

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.

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.

Exemplo usando Trigger

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

Ap�s a criaç�o das tabelas, vamos criar tr�s triggers, uma para cada operaç�o (insert, update e delete), apresentadas na Listagem 2.

Observando o c�digo da Listagem 2, notamos que as diferenças entre cada SP s�o m�nimas, s�o as instruç�es indicando quando ser�o disparadas (after insert, after delete ou after update), a letra indicando qual o tipo de operaç�o (I, U ou D), e a origem das informaç�es (tabela inserted ou deleted). Como j� informamos, elas ser�o disparadas quando operaç�es de insert, update e delete s�o efetuadas na tabela Cliente.

A instruç�o after delete presente na trigger tr_d_Cliente_Log indica que essa trigger executar� a instruç�o SQL quando uma operaç�o de delete for executada na tabela Cliente. Dessa mesma forma, as outras triggers ser�o disparadas quando as demais operaç�es forem executadas, de acordo com a instruç�o presente em cada uma (after insert e after update).

As instruç�es SQL presentes nas triggers, quando executadas, inserem registros na tabela Cliente_Log fazendo um Select na tabela inserted ou deleted. Essas tabelas s�o utilizadas internamente pelo SQL Server para fazer o controle das operaç�es DML, e podem ser referenciadas nas triggers. A tabela inserted cont�m os registros que est�o sendo inseridos na tabela, enquanto deleted armazena os registros que est�o sendo apagados.

Analisando a instruç�o Select da trigger tr_d_Cliente_Log, verificamos que est� sendo feita uma consulta nas informaç�es que estavam na tabela Cliente antes de serem apagadas, utilizando a tabela interna deleted. Posteriormente � executada a instruç�o Insert que insere na tabela Cliente_Log as informaç�es retornadas da consulta da tabela interna deleted.

As instruç�es presentes nas triggers fazem uso das funç�es getdate() para obter a data e hora atual, e da system_user para obter o usu�rio que est� conectado na inst�ncia do SQL Server.

Listagem 2. C�digo de criaç�o de trigger.

USE [Teste]
  GO
   
   
  CREATE trigger [dbo].[tr_d_Cliente_Log] ON [dbo].[Cliente]
  after delete 
  as 
        insert  Cliente_Log
        select *,system_user,getdate(),'D' from deleted
   
  GO
   
   
  CREATE trigger [dbo].[tr_i_Cliente_Log] ON [dbo].[Cliente]
  after insert 
  as 
        insert  Cliente_Log
        select *,system_user,getdate(),'I' from inserted
   
  GO
   
   
  CREATE trigger [dbo].[tr_u_Cliente_Log] ON [dbo].[Cliente]
  after update 
  as 
        insert  Cliente_Log
        select *,system_user,getdate(),'U' from deleted
   
  GO

Para testar a trigger de insert (tr_i_Cliente_Log), vamos inserir tr�s registros na tabela Cliente, conforme a Listagem 3.

A Figura 3 mostra os registros nas tabelas ap�s a inserç�o dos tr�s registros na tabela Cliente. Nota-se na tabela Cliente_Log que os campos Log_Usuario, Log_Data e Log_Operaç�o foram preenchidos pela trigger. Esses campos cont�m informaç�es importantes sobre o que ocorreu na tabela, podendo contribuir significativamente em um processo de auditoria no banco de dados.

Listagem 3. Inserir tr�s registros na tabela Cliente.

USE Teste
  go
   
  -- Mostra o conteudo das tabelas antes do Insert
  select      * from Cliente
  select      * from Cliente_Log
   
  -- Insere 3 registros
  insert      Cliente (Nome, Idade) values ('Tulio',35)
  insert      Cliente (Nome, Idade) values ('Jo�o',25)
  insert      Cliente (Nome, Idade) values ('Maria',15)
   
  -- Mostra o conteudo das tabelas depois do Insert
  select      * from Cliente
  select      * from Cliente_Log

Figura 3. Conte�do das tabelas ap�s a inserç�o de tr�s registros.

Para exemplificar as operaç�es de exclus�o e alteraç�o de registros, execute o c�digo da Listagem 4.

A Figura 4 mostra os registros nas tabelas ap�s a exclus�o e alteraç�o dos registros na tabela Cliente.

Observando o campo Log_Operaç�o da tabela Cliente_Log, temos a informaç�o do tipo de operaç�o que ocorreu quando foi inserido o registro, sendo �I� para inserç�o, �D� para exclus�o e �U� para atualizaç�o.

Note que a tabela Cliente_Log cont�m todos os registros que estavam antes das operaç�es de exclus�o e atualizaç�o na tabela Cliente. Por exemplo, na tabela Cliente temos o nome �Fulana�, se olharmos na tabela Cliente_Log, consta o nome �Maria�, ou seja, o registro antes da operaç�o de atualizaç�o.

Vale ressaltar que quando utilizamos triggers para inserir informaç�es em tabelas estamos utilizando mais recursos do servidor, isto �, uma operaç�o que iria inserir um registro na base de dados, nesse caso, passa a inserir dois registros. Por isso, � importante fazer uma an�lise de quais tabelas s�o realmente importantes e necessitam de uma tabela de Log.

Listagem 4 . Excluir e alterar registros na tabela Cliente.

USE Teste
  go
   
  -- Mostra o conteudo das tabelas antes do Delete e Update
  select      * from Cliente
  select      * from Cliente_Log
   
  -- Excluie um registro
  delete      Cliente where ID_Cliente = 2
   
  -- Altera um registro
  update      Cliente set Nome = 'Fulana' where ID_Cliente = 3
   
  -- Mostra o conteudo das tabelas depois do Delete e Update
  select      * from Cliente
  select      * from Cliente_Log

Figura 4. Conte�do das tabelas ap�s a exclus�o e alteraç�o de registros.

Exemplo usando o CDC

O Change Data Capture (CDC) utiliza os log (arquivos .ldf) para identificar as alteraç�es ocorridas nas tabelas. Quando uma operaç�o DML � executada, essas informaç�es s�o salvas primeiramente nos arquivos de log e posteriormente na tabela de origem.

Atrav�s de um processo de captura, as informaç�es sobre as alteraç�es ocorridas na tabela que est�o nos arquivos de log s�o lidas e salvas tamb�m em tabelas de �hist�rico do CDC� (Figura 5).

Figura 5. Funcionamento do CDC.

Em nosso exemplo sobre o CDC, iremos criar uma base chamada TesteCDC, adicionar uma tabela chamada Fornecedor, habilitar a base para utilizaç�o do CDC e incluir a tabela Fornecedor no processo de CDC.

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

Agora criaremos uma base chamada TesteCDC e a tabela Fornecedor, como apresenta o c�digo da Listagem 5.

Listagem 5. C�digo da criaç�o da base e tabela.

use master
  go
   
  -- Criar a base de dados
  create database TesteCDC
  go
  use TesteCDC
  go
   
   
  -- Criar a tabela Fornecedor
  CREATE TABLE dbo.Fornecedor
  (
        ID_Fornecedor int NOT NULL IDENTITY (1, 1),
        Nome varchar(50) NOT NULL,
        Telefone int NOT NULL
  ) 
  GO
  ALTER TABLE dbo.Fornecedor -- Adiciona chave primaria
  ADD CONSTRAINT PK_Fornecedor_1 PRIMARY KEY CLUSTERED (ID_Fornecedor)
  GO

Ap�s a criaç�o da base de dados e da tabela, usaremos a stored procedure sp_cdc_enable_db para habilitar a base de dados para utilizar o CDC, e a SP sp_cdc_enable_table para incluir a tabela no processo CDC (Listagem 6).

Listagem 6. C�digo para habilitar o CDC e incluir a tabela.

use TesteCDC
  go
   
  -- Habilitando a base de dados
  exec sys.sp_cdc_enable_db
  go
   
   
  -- Adicionando a tabela
  exec sp_cdc_enable_table
        @source_schema = 'dbo',
        @source_name = 'Fornecedor',
        @role_name = 'CDC_Role',
        @supports_net_changes = 1
  Go

Para verificar quais bases de dados est�o com o recurso CDC habilitado, podemos fazer uma consulta na tabela sys.Database, buscando pelo campo is_cdc_enabled igual a 1. Para verificar quais tabelas est�o inclu�das no CDC, devemos consultar a tabela sys.Tables, buscando pelo campo is_tracked_by_cdc igual a 1. A Listagem 7 mostra as duas consultas, tendo o resultado mostrado na Figura 6.

Listagem 7. Verificaç�o se o CDC esta habilitado.

use master
  go
   
  select      name,
              is_cdc_enabled
  from        sys.databases
  where       is_cdc_enabled = 1
   
   
  -- Tabela
  use TesteCDC
  go
   
  select      name,
              is_tracked_by_cdc
  from        sys.tables
  where       is_tracked_by_cdc = 1

Figura 6. Resultado da verificaç�o se o CDC est� habilitado.

Quando executamos a SP sp_cdc_enable_db para habilitar a base de dados para utilizaç�o do CDC, s�o criados os seguintes objetos no SQL Server: um Schema chamado cdc, um usu�rio chamado cdc e cinco tabelas de sistemas que conter�o informaç�es sobre o CDC (Figura 7).

Quando executamos a SP sp_cdc_enable_table para incluir a tabela no CDC, � criada uma nova tabela de sistema com o nome da tabela de origem contendo no final a identificaç�o _CT. Essa tabela cont�m todos os campos da tabela de origem mais cinco campos de controle, conforme a Figura 8. Em nosso exemplo tamb�m ser� criada, se n�o existir, uma Role chamada CDC_Role, que ser� respons�vel por permitir o acesso �s informaç�es nas tabelas _CT quando o usu�rio n�o tiver permiss�o de db_owner.

Ao executar a SP sp_cdc_enable_table pela primeira vez, ser�o criados dois JOBs (Figura 9). No nosso exemplo, o primeiro � chamado cdc_dbo_TesteCDC_capture, o qual ser� iniciado junto com a inst�ncia do SQL Server e ser� respons�vel pela leitura das informaç�es sobre alteraç�es que foram registradas nos arquivos de log. As alteraç�es identificadas ser�o salvas na tabela de sistema que foi criada com a identificaç�o _CT. O segundo JOB criado � o cdc_TesteCDC_cleanup, respons�vel por apagar as informaç�es antigas das tabelas _CT. Por default, esse JOB � executado diariamente �s 2:00 hs, e a cada execuç�o s�o apagados os registros das tabelas _CT que tenham mais de 72 horas (antigos). Caso seja necess�rio manter por mais tempo as informaç�es nas tabelas _CT, podemos alterar o tempo de retenç�o utilizando a SP sp_cdc_change_job. O tempo m�ximo permitido � de 100 anos, o valor para o par�metro da SP chamado retention tem que ser informado em minutos. A Listagem 8 mostra a alteraç�o para 100 anos. Uma boa pr�tica � manter um per�odo pequeno (um m�s, dependendo da quantidade de transaç�es) e criar uma rotina para descarte dos registros antigos, salvando-os em outro SQL Server ou em arquivos texto (.txt), para o caso de ser necess�ria a consulta dessas informaç�es no futuro.

Figura 7. Objetos criados quando habilitado o CDC.

Figura 8. Tabela criada pela SP sp_cdc_enable_table.

Figura 9. JOB criado quando inclu�do a tabela.

Listagem 8. Alterando o tempo de retenç�o.

use TesteCDC
  go
   
  exec sp_cdc_change_job @job_type='cleanup', @retention=52494800
  go

Ap�s habilitar a base de dados para utilizar o CDC e incluir a tabela de exemplo Fornecedor, iremos inserir tr�s registros nesta tabela e depois selecionar os registros nas tabelas Fornecedor e cdc.dbo_Fornecedor_TC para mostrar o que ocorreu (veja o c�digo na Listagem 9).

A Figura 10 mostra o resultado obtido ap�s a execuç�o do c�digo da Listagem 9.

Para realizar consultas nas tabelas de sistemas terminadas em _CT podemos tamb�m utilizar a funç�o (FN) cdc.fn_get_net_changes_dbo_Fornecedor. Essa funç�o � criada no momento em que adicionamos a tabela Fornecedor no CDC. A Listagem 10 demonstra a utilizaç�o da FN.

Listagem 9. Operaç�es na tabela de fornecedor.

USE TesteCDC
  go
   
  -- Mostra o conteudo das tabelas antes do Insert
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT
   
  -- Insere 3 registros
  insert      Fornecedor(Nome, Telefone) values ('Tulio',1111)
  insert      Fornecedor (Nome, Telefone) values ('Jo�o',2222)
  insert      Fornecedor (Nome, Telefone) values ('Maria',3333)
   
  -- Mostra o conteudo das tabelas depois do Insert
  -- Esperar 5 segundos antes de executar essa parte
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT

Figura 10. Conte�do das tabelas Fornecedor e dbo_Fornecedor_CT.

Listagem 10. Usando a FN cdc.fn_get_net_changes_dbo_Fornecedor.

use TesteCDC
  go
   
  declare @from_lsn binary(10),
              @to_lsn binary(10)
              
  set @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Fornecedor') -- Menor data
  set @to_lsn   = sys.fn_cdc_get_max_lsn() -- Maior data
   
  select * from
   cdc.fn_cdc_get_net_changes_dbo_Fornecedor(@from_lsn, @to_lsn, 'all');

Se observarmos a Figura 10, veremos que o primeiro resultado (tabela Fornecedor) retornou os registros que foram inseridos. O segundo resultado retornou o mesmo conte�do da tabela Fornecedor mais algumas informaç�es adicionais. Dentre estas informaç�es, as mais importantes s�o __$start_lsn, que cont�m a data e a hora que a operaç�o foi executada, e __$operation, que cont�m o tipo de operaç�o.

Para visualizar a data que est� no campo __$start_lsn podemos fazer uso da funç�o sys.fn_cdc_map_lsn_to_time.

A Tabela 1 mostra o significado dos c�digos do campo __$operation.

C�digo	Descriç�o
1	Exclus�o
2	Inserç�o
3	Antes da alteraç�o
4	Ap�s a alteraç�o

Tabela 1. C�digos do campo __$operation.

Para entender melhor o exemplo, vamos alterar um registro e excluir outro (veja a Listagem 11). Na Figura 11 apresentamos os resultados ap�s a alteraç�o e exclus�o dos registros. O primeiro resultado mostra a tabela Fornecedor, que cont�m agora dois registros. Para saber o que ocorreu nessa tabela, recorremos � tabela cdc.dbo_Fornecedor_CT.

Observando cdc.dbo_Fornecedor_CT, temos nos tr�s primeiros registros a informaç�o de inclus�o, pois no campo __$operation est� presente o c�digo 2. O conte�do inserido � o que consta nos �ltimos campos desses registros (ID_Fornecedor, Nome e Telefone).

O quarto e quinto registro da tabela cdc.dbo_Fornecedor_CT cont�m informaç�es sobre as alteraç�es ocorridas na tabela Fornecedor. O campo __$operation com c�digo 3 informa que o registro cont�m os dados que existiam na tabela Fornecedor antes da alteraç�o, e no quinto registro (__$operation = 4) os dados ap�s a alteraç�o. O CDC n�o registra o usu�rio que executou a instruç�o DML na tabela.

Listagem 11. Alteraç�o e exclus�o de registros.

USE TesteCDC
  go
   
  -- Alteracao
  update dbo.Fornecedor set Nome = 'Tulio Rosa' where ID_Fornecedor = 1
   
  -- Exclusao
  delete dbo.Fornecedor where ID_Fornecedor = 2
   
  -- Mostra o conteudo das tabelas depois da alteracao e exclusao
  -- Esperar 5 segundos antes de executar essa parte
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT

Figura 11. Resultado ap�s a alteraç�o e exclus�o dos registros.

A Listagem 12 mostra como fazer para adicionar o campo Estado na tabela Fornecedor.

Como foi habilitado o CDC na tabela Fornecedor, ao adicionar um novo campo as informaç�es referentes a alteraç�es (instruç�es DDL) s�o armazenadas na tabela cdc.ddl_history. A Listagem 13 mostra como efetuar a consulta nessa tabela, e o resultado pode ser conferido na Figura 12.

Listagem 12 . Adicionando o campo Estado.

use TesteCDC
  go
   
  -- Adicionando o campo estado
  alter table dbo.Fornecedor add
        Estado char(2)
  GO

Listagem 13 . Consulta nas tabelas ap�s adicionar o novo campo.

use TesteCDC
  go
   
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT
   
  select      OBJECT_NAME(source_object_id) as 'Tabela',
              OBJECT_NAME(OBJECT_ID) as 'Tabela_CT',
              ddl_command as 'DDL',
              ddl_time as 'Data'
  from        cdc.ddl_history

Figura 12. Resultado das consultas ap�s adicionar o novo campo.

Como pode ser verificado na Figura 12, o novo campo foi adicionado na tabela Fornecedor, mas n�o foi adicionado na tabela Fornecedor_CT. Para resolver esse problema a forma mais simples � retirar e adicionar novamente a tabela Fornecedor no CDC, conforme a Listagem 14. � importante observar que quando retiramos uma tabela do CDC, a tabela corespondente _CT � apagada, perdendo todo o seu conte�do. Ent�o, se existe a intenç�o de manter os dados das tabelas _CT, os registros dessas tabelas precisam ser copiados para outra tabela antes da operaç�o de retirar a tabela do CDC.

Listagem 14 . Retira e adiciona novamente a tabela Fornecedor.

use TesteCDC
  go
   
  -- Retirando a tabela
  exec sp_cdc_disable_table 
        @source_schema = 'dbo',
        @source_name = 'Fornecedor',
        @capture_instance = 'all'
  go
   
  -- Adicionando a tabela
  exec sp_cdc_enable_table
        @source_schema = 'dbo',
        @source_name = 'Fornecedor',
        @role_name = 'CDC_Role',
        @supports_net_changes = 1
  go

Para verificar a utilizaç�o do novo campo, iremos inserir um registro e alter�-lo, de acordo com a Listagem 15. O novo resultado pode ser conferido na Figura 13.

Listagem 15. Efetuando alteraç�es no novo campo.

use TesteCDC
  go
   
  -- Inserindo
  Insert Fornecedor(Nome, Telefone,Estado) values ('Fabiola',5555,'GO')
   
  -- Alteracao
  update dbo.Fornecedor set Estado = 'DF' where ID_Fornecedor = 3

Figura 13. Resultado da alteraç�o do novo campo.

Para melhorar a visualizaç�o do hist�rico (tabelas _CT), podemos criar uma stored procedure para formatar o resultado destas tabelas. A Listagem 16 mostra o c�digo de criaç�o da SP. Ela recebe como par�metros o nome da tabela, a data inicial e a data final. Estas datas s�o referentes ao per�odo em que se deseja visualizar as alteraç�es. A Listagem 17 mostra a sua utilizaç�o. Observando o resultado na Figura 14, temos a data em que ocorreu a operaç�o, o tipo da operaç�o e os campos do registro da tabela de origem.

A Tabela 2 mostra o significado dos c�digos do campo Operaç�o, da Figura 14.

Operaç�o	Descriç�o
I	Inserido
D	Apagado
UA	Dados antes da alteraç�o
UD	Dados ap�s a alteraç�o

Tabela 2. C�digos do campo Operaç�o.

Listagem 16. Criaç�o da SP para retornar o resultado formatado.

create proc cdc.s_historico @tabela varchar(100), @data_inicial varchar(23), @data_final varchar(23)
  as
   
  exec
  ('
  select      sys.fn_cdc_map_lsn_to_time(__$start_lsn) as Data,
              case __$operation 
              when 1 then ''D''
              when 2 then ''I''
              when 3 then ''UA''
              when 4 then ''UD''
              end as Operacao,
              *
  into  #temp
  from  cdc.' + @tabela + ' 
  where sys.fn_cdc_map_lsn_to_time(__$start_lsn)
              between ''' + @data_inicial + ''' and ''' + @data_final + ''' 
   
  alter table #temp drop column __$start_lsn
  alter table #temp drop column __$operation
  alter table #temp drop column __$update_mask
  alter table #temp drop column __$seqval
  alter table #temp drop column __$end_lsn
   
  select      *
  from  #temp
  order by Data
  ')

Listagem 17. Utilizaç�o da SP cdc.s_historico.

use TesteCDC
  go
   
  exec cdc.s_historico 'dbo_Fornecedor_CT','2009-11-01','2009-11-30'
  go

Figura 14. Resultado da SP cdc.s_historico.

Consideraç�es sobre Trigger e CDC

Como podemos observar, tanto a utilizaç�o dos recursos de Triggers como CDC inserem registros em tabelas na base de dados, e com isso temos o aumento de utilizaç�o de recursos do servidor onde est� instalado a inst�ncia do SQL Server.

A utilizaç�o do CDC � mais vantajosa que as Triggers devido ao fato das atualizaç�es serem buscadas dos arquivos de log, de forma ass�ncrona, enquanto as Triggers fazem a inclus�o dos registros em todas as tabelas de forma s�ncrona.

Outra vantagem do CDC � que podemos par�-lo em momentos cr�ticos, isto �, onde esteja ocorrendo um grande n�mero de transaç�es (atividade intensa no banco de dados), e voltar a atualizar as tabelas _CT quando a carga no servidor estiver mais baixa, sem perda de informaç�es.

Para suspender temporariamente o CDC, utilizamos a SP sys.sp_cdc_stop_job, e para voltar a utilizar o CDC, utilizamos a SP sys.cdc_start_job. � importante saber que o arquivo de log aumentar� enquanto o serviço estiver suspenso, uma vez que as informaç�es sobre as alteraç�es nos dados das tabelas s�o salvas nos arquivos de log, e ficam dispon�veis at� o CDC fazer a leitura. Ap�s voltar a utilizar o CDC as informaç�es que estavam nos arquivos de log s�o liberadas para serem descartadas ou inclu�das em backup.

Conclus�o

Demonstramos nesse artigo duas formas de armazenar informaç�es sobre as operaç�es (insert, update e delete) que ocorrem nas tabelas, atrav�s de Triggers e CDC. Podemos utilizar essas informaç�es para entender o que ocorreu na tabela e quando, o que � muito �til em um processo de auditoria.

Imagine o seguinte cen�rio: um funcion�rio da empresa que tenha acesso ao sistema de contas a pagar e receber altera de forma fraudulenta o valor que a empresa tem a receber de um determinado cliente. Atrav�s da utilizaç�o dos recursos demonstrados nesse artigo � poss�vel identificar quando foi feita a alteraç�o, por quem e ainda teremos condiç�es de corrigir o valor alterado, voltando ao valor inicial.

Para concluir, vale destacar que a utilizaç�o dos recursos de triggers ou do CDC aumentar�o o consumo de recursos do servidor onde est� instalado a inst�ncia do SQL Server. Por isso, tenha cuidado ao utilizar estes recursos e evite que o desempenho do banco seja prejudicado.

Confira tamb�m

Cursos de SQL e Banco de Dados

Curso

Curso de SQL Server

Curso

Guia Completo de SQL Server

Guia

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

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.