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