Audit Script Wizard (SQL Server 2008) II

After posting my article audit Wizard where you create an additional table for each table to be audited, in this issue propose another solution which is created only once a single table and persist data in a xml field, the differences are many, but you have to discover which are the scope and mainly depends on the culture to use xml.

Best regards

See also: Audit Script Wizard (SQL Server 2008)

http://sqldimention.blogspot.com/2010/09/audit-wizard-script.html

Después de publicar mi articulo de Wizard de auditoria donde se crea una tabla adicional por cada tabla a auditar, en esta entrega propongo otra solución donde se crea una única vez una sola tabla y los datos persisten en un campo xml, las diferencias son muchas, pero hay que evaluar cuales son los alcances y sobre todo depende de la cultura a usar xml.

Saludos cordiales

Ver tambien:Audit Wizard Script (SQL server 2008)

http://sqldimention.blogspot.com/2010/09/audit-wizard-script.html

-- =============================================

-- Author: Skynet Silverstar

-- Create date:

-- Description: Script maker where you conform

-- audit info on a specific SQL table

-- =============================================

-- Creador de script donde conforma

-- Informacion de auditoria de

-- una o mas tablas especificas

-- sobre una unica tabla de auditoria

-- =============================================

DECLARE @object_name VARCHAR(50)

DECLARE @column_name VARCHAR(50)

DECLARE @is_nullable VARCHAR(50)

DECLARE @type VARCHAR(50)

DECLARE @max_length INT

DECLARE @precision INT

DECLARE @scale INT

DECLARE @Audit_name VARCHAR(255)

DECLARE @trigger_name VARCHAR(255)

-- The target table name

-- El nombre de la tabla

SET @object_name = 'factura'

--Here change the name of tables and triggers if you want

--Aqui cambie el nombre de las tablas y disparadore si usted quiere!

SET @Audit_name = 'AUDIT_Table'

SET @trigger_name = 'AUDIT_TR_' + @object_name

IF EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME IN ( @trigger_name) )

BEGIN

PRINT '---------------------------------------------------------'

PRINT '--Informe de proceso con error ATENCION!'

PRINT '---------------------------------------------------------'

PRINT '--El trigger ' + @trigger_name + ' ya existe, verificar y volver a ejecutar el proceso'

PRINT '--'

PRINT '--Sugerido hacer backup de la tabla de auditoria y base de datos'

PRINT '--copiar el resultado y descomentar los comandos siguientes y ejecutar F5'

PRINT '--DROP TRIGGER ' + @trigger_name

PRINT ''

PRINT '---------------------------------------------------------'

PRINT '--Warning!! proccess info error'

PRINT '---------------------------------------------------------'

PRINT '--Trigger ' + @trigger_name + ' already exists, Verify and run again the script'

PRINT '--'

PRINT '--Make a backup and save the information'

PRINT '--Execute the next lines if you want delete the old schema'

PRINT '--DROP TRIGGER ' + @trigger_name

END ELSE BEGIN

SET NOCOUNT ON

PRINT '-------- Script de creacion de tabla de auditoria ' + @Audit_name+ ' si existe--------'

PRINT '-------- Make the script to make the audit table ' + @Audit_name+ ' IF EXISTs--------'

PRINT '-------- AUTOR: ' + USER_NAME()

PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

PRINT 'SET ANSI_NULLS ON'

PRINT 'GO'

PRINT ''

PRINT 'SET QUOTED_IDENTIFIER ON'

PRINT 'GO'

PRINT ''

PRINT 'SET ANSI_PADDING ON'

PRINT 'GO'

PRINT ''

PRINT 'IF NOT EXISTS( SELECT * FROM SYS.OBJECTS WHERE NAME IN ( ''' + @Audit_name + ''') )'

PRINT 'BEGIN'

PRINT CHAR(9) + 'CREATE TABLE ' + @Audit_name + ' ('

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_ID] ' + CHAR(9) + ' INT IDENTITY(1,1) ' + CHAR(9) + ','

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_ACTION] ' + CHAR(9) + ' CHAR(1) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_SPID] ' + CHAR(9) + ' INT ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_DATE] ' + CHAR(9) + ' DATETIME ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_USER] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_APLICATION] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_HOST] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' NOT NULL,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_TIMESTAMP] ' + CHAR(9) + ' TIMESTAMP ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_HASH] ' + CHAR(9) + ' BIGINT ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_TABLENAME] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'

PRINT CHAR(9) + CHAR(9) + ' [AUDIT_XML_CONTENT] ' + CHAR(9) + ' XML ' + CHAR(9) + ' ,'

PRINT CHAR(9) + ' CONSTRAINT [' + @Audit_name + '_PK] PRIMARY KEY CLUSTERED '

PRINT CHAR(9) + ' ('

PRINT CHAR(9) + ' [AUDIT_ID] ASC'

PRINT CHAR(9) + ' )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'

PRINT CHAR(9) + ' ) ON [PRIMARY]'

PRINT 'END '

PRINT ' '

PRINT ' GO'

PRINT ' '

PRINT ' SET ANSI_PADDING OFF'

PRINT ' GO'

/********************************************************************************

creacion del disparador

Make a trigger

********************************************************************************/

PRINT '-------- Script de creacion de triger de auditoria AUDIT_TR_' + @object_name+ '--------'

PRINT '-------- Make a script of Audit trigger named AUDIT_TR_' + @object_name+ '--------'

PRINT '-------- AUTOR: ' + USER_NAME()

PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

PRINT 'SET ANSI_NULLS ON'

PRINT 'GO'

PRINT ''

PRINT 'SET QUOTED_IDENTIFIER ON'

PRINT 'GO'

PRINT ''

PRINT 'SET ANSI_PADDING ON'

PRINT 'GO'

PRINT ''

PRINT 'CREATE TRIGGER ' + @trigger_name + ' '

PRINT ' ON ' + @object_name + ' '

PRINT ' AFTER INSERT, UPDATE, DELETE'

PRINT ' AS'

PRINT ' BEGIN'

PRINT CHAR(9) + ' -- Verifico que tipo de accion se va a realizar'

PRINT CHAR(9) + ' -- U : almaceno el dato a actualizar'

PRINT CHAR(9) + ' -- D : almaceno el dato a ser eliminado'

PRINT CHAR(9) + ' -- I : almaceno el dato a ser insertado'

PRINT ''

PRINT CHAR(9) + ' -- Verify the type of operatio to do'

PRINT CHAR(9) + ' -- U : Save on update'

PRINT CHAR(9) + ' -- D : Save on delete'

PRINT CHAR(9) + ' -- I : save on insert'

PRINT CHAR(9) + 'DECLARE @ACTION CHAR(1)'

PRINT CHAR(9) + 'DECLARE @XMLDATA XML'

PRINT CHAR(9) + 'DECLARE @HASHDATA BIGINT'

PRINT ''

PRINT CHAR(9) + 'IF EXISTS(SELECT * FROM DELETED) AND EXISTS(SELECT * FROM INSERTED)'

PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''U'''

PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM DELETED) '

PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''D'' '

PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM INSERTED)'

PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''I'' '

PRINT ''

DECLARE @FIELDS VARCHAR(8000)

SET @FIELDS = ''

DECLARE Table_cursor CURSOR FOR

SELECT

c.name AS column_name

FROM

sys.objects AS o

INNER JOIN

sys.columns AS c

ON ( o.object_id = c.object_id)

WHERE

o.type = 'U'

AND o.name = @object_name

ORDER BY

o.name ,

column_id

OPEN Table_cursor

FETCH NEXT FROM Table_cursor

INTO @column_name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @FIELDS = @FIELDS + '' + ' [' + @column_name + '] ,'

FETCH NEXT FROM Table_cursor

INTO @column_name

END

CLOSE Table_cursor

DEALLOCATE Table_cursor

SET @FIELDS = substring(@FIELDS , 1 , len(@FIELDS ) - 1)

PRINT CHAR(9) + 'SET NOCOUNT ON'

PRINT CHAR(9) + 'IF (@ACTION IN ( ''I'')) BEGIN'

PRINT CHAR(9) + CHAR(9) + 'SET @XMLDATA = CAST((SELECT ' + @FIELDS + ', BINARY_CHECKSUM(' + @FIELDS + ') AS [AUDIT_HASH] FROM INSERTED FOR XML AUTO, Elements) AS XML)'

PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '

PRINT CHAR(9) + CHAR(9) + '( [AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST], [AUDIT_HASH], [AUDIT_SPID], [AUDIT_TABLENAME] , [AUDIT_XML_CONTENT]) VALUES'

PRINT CHAR(9) + CHAR(9) + '( @ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() , 0 , @@SPID, '

PRINT CHAR(9) + CHAR(9) + '''' + @object_name + ''', @XMLDATA ) '

PRINT CHAR(9) + 'END'

PRINT CHAR(9) + 'ELSE IF (@ACTION IN (''U'', ''D'')) BEGIN'

PRINT CHAR(9) + CHAR(9) + 'SET @XMLDATA = CAST((SELECT ' + @FIELDS + ', BINARY_CHECKSUM(' + @FIELDS + ') AS [AUDIT_HASH] FROM DELETED FOR XML AUTO, Elements) AS XML)'

PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '

PRINT CHAR(9) + CHAR(9) + '( [AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST], [AUDIT_HASH], [AUDIT_SPID], [AUDIT_TABLENAME] , [AUDIT_XML_CONTENT]) VALUES'

PRINT CHAR(9) + CHAR(9) + '( @ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() , 0 , @@SPID, '

PRINT CHAR(9) + CHAR(9) + '''' + @object_name + ''', @XMLDATA ) '

PRINT CHAR(9) + 'END'

PRINT 'END'

PRINT '---------------------------------------------------------'

PRINT '--Informe de proceso'

PRINT '---------------------------------------------------------'

PRINT '--Se generò sobre tabla ' + @object_name + ' el trigger ' + @trigger_name + ' '

PRINT '--'

PRINT '--Copie el contenido de resultado a un archivo. sql'

PRINT '--Nombre: xxx_creacion_auditoria_' + @object_name + '.sql'

PRINT ''

PRINT '---------------------------------------------------------'

PRINT '--Proccess info'

PRINT '---------------------------------------------------------'

PRINT '--With the target table ' + @object_name + ' made the trigger ' + @trigger_name + ' '

PRINT '--'

PRINT '--Copy the result to a new query and save '

PRINT '--Name: xxx_Create_Audit_' + @object_name + '.sql'

END