quinta-feira, 20 de novembro de 2014

Log de alterações de uma tabela, quando CDC não está disponível ;)


O script abaixo, cria um banco de dados de teste, e uma tabela chamada "Acesso" com uma trigger que registra todas as alterações feitas na tabela "Acesso" em uma tabela histórico chamado "acessohist".

Por favor, note que todas as mudanças estão sendo logadas como um valor XML. Isso porque uma tabela de log tadicional pode ser um problema de manutenção pois uma vez que você tem uma nova coluna (ou altera um tipo de dado ou nome de coluna) em seu banco de dados, pode ser necessário rever a tabela de log e gatilhos .

Claro, isto é para todos vocês que usam Standard Edition, porque no Enterprise você tem CDC. Não custa lembrar que triggers podem apresentar problemas de desempenho.

USE [master] 

go 

/****** Table Log Example ******/ 
CREATE DATABASE [dbTestTrigger] 

go 

USE [dbTestTrigger] 

go 

CREATE TABLE [dbo].acesso 
  ( 
     [superior]    [VARCHAR](120) NOT NULL, 
     [propriedade] [VARCHAR](120) NOT NULL, 
     [descricao]   [VARCHAR](140) NULL, 
     [especial]    [CHAR](1) NOT NULL DEFAULT ('F'), 
     [idacesso]    [INT] IDENTITY(1, 1) NOT NULL, 
     PRIMARY KEY CLUSTERED ( [idacesso] ASC ) 
  ) 
ON [PRIMARY] 

go 

CREATE TABLE [dbo].[acessohist] 
  ( 
     [idacessohist] [INT] IDENTITY(1, 1) NOT NULL, 
     [data]         [DATETIME] NULL DEFAULT (Getdate()), 
     [texto]        [XML] NULL, 
     PRIMARY KEY CLUSTERED ( [idacessohist] ASC ) 
  ) 
ON [PRIMARY] 
textimage_on [PRIMARY] 

go 

CREATE TRIGGER trglogaccessdef 
ON [dbo].[acesso] 
after INSERT, DELETE, UPDATE 
AS 
  BEGIN 
      SET nocount ON; 

      DECLARE @Text XML; 

      SET @Text = (SELECT *, 
                          SYSTEM_USER AS LoginName 
                   FROM   deleted 
                   FOR xml auto) 

      IF @Text IS NOT NULL 
        BEGIN 
            INSERT INTO [dbo].[acessohist] 
                        (texto) 
            VALUES      (@Text) 
        END 

      SET @Text = NULL 
      SET @Text = (SELECT *, 
                          SYSTEM_USER AS LoginName 
                   FROM   inserted 
                   FOR xml auto) 

      IF @Text IS NOT NULL 
        BEGIN 
            INSERT INTO [dbo].[acessohist] 
                        (texto) 
            VALUES      (@Text) 
        END 
  END 

go 

--

Rodrigo 

Nenhum comentário:

Postar um comentário

Leave your comment here!