Auto Auditing on Tables

,

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.

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                Date		JobNo.	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

Rate

4.75 (8)

Share

Share

Rate

4.75 (8)