quinta-feira, 20 de novembro de 2014

Loggin changes in table, when CDC is not an option


The script bellow, creates a test database, and a table named "acesso" with a trigger that logs all changes made to the "acesso" table into a historic table named "acessohist".

Please note that all changes are logged in as a XML value. That is because a tadicional logging table might be a mantainance issue, because, once you have a brand new column (or change a columns data type or name, for that matter) in your database, you might need to review the log table and triggers.

Of course, this is for all of you that use SQL SERVER Standard edition, because on Enterprise, you have CDC.

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 


Nenhum comentário:

Postar um comentário

Leave your comment here!