Auto Auditing on Tables

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--TYPE IN THE NAME OF THE DB YOUR SOURCE TABLE IS IN , INTO THE BRACKETS BELOW

ALTER PROCEDURE [dbo].[pAutoAudit]
(
 @DATABASE  varchar(100)
, @TABLENAME varchar(100)
, @SCHEMA varchar(100)
, @AuditDATABASE  varchar(100)
, @AudiTABLENAME  varchar(100)
, @YourName varchar(150)
, @VinJobNo int
, @VbtComplex BIT = 0
)
AS

DECLARE @Done bit 
DECLARE @CRLF char(2)
DECLARE @SQL varchar(8000)
DECLARE @SQL1 varchar(8000)
DECLARE @SQL2 varchar(8000)
DECLARE @SQL3 varchar(8000)
DECLARE @SQLColumn varchar(1000)
DECLARE @SQLColumnSelect varchar(1000)
 
SET @Done=0 
SET @CRLF = char(10)
SET @SQLColumn = ''
SET @SQLColumnSelect = ''
/*
This is something that I find very useful and saves me a lot of time.
This procedure needs to be created on the Database that holds the table that you wish to Audit.
This procedure when called will create an audit table based on your source table in a database of your choice. All update/delete and insert triggers will also be created.
The results are then printed out so that you can make an small modifications that you may need to.
*//*****************************************************************************
@DATABASE = the database that the table is in that you wish to audit [Make sure you are currently look at this db]
@TABLENAME = the table which you would like to be audited
@SCHEMA = trhe schema you wish the tables to be created
@AuditDATABASE = the name of the audit database to create the DB in
@AudiTABLENAME =The name of your audit table
@YourName = Your name which will be used for the trigger discriptions
@VinJobNo = The Job number that this relates
@VbtComplex  = if 1 then triggers will have looping inserts 0 then triggers have striaght inserts
*****************************************************************************/

--CHECK TO SEE IF AUDIT TABLE EXISTS
--IF SO THEN DROP IT
--CREATE NEW AUDIT TABLE
SET @SQL = 'USE [' + @AuditDATABASE + '] 
GO' + @CRLF + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' +@AuditDATABASE +'].['+@SCHEMA+'].[' + @AudiTABLENAME + ']'') AND type in (N''U'')) 
BEGIN
DROP TABLE [' +@AuditDATABASE +'].[dbo].['+@AudiTABLENAME+'] 
END

     
CREATE TABLE [' +@AuditDATABASE +'].['+@SCHEMA+'].['+@AudiTABLENAME+'] (
AuditID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,'+@CRLF

DECLARE @COLUMNID int

DECLARE @COLUMNNAME varchar(1000)
DECLARE @COLUMNTYPE varchar(100)
DECLARE @COLUMNSIZE INT

SET @COLUMNID = 0
WHILE @Done=0   
BEGIN
SELECT top 1
    @COLUMNID=clmns.column_id,
    @COLUMNNAME=clmns.name ,
    @COLUMNTYPE=usrt.name ,
    @COLUMNSIZE=CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1
  THEN clmns.max_length/2
  ELSE clmns.max_length
     END AS int)
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns 
ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.types AS usrt 
ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset 
ON baset.user_type_id = clmns.system_type_id 
and
baset.user_type_id = baset.system_type_id
WHERE
(tbl.name=@TABLENAME and SCHEMA_NAME(tbl.schema_id)=@SCHEMA)
and 
clmns.column_id > @COLUMNID
ORDER BY
    clmns.column_id asc

IF @@rowcount=0  
begin
SET @Done=1  
end
else
begin
SET @SQLColumn=@SQLColumn+'['+@COLUMNNAME+'] ['+@COLUMNTYPE+'] '
SET @SQLColumnSelect = @SQLColumnSelect + ',['+@COLUMNNAME+']'+@CRLF
IF ((@COLUMNTYPE='varchar') or (@COLUMNTYPE='char') )
BEGIN 
SET @SQLColumn=@SQLColumn+'('+CASE WHEN ltrim(str(@COLUMNSIZE)) = '-1' then 'MAX' ELSE ltrim(str(@COLUMNSIZE)) END
+') '
END

SET @SQLColumn=@SQLColumn+'NULL, '+@CRLF
end


END

--JOIN THE SQL to the Column
SET @SQL = @SQL + @SQLColumn +  '[ActionType] INT,'  + @CRLF + '[ActionUser] VARCHAR(200),' + @CRLF + '[ActionDate] datetime )'+ @CRLF+' GO' + @CRLF + 'USE [' + @DATABASE + ']'+ @CRLF+' GO' +@CRLF
SELECT @SQLColumnSelect = right(@SQLColumnSelect,LEN(@SQLColumnSelect)-2)


--CHECK TO CREATE AN UPDATE TRIGGER
SET @SQL1='IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tr'+@TABLENAME+'_Update]''))
BEGIN 
DROP TRIGGER [dbo].[tr'+@TABLENAME+'_Update]
END'+@CRLF + ' GO ' + @CRLF

SET @SQL1=@SQL1 + 'CREATE TRIGGER [tr'+@TABLENAME+'_UPDATE] ON ['+@SCHEMA+'].['+@TABLENAME+'] FOR UPDATE
/*
  Author:      .'+@YourName+'
  Create Date: .'+ CONVERT(VARCHAR, GETDATE(),103) +'
  Description: .This is used for auditing any data updates that happen on this table
  Results:     .Records are audited
  
  Change History:
  Author                DateJobNo.Description
  ------                ----        -----------------
  '+@YourName+'' + CONVERT(VARCHAR, GETDATE(),103) +'  ' + CAST(@VinJobNo as VARCHAR)+ 'CREATED
*/AS
BEGIN 
SET NOCOUNT ON;

'
IF (@VbtComplex = 0)
BEGIN
SELECT @SQL1=@SQL1 + 'INSERT INTO [' +@AuditDATABASE +'].['+@SCHEMA+'].[' + @AudiTABLENAME + ']
(
' + REPLACE(@SQLColumnSelect,',[',',[') + ',[ActionType]
,[ActionUser]
,[ActionDate]
)
SELECT
' + REPLACE(@SQLColumnSelect,',[',',[') + ',2--Update
,suser_sname()
,GETDATE()
FROM INSERTED
END'+ @CRLF+ ' GO ' + @CRLF
END
ELSE
BEGIN
SELECT @SQL1=@SQL1 + '
--DECLARE Temp Table for holding updated records
DECLARE @VtblInsert TABLE
(
ROWID INT IDENTITY(1,1) PRIMARY KEY , 
' + left(@SQLColumn,LEN(@SQLColumn)-3) + '
)

--INSERT ROWS INTO THIS TABLE
INSERT INTO @VtblInsert
(
' +@SQLColumnSelect + '
)
SELECT 
'+ @SQLColumnSelect + '
FROM INSERTED


DECLARE @VinLoopCounter INT
DECLARE @VinLoopCounterMax INT

SELECT @VinLoopCounter = 1
,@VinLoopCounterMax = MAX(RowID)
FROM @VtblInsert


--LOOP THROUGH ALL RECORDS
WHILE (@VinLoopCounter < = @VinLoopCounterMAX)
BEGIN

INSERT INTO [' +@AuditDATABASE +'].['+@SCHEMA+'].[' + @AudiTABLENAME + ']
(
' + REPLACE(@SQLColumnSelect,',[',',[') + ',[ActionType]
,[ActionUser]
,[ActionDate]
)
SELECT
' + REPLACE(@SQLColumnSelect,',[',',[') + ',2--Update
,suser_sname()
,GETDATE()
FROM @VtblInsert
WHERE ROWID = @VinLoopCounter

SET @VinLoopCounter = @VinLoopCounter + 1
END
END'+ @CRLF+ ' GO ' + @CRLF
END
--CHECK TO CREATE AN INSERT TRIGGER
SET @SQL2='IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tr'+@TABLENAME+'_Insert]''))
BEGIN 
DROP TRIGGER [dbo].[tr'+@TABLENAME+'_Insert]
END'+@CRLF + ' GO ' + @CRLF

SET @SQL2=@SQL2 + 'CREATE TRIGGER [tr'+@TABLENAME+'_Insert] ON ['+@SCHEMA+'].['+@TABLENAME+'] FOR INSERT
/*
  Author:      .'+@YourName+'
  Create Date: .'+ CONVERT(VARCHAR, GETDATE(),103) +'
  Description: .This is used for auditing any data inserts that happen on this table
  Results:     .Records are audited
  
  Change History:
  Author                Date          Description
  ------                ----        -----------
  '+@YourName+'' + CONVERT(VARCHAR, GETDATE(),103) +'CREATED
*/AS
BEGIN 
SET NOCOUNT ON;'
IF (@VbtComplex = 0)
BEGIN
SELECT @SQL2=@SQL2 + '
INSERT INTO [' +@AuditDATABASE +'].['+@SCHEMA+'].[' + @AudiTABLENAME + ']
(
' + REPLACE(@SQLColumnSelect,',[',',[') + ',[ActionType]
,[ActionUser]
,[ActionDate]
)
SELECT
' + REPLACE(@SQLColumnSelect,',[',',[') + ',1--INSERT
,suser_sname()
,GETDATE()
FROM INSERTED
END'+ @CRLF+ ' GO ' + @CRLF
END
ELSE
BEGIN 
SELECT @SQL2=@SQL2 + '
--DECLARE Temp Table for holding updated records
DECLARE @VtblInsert TABLE
(
ROWID INT IDENTITY(1,1) PRIMARY KEY , 
' + left(@SQLColumn,LEN(@SQLColumn)-3)  + '
)

--INSERT ROWS INTO THIS TABLE
INSERT INTO @VtblInsert
(
' +@SQLColumnSelect + '
)
SELECT 
'+ @SQLColumnSelect + '
FROM INSERTED


DECLARE @VinLoopCounter INT
DECLARE @VinLoopCounterMax INT

SELECT @VinLoopCounter = 1
,@VinLoopCounterMax = MAX(RowID)
FROM @VtblInsert


--LOOP THROUGH ALL RECORDS
WHILE (@VinLoopCounter < = @VinLoopCounterMAX)
BEGIN

INSERT INTO [' +@AuditDATABASE +'].['+@SCHEMA+'].[' + @AudiTABLENAME + ']
(
' + REPLACE(@SQLColumnSelect,',[',',[') + ',[ActionType]
,[ActionUser]
,[ActionDate]
)
SELECT
' + REPLACE(@SQLColumnSelect,',[',',[') + ',1--INSERT
,suser_sname()
,GETDATE()
FROM @VtblInsert
WHERE ROWID = @VinLoopCounter

SET @VinLoopCounter = @VinLoopCounter + 1
END
END'+ @CRLF+ ' GO ' + @CRLF
END

--CHECK TO CREATE AN Deletes TRIGGER
SET @SQL3='IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tr'+@TABLENAME+'_Delete]''))
BEGIN 
DROP TRIGGER [dbo].[tr'+@TABLENAME+'_Delete]
END'+@CRLF + ' GO ' + @CRLF

SET @SQL3=@SQL3 + 'CREATE TRIGGER [tr'+@TABLENAME+'_Delete] ON ['+@SCHEMA+'].['+@TABLENAME+'] FOR Delete
/*
  Author:      .'+@YourName+'
  Create Date: .'+ CONVERT(VARCHAR, GETDATE(),103) +'
  Description: .This is used for auditing any data Deletes that happen on this table
  Results:     .Records are audited
  
  Change History:
  Author                Date          Description
  ------                ----        -----------
  '+@YourName+'' + CONVERT(VARCHAR, GETDATE(),103) +'CREATED
*/AS
BEGIN 
SET NOCOUNT ON;
'
IF (@VbtComplex = 0)
BEGIN
SELECT @SQL3=@SQL3 + '
INSERT INTO [' +@AuditDATABASE +'].['+@SCHEMA+'].[' + @AudiTABLENAME + ']
(
' + REPLACE(@SQLColumnSelect,',[',',[') + ',[ActionType]
,[ActionUser]
,[ActionDate]
)
SELECT
' + REPLACE(@SQLColumnSelect,',[',',[') + ',3--DELETE
,suser_sname()
,GETDATE()
FROM DELETED
END'+ @CRLF+ ' GO ' + @CRLF
END
ELSE
BEGIN
SELECT @SQL3=@SQL3 + '
--DECLARE Temp Table for holding updated records
DECLARE @VtblDeleted TABLE
(
ROWID INT IDENTITY(1,1) PRIMARY KEY , 
' + left(@SQLColumn,LEN(@SQLColumn)-3)  + '
)

--INSERT ROWS INTO THIS TABLE
INSERT INTO @VtblDELETED
(
' +@SQLColumnSelect + '
)
SELECT 
'+ @SQLColumnSelect + '
FROM DELETED


DECLARE @VinLoopCounter INT
DECLARE @VinLoopCounterMax INT

SELECT @VinLoopCounter = 1
,@VinLoopCounterMax = MAX(RowID)
FROM @VtblDELETED


--LOOP THROUGH ALL RECORDS
WHILE (@VinLoopCounter < = @VinLoopCounterMAX)
BEGIN

INSERT INTO [' +@AuditDATABASE +'].['+@SCHEMA+'].[' + @AudiTABLENAME + ']
(
' + REPLACE(@SQLColumnSelect,',[',',[') + ',[ActionType]
,[ActionUser]
,[ActionDate]
)
SELECT
' + REPLACE(@SQLColumnSelect,',[',',[') + ',3--DELETE
,suser_sname()
,GETDATE()
FROM @VtblDELETED
WHERE ROWID = @VinLoopCounter

SET @VinLoopCounter = @VinLoopCounter + 1
END
END'+ @CRLF+ ' GO ' + @CRLF
END

PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT '--CREATE TABLES'
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT @SQL
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT '--UPDATE TRIGGER'
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT @SQL1
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT '--INSERT TRIGGER'
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT @SQL2
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT '--DELETE TRIGGER'
PRINT '-----------------------------------------------------------------------------------------------------------------'
PRINT @SQL3