Created
February 7, 2017 21:08
-
-
Save RoLYroLLs/90d2d677d0b8f9ac200487ead0086a23 to your computer and use it in GitHub Desktop.
TSQL Audit Trail Generator with Triggers and Views
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- ============================================= | |
| -- | |
| -- 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