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

SIMPLE CURSOR EXAMPLE

/*

SIMPLE CURSOR EXAMPLE

Generation of a table with nested entities using cursors,

Logical entities are generated in a hierarchical

EJEMPLO SIMPLE DE CURSORES Entities dictionary

Generacion de una tabla con entidades anidades usando cursores,

Se generan entidades logicas en forma jerarquica

*/

--Declare a table
DECLARE @Entionary TABLE  ( 
 [ID]   INT IDENTITY(1,1)  ,
 [Type]   VARCHAR(25)    , 
 [PhisicName]  VARCHAR(255)  ,
 [LogicName]  VARCHAR(255)  ,
 [Parent]  INT   ,
 [Synonym]  INT   ,
 [Help]   xml     
)

-- Var where I take the Identity number of Patern row
DECLARE @RootID   INTEGER  

-- Name of table
DECLARE @object_name VARCHAR(255)

-- Logical name... if you like other name is free to change it
DECLARE @LogicName  VARCHAR(255)

-- Modify Date of  Table, only to show other field
DECLARE @modify_date DATETIME

--Insert the root Row where depends every table name
INSERT INTO 
 @Entionary  (
     [Type]  , 
     [PhisicName] ,  
     [LogicName] ,  
     [Parent] , 
     [Synonym] 
    ) values (  
     'Entity' , 
     'Generic' , 
     'Generic' , 
     NULL  , 
     NULL
    )
    
-- Take the Identity value 
SET @RootID = @@IDENTITY

--Well here declare a cursor, only that
DECLARE Table_cursor CURSOR FOR 
 SELECT 
  name  AS [object_name], 
  modify_date
 FROM 
  sys.objects 
 WHERE 
  type = 'U' 
 ORDER BY 
  name
--Open the cursor 
OPEN Table_cursor
--Fetch the first
FETCH NEXT FROM Table_cursor 
--Use the field values in the declared vars
INTO @object_name , @modify_date
--Loop not eof 
WHILE @@FETCH_STATUS = 0
BEGIN
 --Insert table entity
 SET @LogicName = 'Logic_' + @object_name 
 SET @LogicName = @LogicName + '('+convert( Varchar(10), @modify_date)+')'
 INSERT INTO @Entionary (
     [Type]  , 
     [PhisicName] , 
     [LogicName] , 
     [Parent] , 
     [Synonym]
    ) values (  
     'SubEntity' , 
     @object_name , 
     @LogicName , 
     @RootID , 
     NULL 
    )
 --For each table insert every field
 INSERT INTO @Entionary (
     [Type]  , 
     [PhisicName] , 
     [LogicName] , 
     [Parent] , 
     [Synonym]
    ) 
    select 
     'Field'   ,
     c.name   , 
     'LogicField_' + c.name , 
     @@identity  , 
     NULL  
    FROM 
     sys.columns AS c 
    INNER JOIN 
     sys.objects AS o 
    ON ( 
     o.object_id = c.object_id 
    ) 
    WHERE 
     o.name = @object_name

 --Move next :P
 FETCH NEXT FROM Table_cursor
 INTO  @object_name , @modify_date
--End of cursor
END
--Close an Deallocate cursor 
CLOSE Table_cursor
DEALLOCATE Table_cursor

--I Can't use agregate functions with update from (I Don't know why)... with declared tables, use the temp table
SELECT *  INTO #temp FROM  @Entionary

-- other example for set a xml field, only that
UPDATE #temp SET  [Help] = t.Result
FROM 
 (SELECT ID, CONVERT(XML, '' 
 + CHAR(13) + CHAR(9) + 'MRP'
 + CHAR(13) + CHAR(9) + '23/09/2010'
 + CHAR(13) + CHAR(9) + '' + [LogicName] + ''
 + CHAR(13) + CHAR(9) + '' + [PhisicName] + ''
 + CHAR(13) + CHAR(9) + ''
 + CHAR(13) + CHAR(9) + ''
 + CHAR(13) + ' ' )  AS Result
 FROM #temp ) AS t INNER JOIN #temp ON ( #temp.id = t.id) 

--show the ResultSet
SELECT * FROM #temp

--Drop the temp table
DROP TABLE #temp

Task Calendar example

This example just want to show different ways to solve specific problems, means that this script contains unnecessary processes.

Best regards

--Declare a new table where I will insert every task

DECLARE @CalendarX TABLE  (
[ID] INT IDENTITY(1,1)  ,
[TaskDate] datetime ,
[TaskName] varchar(255)
)

--Other table where insert every day of month
DECLARE @DaysofMonth TABLE  (
[ID] INT   ,
[TaskDate] datetime
)

SET NOCOUNT ON
--Insert examples task rows
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-20', 'Task 1')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-19', 'Task 2')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-15', 'Task 3')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-8', 'Task 4')
Insert into @CalendarX ([TaskDate], [TaskName]) values ( '2010-10-21', 'Task 5')
SET NOCOUNT OFF

DECLARE @daytosee   DATETIME
DECLARE @FirstDayofMonth DATETIME
DECLARE @LastDayofMonth  DATETIME
DECLARE @CountofDays  SMALLINT
DECLARE @CurrentDay   SMALLINT
DECLARE @XMLDATA   XML

--Set each var
SET @CurrentDay   = 1
SET @daytosee   = DATEADD(m,1,GETDATE())
SET @CountofDays  = 0 -- Set to zero
SET @FirstDayofMonth         = CONVERT(DATETIME,CONVERT(VARCHAR(4),DATEPART(yyyy, @daytosee)) 

    + '/' +  CONVERT(VARCHAR(2),DATEPART(m,@daytosee)) 

    + '/01' )
SET @LastDayofMonth  = DATEADD(d,-1, dateadd(m, 1, @FirstDayofMonth))
SET @CountofDays  = DATEDIFF(d, @FirstDayofMonth, @LastDayofMonth ) + 1

--Show values


--Run a simple while
WHILE @CurrentDay < @CountofDays BEGIN  

INSERT INTO @DaysofMonth 

 VALUES (@CurrentDay, DATEADD(d, @CurrentDay -1, @FirstDayofMonth))  

 SET @CurrentDay = @CurrentDay + 1 

END  

--Join the tables  

BEGIN  

 WITH FinalCalendar AS (SELECT

   ISNULL(cx.ID, 0) AS ID,        

   dm.TaskDate ,        

   ISNULL(cx.TaskName, '[Empty]') AS TaskName       

  FROM 

   @DaysofMonth AS dm

  LEFT join        

   @CalendarX  AS cx

  ON (dm.TaskDate = cx.TaskDate))  

 SELECT @XMLDATA  = CAST((SELECT * FROM FinalCalendar FOR XML AUTO) AS XML)  

END  

--Show the result  

SELECT   

 @DayToSee     AS DayToSee     ,   

 @FirstDayofMonth  AS FirstDayofMonth  ,   

 @LastDayofMonth   AS LastDayofMonth  ,   

 @CountofDays    AS CountofDays   ,  

 @XMLDATA             AS Calendar  

/* Compatible SQL 2008 SQL 2008 compatible */

Resultado XML de un select dinamico

-Quiere obtener el resultado de un select dinamico? no puede hacerlo? :D y ademas quiere sacar un xml como salida?

-Aqui esta el ejemplo que usted buscaba... (probado en sql server 2008).

Saludos cordiales

- Want to get the result of a dynamic select? can not do? : D and also wants to get an xml as output?

- Here is the example you wanted ... (Tested in SQL Server 2008).

Best regards

--I make a temporal table
--Creo una tabla temporal
CREATE TABLE TEMPORALTBL (
 ID INT,
 DESCRIPTION  varchar(200)
)

--Insert the example rows
--Inserto registros de ejemplo
INSERT INTO TEMPORALTBL VALUES (1,'THIS IS A TEST')
INSERT INTO TEMPORALTBL VALUES (2,'MAY ENGLISH IS POOR')
INSERT INTO TEMPORALTBL VALUES (3,'MAY SPANISH IS THE POOREST')


DECLARE @cmd NVARCHAR(2000)
DECLARE @result XML

--My dynamic select
--Mi select dinamico
SET @cmd = N'select top 100 * from temporaltbl '

--I make a cast to xml
--Hago un cast a xml
SET @cmd = N'select @insideresult= CAST((' +@cmd + ' FOR XML AUTO, Elements ) AS xml) '

--EXECUTE THE SELECT!!!! CAN YOU SEE?
--EJECUTO EL SELECT... CABRON LO PUEDES VER?
EXEC sp_executeSQL @cmd ,N'@insideresult xml output',@insideresult = @result OUTPUT

--Show the XML and command
--Muestro el resultado del xml y el comandojavascript:void(0)
SELECT @cmd  as command, @result as Textxml

--Drop the example table
--Borro la tabla de ejemplo
DROP TABLE temporaltbl
/*
Compatible SQL 2008
SQL 2008 compatible
*/
  

Audit Wizard Script (SQL server 2008)

Este es el primer post donde desarrollo lo que realmente puede llegar a interesar, en este caso es un simple hacedor de script que genera tablas de auditoria con su respectivo disparador, como siempre digo, todo se puede mejorar pero partiendo de una primera idea se puede mejorar aun mas, Cualquier consulta o idea no existe problema en compartirla y discutirla.

El siguiente script es para educación solamente, no es responsable de cualquier daño o mal uso que el código o parte de el pueda producir.

Saludos cordiales

This is the first post where I develop what can really get interested, here is a simple script that generates a wizard audit tables with its own release, as I always say, everything can be improved but starting from an initial idea can be improve further, Any question or idea to share it no problem and discuss it.

The following script is for education only, is not responsible for any damage or misuse of the code or part of it can produce

Best regards

-- =============================================
-- 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 sobre
-- una tabla especifica
-- =============================================


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_' + @object_name
SET @trigger_name = 'AUDIT_TR_' + @object_name


if exists( select * from sys.objects where name in ( @Audit_name, @trigger_name) )
BEGIN
 PRINT '---------------------------------------------------------'
 PRINT '--Informe de proceso con error ATENCION!'
 PRINT '---------------------------------------------------------'
 PRINT '--La tabla de auditoria ' + @Audit_name + ' ya existe, verificar y volver a ejecutar el proceso'
 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 TABLE ' + @Audit_name
 PRINT '--DROP TRIGGER ' + @trigger_name
 PRINT ''
 PRINT '---------------------------------------------------------'
 PRINT '--Warning!! proccess info error
 PRINT '---------------------------------------------------------'
 PRINT '--The audit table ' + @Audit_name + ' already exists, Verify and run again the script'
 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 TABLE ' + @Audit_name
 PRINT '--DROP TRIGGER ' + @trigger_name


END ELSE BEGIN
 SET NOCOUNT ON



 PRINT '-------- Script de creacion de tabla de auditoria AUDIT_' + @object_name+ '--------'
 PRINT '-------- Make the script to make the audit table AUDIT_' + @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 TABLE ' + @Audit_name + ' ('
 PRINT CHAR(9) + ' [AUDIT_ID] ' + CHAR(9) + ' INT IDENTITY(1,1) ' + CHAR(9) + ','
 PRINT CHAR(9) + ' [AUDIT_ACTION] ' + CHAR(9) + ' CHAR(1) ' + CHAR(9) + ' ,'
 DECLARE Table_cursor CURSOR FOR
  SELECT
   o.name AS object_name,
   c.name AS column_name,
   is_nullable = CASE WHEN c.is_nullable <> 0 THEN 'NULL' ELSE 'NOT NULL' END,  
   [type] = '[' + upper(t.name) + '] ' + CASE
    WHEN t.name in ('nvarchar', 'nchar','char','varchar') THEN
    '(' + convert(varchar(4), c.max_length) + ')'
    WHEN t.name in ('float', 'numeric','money','smallmoney','real','decimal') THEN
    '(' + CONVERT(VARCHAR(4), c.precision) + ',' + CONVERT(VARCHAR(4), c.scale) + ')'
    ELSE ''

   END,
   c.max_length ,
   c.precision ,
   c.scale
  FROM
   sys.objects AS o
  INNER JOIN
   sys.columns AS c
  ON ( o.object_id = c.object_id)
  INNER JOIN
   systypes as t
  ON ( c.system_type_id = t.xtype )
  WHERE
   o.type = 'U'
   AND o.name = @object_name
  ORDER BY
   o.name ,
   column_id
 OPEN Table_cursor
 FETCH NEXT FROM Table_cursor
 INTO @object_name , @column_name ,@is_nullable, @type ,
   @max_length, @precision , @scale

 WHILE @@FETCH_STATUS = 0
 BEGIN

  PRINT CHAR(9) + ' [' + @column_name + '] ' + CHAR(9) + @TYPE + ' ' + CHAR(9) + @is_nullable + ','
 
  FETCH NEXT FROM Table_cursor
  INTO @object_name , @column_name ,@is_nullable, @type ,
    @max_length, @precision , @scale
 END
 CLOSE Table_cursor
 DEALLOCATE Table_cursor


 PRINT CHAR(9) + ' [AUDIT_DATE] ' + CHAR(9) + ' DATETIME ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_USER] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_APLICATION] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_HOST] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' NOT NULL,'
 PRINT CHAR(9) + ' [AUDIT_TIMESTAMP] ' + CHAR(9) + ' TIMESTAMP ' + CHAR(9) + ' ,'

 PRINT ' CONSTRAINT [' + @Audit_name + '_PK] PRIMARY KEY CLUSTERED '
 PRINT ' ('
 PRINT '  [AUDIT_ID] ASC'
 PRINT ' )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
 PRINT ' ) ON [PRIMARY]'
 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 ''
 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 @CAMPOS VARCHAR(8000)
 SET @CAMPOS = ''
 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 @CAMPOS = @CAMPOS + '' + ' [' + @column_name + '] ,'
 
  FETCH NEXT FROM Table_cursor
  INTO @column_name
 END
 CLOSE Table_cursor
 DEALLOCATE Table_cursor
 PRINT CHAR(9) + 'SET NOCOUNT ON'

 PRINT CHAR(9) + 'IF (@ACTION IN ( ''I'')) BEGIN'
 PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
 PRINT CHAR(9) + CHAR(9) + '( ' + @CAMPOS + '[AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST])'
 PRINT CHAR(9) + CHAR(9) + ' SELECT ' + @CAMPOS + '@ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() FROM INSERTED'
 PRINT CHAR(9) + 'END'

 PRINT CHAR(9) + 'ELSE IF (@ACTION IN (''U'', ''D'')) BEGIN'
 PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
 PRINT CHAR(9) + CHAR(9) + '( ' + @CAMPOS + '[AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST])'
 PRINT CHAR(9) + CHAR(9) + ' SELECT ' + @CAMPOS + '@ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() FROM DELETED'
 PRINT CHAR(9) + 'END'
 PRINT 'END'



 PRINT '---------------------------------------------------------'
 PRINT '--Informe de proceso'
 PRINT '---------------------------------------------------------'
 PRINT '--Se generò la tabla de auditoria ' + @Audit_name
 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 '--Made the audit table named ' + @Audit_name
 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
/*
Compatible SQL 2008
SQL 2008 compatible
*/