Skip to content

Instantly share code, notes, and snippets.

@RoLYroLLs

RoLYroLLs/AuditTrailGenerator.sql

Created February 7, 2017 21:08
Show Gist options
  • Save RoLYroLLs/90d2d677d0b8f9ac200487ead0086a23 to your computer and use it in GitHub Desktop.
Save RoLYroLLs/90d2d677d0b8f9ac200487ead0086a23 to your computer and use it in GitHub Desktop.
Download ZIP
TSQL Audit Trail Generator with Triggers and Views
Raw
AuditTrailGenerator.sql
-- =============================================
--
-- Modification of https://www.codeproject.com/articles/21068/audit-trail-generator-for-microsoft-sql
--
-- Author: Rolando Liriano
-- Create date: 2017/02/03
-- Description: Creates an audit trail table along with triggers and views for a specified table
-- @TableName required - Name of the table to audit
-- @PrimaryKey required - Name of the primary key in table
-- @TableSchema optional - Current schema of the table
-- @AuditTableSchema optional - Schema of the audit table to create
-- @AuditNameExtention optional - Extention name to the new audit table
-- @IncludePreviousColumns optional - Should include a column to store previous columns
-- @TriggerPrefix optional - Prefix to use for the triggers
-- @ViewPrefix optional - Prefix to use for the views
-- @DropAuditTable optional - Should force-drop current audit table
-- @ShowOuputOnly optional - Show the output without actually doing anything
-- =============================================
CREATE PROCEDURE [Audit].[GenerateAuditTrail]
@TableName VARCHAR(128)
, @PrimaryKey VARCHAR(128)
, @TableSchema VARCHAR(128) = 'dbo'
, @AuditTableSchema VARCHAR(128) = 'dbo'
, @AuditNameExtention VARCHAR(128) = '_Audit'
, @IncludePreviousColumns BIT = 0
, @TriggerPrefix VARCHAR(20) = 'trg_'
, @ViewPrefix VARCHAR(20) = 'vw_'
, @DropAuditTable BIT = 0
, @ShowOuputOnly BIT = 0
AS
BEGIN
DECLARE @NewLine VARCHAR(20) = CHAR(13) + CHAR(10)
/********************************************************************
Initial checks
********************************************************************/
PRINT '****************************************'
-- Check if table exists
IF NOT EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TableName + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table [' + @TableSchema + '].[' + @TableName + '] does not exist'
RETURN
END
ELSE
BEGIN
PRINT 'PASS: Table [' + @TableSchema + '].[' + @TableName + '] does exist'
END
-- Check if audit table exists
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1)
BEGIN
PRINT 'PASS: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] already exist'
END
ELSE
BEGIN
PRINT 'PASS: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] does not exist'
END
-- Check @PrimaryKey
IF @PrimaryKey IS NULL
BEGIN
PRINT 'ERROR: @PrimaryKey cannot be null'
RETURN
END
ELSE
BEGIN
PRINT 'PASS: @PrimaryKey is not null'
END
-- Check @AuditNameExtention
IF @AuditNameExtention IS NULL
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END
ELSE
BEGIN
PRINT 'PASS: @AuditNameExtention is not null'
END
PRINT '****************************************'
/********************************************************************
Drop audit table if it exists and drop should be forced
********************************************************************/
IF (EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1) AND @DropAuditTable = 1)
BEGIN
PRINT 'DROPPING: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
IF @ShowOuputOnly = 0
BEGIN
EXEC ('drop table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']')
END
PRINT 'DROPPED: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
END
ELSE
BEGIN
IF @DropAuditTable = 1
BEGIN
PRINT 'Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] cannot be force dropped because it does not exist'
END
END
PRINT '****************************************'
/********************************************************************
Declare cursor to loop over columns
********************************************************************/
DECLARE [TableColumns] CURSOR READ_ONLY
FOR SELECT [b].[name], [c].[name] as [TypeName], [b].[length], [b].[isnullable], [b].[collation], [b].[xprec], [b].[xscale]
FROM [sysobjects] [a]
INNER JOIN [syscolumns] [b] on [a].[id] = [b].[id]
INNER JOIN [systypes] [c] on [b].[xtype] = [c].[xtype] AND [c].[name] <> 'sysname'
WHERE [a].[id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TableName + ']')
AND OBJECTPROPERTY([a].[id], N'IsUserTable') = 1
ORDER BY [b].[colId]
OPEN TableColumns
-- Declare temp variable to fetch records into
DECLARE @ColumnName VARCHAR(128)
DECLARE @ColumnType VARCHAR(128)
DECLARE @ColumnLength SMALLINT
DECLARE @ColumnNullable INT
DECLARE @ColumnCollation SYSNAME
DECLARE @ColumnPrecision TINYINT
DECLARE @ColumnScale TINYINT
-- Declare variable to build statements
DECLARE @CreateStatement VARCHAR(MAX) = ''
DECLARE @ListOfFields VARCHAR(MAX) = ''
DECLARE @ListOfFieldsForInsert VARCHAR(MAX) = ''
DECLARE @ListOfFieldsPrevious VARCHAR(MAX) = ''
DECLARE @ListOfFieldsPreviousForInsert VARCHAR(MAX) = ''
DECLARE @ListOfFieldsForWhere VARCHAR(MAX) = ''
DECLARE @ListOfFieldsForView VARCHAR(MAX) = ''
-- Check if audit table exists
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1)
BEGIN
-- AuditTable exists, update needed
PRINT 'Audit Table already exists. Only triggers and view will be updated.'
FETCH NEXT FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' AND @ColumnType <> 'ntext' AND @ColumnType <> 'image' AND @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + '[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForInsert = @ListOfFieldsForInsert + '[Inserted].[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '],' + @NewLine + ' '
IF @IncludePreviousColumns = 1
BEGIN
SET @ListOfFieldsPrevious = @ListOfFieldsPrevious + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsPreviousForInsert = @ListOfFieldsPreviousForInsert + '[Deleted].[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + 'CASE WHEN [' + @ColumnName + '] <> [' + @ColumnName + '_Previous] THEN 1 ELSE 0 END AS [' + @ColumnName + '_Changed],' + @NewLine + ' '
END
END
FETCH NEXT FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
-- AuditTable does not exist, create new
PRINT 'Audit Table does not exists.'
-- Start of create table
SET @CreateStatement = 'CREATE TABLE [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] (' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditId] [BIGINT] IDENTITY (1, 1) NOT NULL, ' + @NewLine + ' '
FETCH NEXT FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' AND @ColumnType <> 'ntext' AND @ColumnType <> 'image' AND @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + '[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForInsert = @ListOfFieldsForInsert + '[Inserted].[' + @ColumnName + '],' + @NewLine + ' '
IF @ListOfFieldsForWhere <> ''
BEGIN
SET @ListOfFieldsForWhere = @ListOfFieldsForWhere + 'or '
END
SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
IF @ColumnType IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(MAX) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnLength AS VARCHAR(10)) + ') '
END
IF @ColumnType IN ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnPrecision AS VARCHAR(10)) + ',' + CAST(@ColumnScale AS VARCHAR(10)) + ') '
IF @ColumnType IN ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + 'NOT '
SET @CreateStatement = @CreateStatement + 'NULL, ' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForWhere = @ListOfFieldsForWhere + '[Inserted].[' + @ColumnName + '] <> [Deleted].[' + @ColumnName + ']' + @NewLine + ' '
-- Previous value columns
IF @IncludePreviousColumns = 1
BEGIN
SET @ListOfFieldsPrevious = @ListOfFieldsPrevious + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsPreviousForInsert = @ListOfFieldsPreviousForInsert + '[Deleted].[' + @ColumnName + '],' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '_Previous] [' + @ColumnType + '] '
IF @ColumnType IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(MAX) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnLength AS VARCHAR(10)) + ') '
END
IF @ColumnType IN ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnPrecision AS VARCHAR(10)) + ',' + CAST(@ColumnScale AS VARCHAR(10)) + ') '
IF @ColumnType IN ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
SET @CreateStatement = @CreateStatement + 'NULL, ' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + 'CASE WHEN [' + @ColumnName + '] <> [' + @ColumnName + '_Previous] THEN 1 ELSE 0 END AS [' + @ColumnName + '_Changed],' + @NewLine + ' '
END
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
-- Add audit trail columns
SET @CreateStatement = @CreateStatement + '[AuditAction] [CHAR] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditDate] [DATETIMEOFFSET] (7) NOT NULL ,' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditUser] [VARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditApp] [VARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'
-- Create audit table
PRINT 'CREATING: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
PRINT ' ' + @CreateStatement
PRINT '****************************************'
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
-- Set primary key and default values
SET @CreateStatement = 'ALTER TABLE [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] ADD ' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (SYSDATETIMEOFFSET()) FOR [AuditDate],' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (SUSER_SNAME()) FOR [AuditUser], ' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + LTRIM(RTRIM(ISNULL(APP_NAME(),''''))) + '') '') FOR [AuditApp], ' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED ([AuditId]) ON [PRIMARY]'
PRINT 'Creating Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
PRINT ' ' + @CreateStatement
PRINT '****************************************'
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'Created Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
END
CLOSE TableColumns
DEALLOCATE TableColumns
/********************************************************************
Drop Triggers, if they exist
*********************************************************************/
PRINT 'Dropping triggers...'
/* Drop insert trigger */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]') AND OBJECTPROPERTY([id], N'IsTrigger') = 1)
BEGIN
SET @CreateStatement = 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
PRINT 'DROPPING: Trigger [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: Trigger [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
END
/* Drop update trigger */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]') AND OBJECTPROPERTY([id], N'IsTrigger') = 1)
BEGIN
SET @CreateStatement = 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
PRINT 'DROPPING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
END
/* Drop delete trigger */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]') AND OBJECTPROPERTY([id], N'IsTrigger') = 1)
BEGIN
SET @CreateStatement = 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
PRINT 'DROPPING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
END
/********************************************************************
Drop Views, if they exist
********************************************************************/
PRINT 'Dropping views...'
/* Drop changes view */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + @AuditNameExtention + '_Changes]') AND OBJECTPROPERTY([id], N'IsView') = 1)
BEGIN
SET @CreateStatement = 'DROP VIEW [' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + @AuditNameExtention + '_Changes]'
PRINT 'DROPPING: View: [' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + @AuditNameExtention + '_Changes]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: View: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + @AuditNameExtention + '_Changes]'
END
/********************************************************************
Create triggers
********************************************************************/
PRINT 'Creating triggers...'
/* Insert trigger */
SET @CreateStatement = 'CREATE TRIGGER ' + @TriggerPrefix + @TableName + '_Insert ON [' + @TableSchema + '].[' + @TableName + '] ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FOR INSERT AS ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'INSERT INTO [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '(' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '' + @ListOfFields + '[AuditAction]' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + ') ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'SELECT ' + @ListOfFields + '''I'' ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FROM Inserted'
PRINT 'CREATING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
/* Update trigger */
SET @CreateStatement = 'CREATE TRIGGER ' + @TriggerPrefix + @TableName + '_Update ON [' + @TableSchema + '].[' + @TableName + '] ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FOR UPDATE AS ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'INSERT INTO [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '(' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '' + @ListOfFields + @ListOfFieldsPrevious + '[AuditAction]' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + ') ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'SELECT ' + @ListOfFieldsForInsert + @ListOfFieldsPreviousForInsert + '''U'' ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FROM [Inserted] INNER JOIN [Deleted] ON [Deleted].[' + @PrimaryKey + '] = [Inserted].[' + @PrimaryKey + ']' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'WHERE ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + @ListOfFieldsForWhere + @NewLine + ' '
PRINT 'CREATING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
BEGIN
DECLARE @NewLine VARCHAR(20) = CHAR(13) + CHAR(10)
/********************************************************************
Initial checks
********************************************************************/
PRINT '****************************************'
-- Check if table exists
IF NOT EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TableName + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table [' + @TableSchema + '].[' + @TableName + '] does not exist'
RETURN
END
ELSE
BEGIN
PRINT 'PASS: Table [' + @TableSchema + '].[' + @TableName + '] does exist'
END
-- Check if audit table exists
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1)
BEGIN
PRINT 'PASS: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] already exist'
END
ELSE
BEGIN
PRINT 'PASS: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] does not exist'
END
-- Check @PrimaryKey
IF @PrimaryKey IS NULL
BEGIN
PRINT 'ERROR: @PrimaryKey cannot be null'
RETURN
END
ELSE
BEGIN
PRINT 'PASS: @PrimaryKey is not null'
END
-- Check @AuditNameExtention
IF @AuditNameExtention IS NULL
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END
ELSE
BEGIN
PRINT 'PASS: @AuditNameExtention is not null'
END
PRINT '****************************************'
/********************************************************************
Drop audit table if it exists and drop should be forced
********************************************************************/
IF (EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1) AND @DropAuditTable = 1)
BEGIN
PRINT 'DROPPING: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
IF @ShowOuputOnly = 0
BEGIN
EXEC ('drop table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']')
END
PRINT 'DROPPED: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
END
ELSE
BEGIN
IF @DropAuditTable = 1
BEGIN
PRINT 'Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] cannot be force dropped because it does not exist'
END
END
PRINT '****************************************'
/********************************************************************
Declare cursor to loop over columns
********************************************************************/
DECLARE [TableColumns] CURSOR READ_ONLY
FOR SELECT [b].[name], [c].[name] as [TypeName], [b].[length], [b].[isnullable], [b].[collation], [b].[xprec], [b].[xscale]
FROM [sysobjects] [a]
INNER JOIN [syscolumns] [b] on [a].[id] = [b].[id]
INNER JOIN [systypes] [c] on [b].[xtype] = [c].[xtype] AND [c].[name] <> 'sysname'
WHERE [a].[id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TableName + ']')
AND OBJECTPROPERTY([a].[id], N'IsUserTable') = 1
ORDER BY [b].[colId]
OPEN TableColumns
-- Declare temp variable to fetch records into
DECLARE @ColumnName VARCHAR(128)
DECLARE @ColumnType VARCHAR(128)
DECLARE @ColumnLength SMALLINT
DECLARE @ColumnNullable INT
DECLARE @ColumnCollation SYSNAME
DECLARE @ColumnPrecision TINYINT
DECLARE @ColumnScale TINYINT
-- Declare variable to build statements
DECLARE @CreateStatement VARCHAR(MAX) = ''
DECLARE @ListOfFields VARCHAR(MAX) = ''
DECLARE @ListOfFieldsForInsert VARCHAR(MAX) = ''
DECLARE @ListOfFieldsPrevious VARCHAR(MAX) = ''
DECLARE @ListOfFieldsPreviousForInsert VARCHAR(MAX) = ''
DECLARE @ListOfFieldsForWhere VARCHAR(MAX) = ''
DECLARE @ListOfFieldsForView VARCHAR(MAX) = ''
-- Check if audit table exists
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']') AND OBJECTPROPERTY([id], N'IsUserTable') = 1)
BEGIN
-- AuditTable exists, update needed
PRINT 'Audit Table already exists. Only triggers and view will be updated.'
FETCH NEXT FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' AND @ColumnType <> 'ntext' AND @ColumnType <> 'image' AND @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + '[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForInsert = @ListOfFieldsForInsert + '[Inserted].[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '],' + @NewLine + ' '
IF @IncludePreviousColumns = 1
BEGIN
SET @ListOfFieldsPrevious = @ListOfFieldsPrevious + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsPreviousForInsert = @ListOfFieldsPreviousForInsert + '[Deleted].[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + 'CASE WHEN [' + @ColumnName + '] <> [' + @ColumnName + '_Previous] THEN 1 ELSE 0 END AS [' + @ColumnName + '_Changed],' + @NewLine + ' '
END
END
FETCH NEXT FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
-- AuditTable does not exist, create new
PRINT 'Audit Table does not exists.'
-- Start of create table
SET @CreateStatement = 'CREATE TABLE [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] (' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditId] [BIGINT] IDENTITY (1, 1) NOT NULL, ' + @NewLine + ' '
FETCH NEXT FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' AND @ColumnType <> 'ntext' AND @ColumnType <> 'image' AND @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + '[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForInsert = @ListOfFieldsForInsert + '[Inserted].[' + @ColumnName + '],' + @NewLine + ' '
IF @ListOfFieldsForWhere <> ''
BEGIN
SET @ListOfFieldsForWhere = @ListOfFieldsForWhere + 'or '
END
SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
IF @ColumnType IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(MAX) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnLength AS VARCHAR(10)) + ') '
END
IF @ColumnType IN ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnPrecision AS VARCHAR(10)) + ',' + CAST(@ColumnScale AS VARCHAR(10)) + ') '
IF @ColumnType IN ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
IF @ColumnNullable = 0 AND @IncludePreviousColumns = 0
SET @CreateStatement = @CreateStatement + 'NOT '
SET @CreateStatement = @CreateStatement + 'NULL, ' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '],' + @NewLine + ' '
SET @ListOfFieldsForWhere = @ListOfFieldsForWhere + '[Inserted].[' + @ColumnName + '] <> [Deleted].[' + @ColumnName + ']' + @NewLine + ' '
-- Previous value columns
IF @IncludePreviousColumns = 1
BEGIN
SET @ListOfFieldsPrevious = @ListOfFieldsPrevious + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsPreviousForInsert = @ListOfFieldsPreviousForInsert + '[Deleted].[' + @ColumnName + '],' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '_Previous] [' + @ColumnType + '] '
IF @ColumnType IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(MAX) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnLength AS VARCHAR(10)) + ') '
END
IF @ColumnType IN ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnPrecision AS VARCHAR(10)) + ',' + CAST(@ColumnScale AS VARCHAR(10)) + ') '
IF @ColumnType IN ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
SET @CreateStatement = @CreateStatement + 'NULL, ' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + '[' + @ColumnName + '_Previous],' + @NewLine + ' '
SET @ListOfFieldsForView = @ListOfFieldsForView + 'CASE WHEN [' + @ColumnName + '] <> [' + @ColumnName + '_Previous] THEN 1 ELSE 0 END AS [' + @ColumnName + '_Changed],' + @NewLine + ' '
END
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
-- Add audit trail columns
SET @CreateStatement = @CreateStatement + '[AuditAction] [CHAR] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditDate] [DATETIMEOFFSET] (7) NOT NULL ,' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditUser] [VARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditApp] [VARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'
-- Create audit table
PRINT 'CREATING: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
PRINT ' ' + @CreateStatement
PRINT '****************************************'
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
-- Set primary key and default values
SET @CreateStatement = 'ALTER TABLE [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] ADD ' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (SYSDATETIMEOFFSET()) FOR [AuditDate],' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (SUSER_SNAME()) FOR [AuditUser], ' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + LTRIM(RTRIM(ISNULL(APP_NAME(),''''))) + '') '') FOR [AuditApp], ' + @NewLine
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED ([AuditId]) ON [PRIMARY]'
PRINT 'Creating Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
PRINT ' ' + @CreateStatement
PRINT '****************************************'
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'Created Audit Table [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']'
END
CLOSE TableColumns
DEALLOCATE TableColumns
/********************************************************************
Drop Triggers, if they exist
*********************************************************************/
PRINT 'Dropping triggers...'
/* Drop insert trigger */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]') AND OBJECTPROPERTY([id], N'IsTrigger') = 1)
BEGIN
SET @CreateStatement = 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
PRINT 'DROPPING: Trigger [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: Trigger [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
END
/* Drop update trigger */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]') AND OBJECTPROPERTY([id], N'IsTrigger') = 1)
BEGIN
SET @CreateStatement = 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
PRINT 'DROPPING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
END
/* Drop delete trigger */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]') AND OBJECTPROPERTY([id], N'IsTrigger') = 1)
BEGIN
SET @CreateStatement = 'DROP TRIGGER [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
PRINT 'DROPPING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
END
/********************************************************************
Drop Views, if they exist
********************************************************************/
PRINT 'Dropping views...'
/* Drop changes view */
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE [id] = OBJECT_ID(N'[' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + @AuditNameExtention + '_Changes]') AND OBJECTPROPERTY([id], N'IsView') = 1)
BEGIN
SET @CreateStatement = 'DROP VIEW [' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + @AuditNameExtention + '_Changes]'
PRINT 'DROPPING: View: [' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + @AuditNameExtention + '_Changes]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'DROPPED: View: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + @AuditNameExtention + '_Changes]'
END
/********************************************************************
Create triggers
********************************************************************/
PRINT 'Creating triggers...'
/* Insert trigger */
SET @CreateStatement = 'CREATE TRIGGER ' + @TriggerPrefix + @TableName + '_Insert ON [' + @TableSchema + '].[' + @TableName + '] ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FOR INSERT AS ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'INSERT INTO [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '(' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '' + @ListOfFields + '[AuditAction]' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + ') ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'SELECT ' + @ListOfFields + '''I'' ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FROM Inserted'
PRINT 'CREATING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Insert]'
/* Update trigger */
SET @CreateStatement = 'CREATE TRIGGER ' + @TriggerPrefix + @TableName + '_Update ON [' + @TableSchema + '].[' + @TableName + '] ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FOR UPDATE AS ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'INSERT INTO [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '(' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '' + @ListOfFields
IF @IncludePreviousColumns = 1
SET @CreateStatement = @CreateStatement + '' + @ListOfFieldsPrevious
SET @CreateStatement = @CreateStatement + '' + '[AuditAction]' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + ') ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'SELECT ' + @ListOfFieldsForInsert
IF @IncludePreviousColumns = 1
SET @CreateStatement = @CreateStatement + @ListOfFieldsPreviousForInsert
SET @CreateStatement = @CreateStatement + '''U'' ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FROM [Inserted] INNER JOIN [Deleted] ON [Deleted].[' + @PrimaryKey + '] = [Inserted].[' + @PrimaryKey + ']' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'WHERE ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + @ListOfFieldsForWhere + @NewLine + ' '
PRINT 'CREATING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Update]'
/* Delete trigger */
SET @CreateStatement = 'CREATE TRIGGER ' + @TriggerPrefix + @TableName + '_Delete ON [' + @TableSchema + '].[' + @TableName + '] ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FOR DELETE AS ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'INSERT INTO [' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + ']' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '(' + @NewLine + ' '
IF @IncludePreviousColumns = 0
SET @CreateStatement = @CreateStatement + '' + @ListOfFields
ELSE
SET @CreateStatement = @CreateStatement + '' + @ListOfFieldsPrevious
SET @CreateStatement = @CreateStatement + '' + '[AuditAction]' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + ') ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'SELECT '
SET @CreateStatement = @CreateStatement + @ListOfFields
SET @CreateStatement = @CreateStatement + '''D'' ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'FROM Deleted' + ' '
PRINT 'CREATING: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: Trigger: [' + @TableSchema + '].[' + @TriggerPrefix + @TableName + '_Delete]'
/********************************************************************
Create views
********************************************************************/
PRINT 'Creating views...'
/* Changes view */
SET @CreateStatement = 'CREATE VIEW [' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + @AuditNameExtention + '_Changes] ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'AS ' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + 'SELECT' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + + @ListOfFieldsForView + ' '
SET @CreateStatement = @CreateStatement + '[AuditAction],' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditDate],' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditUser],' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[AuditApp]'
SET @CreateStatement = @CreateStatement + 'FROM' + @NewLine + ' '
SET @CreateStatement = @CreateStatement + '[' + @AuditTableSchema + '].[' + @TableName + @AuditNameExtention + '] '
PRINT 'CREATING: View: [' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + '_Changes]'
PRINT ' ' + @CreateStatement
IF @ShowOuputOnly = 0
BEGIN
EXEC (@CreateStatement)
END
PRINT 'CREATED: View: [' + @AuditTableSchema + '].[' + @ViewPrefix + @TableName + '_Changes]'
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment