Automate Audit Trigger Generation

,

I have been tasked with a daunting job of scripting I/U/D triggers for our new database. The job of the triggers is to Audit the activity on the tables. What column was changed to what value on what table and by whom. With over 60 tables, this is a daunting task. So off I went scouring this forum for functions and ideas. Finally, I got it together and it works. We have 1 Audit table for reporting and the rest is in the soup of stored procedures and functions.

The only caveat to this is that each table that you are going to audit has to have a ModifiedById (unique identifier) and that the triggers will not work for text or ntext columns.

The single point of entry is to execute the vp_GenerateTriggers.

Parameters are Table Name and Action. Table name can be any table or 'ALL' for all tables. Action can be I, U, D - you guessed it and A for all.

So execute vp_GenerateTriggers 'ALL', 'A' will generate insert update and delete triggers for all tables in the database.

Gut the cript, modify it to your needs, or change the templates in the stored procedures to meet your needs.

-Milos
milosk@vehix.com
Vehix.com

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit]
GO

CREATE TABLE [dbo].[Audit] (
	[AuditID] [uniqueidentifier] NOT NULL ,
	[TypeID] [uniqueidentifier] NOT NULL ,
	[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TablePKID] [uniqueidentifier] NOT NULL ,
	[ColumnName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OldValue] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NewValue] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DateModified] [datetime] NOT NULL ,
	[ModifiedByID] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnCleanDefaultValue]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnCleanDefaultValue]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnColumnDefault]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnColumnDefault]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnIsColumnPrimaryKey]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnIsColumnPrimaryKey]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnTableColumnInfo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnTableColumnInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnTableHasPrimaryKey]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnTableHasPrimaryKey]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
	RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE FUNCTION dbo.fnColumnDefault(@sTableName varchar(128), @sColumnName varchar(128))
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @sDefaultValue varchar(4000)

	SELECT	@sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
	FROM	INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME = @sTableName
	 AND 	COLUMN_NAME = @sColumnName

	RETURN 	@sDefaultValue

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE   FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
	DECLARE @nTableID int,
		@nIndexID int,
		@i int
	
	SET 	@nTableID = OBJECT_ID(@sTableName)
	
	SELECT 	@nIndexID = indid
	FROM 	sysindexes
	WHERE 	id = @nTableID
	 AND 	indid BETWEEN 1 And 254 
	 AND 	(status & 2048) = 2048
	
	IF @nIndexID Is Null
		RETURN 0
	
	IF @nColumnName IN
		(SELECT sc.[name]
		FROM 	sysindexkeys sik
			INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
		WHERE 	sik.id = @nTableID
		 AND 	sik.indid = @nIndexID)
	 BEGIN
		RETURN 1
	 END


	RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO









CREATE       FUNCTION dbo.fnTableColumnInfo(@sTableName varchar(128))
RETURNS TABLE
AS
	RETURN
	SELECT	c.name AS sColumnName,
		c.colid AS nColumnID,
		dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn,
		CASE 	WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN 1
			WHEN t.name IN ('decimal', 'numeric') THEN 2
			ELSE 0
		END AS nAlternateType,
		c.length AS nColumnLength,
		c.prec AS nColumnPrecision,
		c.scale AS nColumnScale, 
		c.IsNullable, 
		SIGN(c.status & 128) AS IsIdentity,
		t.name as sTypeName,
		dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue
	FROM	syscolumns c 
		INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
	WHERE	c.id = OBJECT_ID(@sTableName)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE FUNCTION dbo.fnTableHasPrimaryKey(@sTableName varchar(128))
RETURNS bit
AS
BEGIN
	DECLARE @nTableID int,
		@nIndexID int
	
	SET 	@nTableID = OBJECT_ID(@sTableName)
	
	SELECT 	@nIndexID = indid
	FROM 	sysindexes
	WHERE 	id = @nTableID
	 AND 	indid BETWEEN 1 And 254 
	 AND 	(status & 2048) = 2048
	
	IF @nIndexID IS NOT Null
		RETURN 1
	
	RETURN 0
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_DeleteTrigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_DeleteTrigger]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_GenerateTriggers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_GenerateTriggers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_InsertTrigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_InsertTrigger]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vp_UpdateTrigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[vp_UpdateTrigger]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  PROC vp_DeleteTrigger
	@sTableName varchar(128),
	@bExecute bit = 0
AS
BEGIN
	set nocount on
	DECLARE	@sProcText varchar(8000),
		@sColumnName varchar(128),
		@nColumnID smallint,
		@bPrimaryKeyColumn bit,
		@nAlternateType int,
		@nColumnLength int,
		@nColumnPrecision int,
		@nColumnScale int,
		@IsNullable bit, 
		@IsIdentity int,
		@sTypeName varchar(128),
		@sDefaultValue varchar(4000),
		@sCRLF char(2),
		@sTAB char(1),
                @sPrimaryKey varchar(128)
	
	DECLARE @ModifiedByID	UNIQUEIDENTIFIER
	DECLARE @CrmActionID	UNIQUEIDENTIFIER
	DECLARE @TableName	VARCHAR(50)
	DECLARE @TablePKID	UNIQUEIDENTIFIER
	DECLARE @ColumnName	VARCHAR(50)
	DECLARE @OldValue	VARCHAR(50)
	DECLARE @NewValue	VARCHAR(50)
	
	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''
	SET 	@ModifiedByID = '00000000-0000-0000-0000-000000000000'
        SET     @sPrimaryKey = 'unknown'
        SET     @NewValue = 'DELETED'

	SET 	@sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''tr_' + @sTableName + '_D'')' + @sCRLF
	SET 	@sProcText = @sProcText + @sTAB + 'DROP trigger tr_' + @sTableName + '_D' + @sCRLF
	SET 	@sProcText = @sProcText + 'GO' + @sCRLF
	SET 	@sProcText = @sProcText + 'CREATE trigger tr_' + @sTableName + '_D ON ' + @sTableName + ' FOR DELETE' + @sCRLF 
	SET 	@sProcText = @sProcText + 'AS'  + @sCRLF  + @sCRLF 
        SET 	@sProcText = @sProcText + 'DECLARE @ModifiedByID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @CrmActionID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @TableName	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @TablePKID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @ColumnName	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @OldValue	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @NewValue	VARCHAR(50)'  + @sCRLF + @sCRLF 
	SET 	@sProcText = @sProcText + 'SELECT  @CrmActionID = TypeID FROM Type WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' + '''' +   'Delete' + '''' + ''  + @sCRLF + @sCRLF 
	        Print @sProcText
	        set @sProcText = ''

	-- get the columns for this table
	DECLARE crKeyFields cursor for
		SELECT	*
		FROM	dbo.fnTableColumnInfo(@sTableName)
		ORDER BY 3 DESC
	
	OPEN crKeyFields
	
	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
		
	-- Main loop for columns			
	WHILE (@@FETCH_STATUS = 0)
	 	BEGIN

                                -- get the primary column for this table
				IF (@bPrimaryKeyColumn = 1)
			  		BEGIN
           					SET @sPrimaryKey = @sColumnName
        		   		END

				SET @sProcText = @sProcText + ' 	SELECT	@OldValue = CONVERT(VARCHAR(50),d.' + @sColumnName + '),'  + @sCRLF 
                             		SET @sProcText = @sProcText + '                @TablePKID = d.' + @sPrimaryKey + ', '  + @sCRLF 
				SET @sProcText = @sProcText + ' 	     @ModifiedByID = d.ModifiedByID' + @sCRLF 
				SET @sProcText = @sProcText + ' 	FROM DELETED d '   + @sCRLF 
				SET @sProcText = @sProcText + '              INSERT INTO Audit (ModifiedByID, TypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)'  + @sCRLF 
				SET @sProcText = @sProcText + '              VALUES (@ModifiedByID, @CrmActionID, ' +  '''' + @sTableName + '''' + ', @TablePKID,'  +  '''' + @sColumnName +  '''' + ', @OldValue, @NewValue, GETDATE())'  + @sCRLF + @sCRLF 
		        	Print @sProcText
		        	set @sProcText = ''
			FETCH 	NEXT 
			FROM 	crKeyFields 
			INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
		END
	
	CLOSE crKeyFields
	DEALLOCATE crKeyFields

	SET @sProcText = @sCRLF  + ' GO' + @sCRLF  + @sCRLF 
	Print @sProcText

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE Procedure vp_GenerateTriggers
--'***************************************************************************
--'Procedure:	vp_GenerateTriggers
--'
--'Description:	Run I/U/D/S stored procedures for a specific table or all tables
--'
--' Created on:	02/12/2004
--' Created by:	Milos Krivka
--'
--' Modification Notes
--'  By          On                     Notes
--'  --------    --------------------  	--------------------------------
--'  
--'***************************************************************************

--**********************************
--
-- P A R A M E T E R S
--
--**********************************
(
       	@Table         	varchar(20),
       	@Action        	char(1),
        @Execute        bit = 0
)
AS
        --- first, clean up the SQL table
        set nocount on

        Declare @TableName as varchar(100)

	BEGIN
		IF upper(@Table) <> 'ALL'
		   BEGIN
                   	IF upper(@Action) = 'I' execute vp_InsertTrigger @Table, @Execute
			IF upper(@Action) = 'U' execute vp_UpdateTrigger @Table, @Execute
			IF upper(@Action) = 'D' execute vp_DeleteTrigger @Table, @Execute
                        IF upper(@Action) = 'A'
                           	BEGIN
					execute vp_InsertTrigger @Table, @Execute
					execute vp_UpdateTrigger @Table, @Execute
					execute vp_DeleteTrigger @Table, @Execute
				END
                   END
                ELSE
                   BEGIN
			declare table_cursor cursor for
				select name
			     	from sysobjects
				where xtype = 'U' 
				and name <> 'Audit'
				and name <> 'HTMLText'
				and name <> 'History'

			open table_cursor

			-- get the first row
			FETCH NEXT FROM table_cursor 
			INTO @TableName

			WHILE @@FETCH_STATUS = 0
				BEGIN
		                        IF upper(@Action) = 'I' 
						BEGIN
							execute vp_InsertTrigger @TableName, @Execute
						END 
					IF upper(@Action) = 'U'  
						BEGIN
							execute vp_UpdateTrigger @TableName, @Execute
						END 
					IF upper(@Action) = 'D'  
						BEGIN
							execute vp_DeleteTrigger @TableName, @Execute
						END
		                        IF upper(@Action) = 'A'
						BEGIN
							execute vp_InsertTrigger @TableName, @Execute
							execute vp_UpdateTrigger @TableName, @Execute
							execute vp_DeleteTrigger @TableName, @Execute
						END
	
					-- get the next table
					FETCH NEXT FROM table_cursor 
					INTO @TableName
				END

			-- cleanup
			CLOSE table_cursor
   			DEALLOCATE table_cursor

			 
                   END
	END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  PROC vp_InsertTrigger
	@sTableName varchar(128),
	@bExecute bit = 0
AS
BEGIN
	set nocount on
	DECLARE	@sProcText varchar(8000),
		@sColumnName varchar(128),
		@nColumnID smallint,
		@bPrimaryKeyColumn bit,
		@nAlternateType int,
		@nColumnLength int,
		@nColumnPrecision int,
		@nColumnScale int,
		@IsNullable bit, 
		@IsIdentity int,
		@sTypeName varchar(128),
		@sDefaultValue varchar(4000),
		@sCRLF char(2),
		@sTAB char(1),
                @sPrimaryKey varchar(128)
	
	DECLARE @ModifiedByID	UNIQUEIDENTIFIER
	DECLARE @CrmActionID	UNIQUEIDENTIFIER
	DECLARE @TableName	VARCHAR(50)
	DECLARE @TablePKID	UNIQUEIDENTIFIER
	DECLARE @ColumnName	VARCHAR(50)
	DECLARE @OldValue	VARCHAR(50)
	DECLARE @NewValue	VARCHAR(50)
	
	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''
	SET 	@ModifiedByID = '00000000-0000-0000-0000-000000000000'
        SET     @sPrimaryKey = 'unknown'

	SET 	@sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''tr_' + @sTableName + '_I'')' + @sCRLF
	SET 	@sProcText = @sProcText + @sTAB + 'DROP trigger tr_' + @sTableName + '_I' + @sCRLF
	SET 	@sProcText = @sProcText + 'GO' + @sCRLF
	SET 	@sProcText = @sProcText + 'CREATE trigger tr_' + @sTableName + '_I ON ' + @sTableName + ' FOR INSERT' + @sCRLF 
	SET 	@sProcText = @sProcText + 'AS'  + @sCRLF  + @sCRLF 
        SET 	@sProcText = @sProcText + 'DECLARE @ModifiedByID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @CrmActionID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @TableName	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @TablePKID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @ColumnName	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @OldValue	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @NewValue	VARCHAR(50)'  + @sCRLF + @sCRLF 
	SET 	@sProcText = @sProcText + 'SELECT  @CrmActionID = TypeID FROM Type WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' + '''' +   'Insert' + '''' + ''  + @sCRLF + @sCRLF 
	        Print @sProcText
	        set @sProcText = ''

	-- get the columns for this table
	DECLARE crKeyFields cursor for
		SELECT	*
		FROM	dbo.fnTableColumnInfo(@sTableName)
		ORDER BY 3 DESC
	
	OPEN crKeyFields
	
	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
		
	-- Main loop for columns			
	WHILE (@@FETCH_STATUS = 0)
	 	BEGIN
			-- get the primary column for this table
			IF (@bPrimaryKeyColumn = 1)
			   BEGIN
           			SET @sPrimaryKey = @sColumnName
        		   END
 
			SET @sProcText = @sProcText + '  IF UPDATE('+ @sColumnName +')'  + @sCRLF 
			SET @sProcText = @sProcText + '    BEGIN'  + @sCRLF 
			SET @sProcText = @sProcText + '       SELECT  @TablePKID = i.' + @sPrimaryKey + ','  + @sCRLF 
			SET @sProcText = @sProcText + ' 		@OldValue = CONVERT(VARCHAR(50),d.' + @sColumnName + '),'  + @sCRLF 
			SET @sProcText = @sProcText + ' 		@NewValue = CONVERT(VARCHAR(50),i.'+ @sColumnName +'), ' + @sCRLF 
			SET @sProcText = @sProcText + ' 		@ModifiedByID = i.ModifiedByID' + @sCRLF 
			SET @sProcText = @sProcText + ' 	FROM INSERTED i LEFT OUTER JOIN DELETED d on i.' + @sPrimaryKey + ' = d.' + @sPrimaryKey + ' '  + @sCRLF 
			SET @sProcText = @sProcText + '        IF @OldValue <> @NewValue or (@OldValue is null and (@NewValue is not null or @NewValue <> ' + '''' + '''' +')) or (@OldValue is not null and (@NewValue is null or @NewValue = ' + '''' + '''' +' ))'  + @sCRLF 
			SET @sProcText = @sProcText + '          BEGIN'  + @sCRLF 
			SET @sProcText = @sProcText + '              INSERT INTO Audit (ModifiedByID, TypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)'  + @sCRLF 
			SET @sProcText = @sProcText + '              VALUES (@ModifiedByID, @CrmActionID, ' +  '''' + @sTableName + '''' + ', @TablePKID, ' +  '''' + @sColumnName +  '''' + ', @OldValue, @NewValue, GETDATE())'  + @sCRLF 
			SET @sProcText = @sProcText + '          END'  + @sCRLF 
			SET @sProcText = @sProcText + '     END' + @sCRLF  + @sCRLF 
	        		Print @sProcText
	        		set @sProcText = ''
			
			FETCH 	NEXT 
			FROM 	crKeyFields 
			INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
		
                      	
		END
	
	CLOSE crKeyFields
	DEALLOCATE crKeyFields


	SET @sProcText = @sCRLF  + ' GO' + @sCRLF  + @sCRLF 
	Print @sProcText

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  PROC vp_UpdateTrigger
	@sTableName varchar(128),
	@bExecute bit = 0
AS
BEGIN
	set nocount on
	DECLARE	@sProcText varchar(8000),
		@sColumnName varchar(128),
		@nColumnID smallint,
		@bPrimaryKeyColumn bit,
		@nAlternateType int,
		@nColumnLength int,
		@nColumnPrecision int,
		@nColumnScale int,
		@IsNullable bit, 
		@IsIdentity int,
		@sTypeName varchar(128),
		@sDefaultValue varchar(4000),
		@sCRLF char(2),
		@sTAB char(1),
                @sPrimaryKey varchar(128)
	
	DECLARE @ModifiedByID	UNIQUEIDENTIFIER
	DECLARE @CrmActionID	UNIQUEIDENTIFIER
	DECLARE @TableName	VARCHAR(50)
	DECLARE @TablePKID	UNIQUEIDENTIFIER
	DECLARE @ColumnName	VARCHAR(50)
	DECLARE @OldValue	VARCHAR(50)
	DECLARE @NewValue	VARCHAR(50)
	
	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''
	SET 	@ModifiedByID = '00000000-0000-0000-0000-000000000000'
        SET     @sPrimaryKey = 'unknown'

	SET 	@sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''tr_' + @sTableName + '_U'')' + @sCRLF
	SET 	@sProcText = @sProcText + @sTAB + 'DROP trigger tr_' + @sTableName + '_U' + @sCRLF
	SET 	@sProcText = @sProcText + 'GO' + @sCRLF		 
	SET 	@sProcText = @sProcText + 'CREATE trigger tr_' + @sTableName + '_U ON ' + @sTableName + ' FOR UPDATE' + @sCRLF 	 
	SET 	@sProcText = @sProcText + 'AS'  + @sCRLF  + @sCRLF          
        SET 	@sProcText = @sProcText + 'DECLARE @ModifiedByID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @CrmActionID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @TableName	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @TablePKID	UNIQUEIDENTIFIER'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @ColumnName	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @OldValue	VARCHAR(50)'  + @sCRLF 
	SET 	@sProcText = @sProcText + 'DECLARE @NewValue	VARCHAR(50)'  + @sCRLF + @sCRLF 	 
	SET 	@sProcText = @sProcText + 'SELECT  @CrmActionID = TypeID FROM Type WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' + '''' +   'Update' + '''' + ''  + @sCRLF + @sCRLF 
	        Print @sProcText
	        set @sProcText = ''

	-- get the columns for this table
	DECLARE crKeyFields cursor for
		SELECT	*
		FROM	dbo.fnTableColumnInfo(@sTableName)
		ORDER BY 3 DESC
	
	OPEN crKeyFields
	
	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
		
	-- Main loop for columns			
	WHILE (@@FETCH_STATUS = 0)
	 	BEGIN
			-- get the primary column for this table
			IF (@bPrimaryKeyColumn = 1)
			   BEGIN
           			SET @sPrimaryKey = @sColumnName
        		   END
                        ELSE
                           BEGIN
				SET @sProcText = @sProcText + '  IF UPDATE('+ @sColumnName +')'  + @sCRLF 
				SET @sProcText = @sProcText + '    BEGIN'  + @sCRLF 
				SET @sProcText = @sProcText + '       SELECT  	@TablePKID = i.' + @sPrimaryKey + ','  + @sCRLF 
				SET @sProcText = @sProcText + ' 		@OldValue = CONVERT(VARCHAR(50),d.' + @sColumnName + '),'  + @sCRLF 
				SET @sProcText = @sProcText + ' 		@NewValue = CONVERT(VARCHAR(50),i.'+ @sColumnName +'), ' + @sCRLF 
                                SET @sProcText = @sProcText + ' 		@ModifiedByID = i.ModifiedByID' + @sCRLF 
				SET @sProcText = @sProcText + ' 	FROM INSERTED i LEFT OUTER JOIN DELETED d on i.' + @sPrimaryKey + ' = d.' + @sPrimaryKey + ' '  + @sCRLF 
				SET @sProcText = @sProcText + '        IF @OldValue <> @NewValue '  + @sCRLF 	
				SET @sProcText = @sProcText + '          BEGIN'  + @sCRLF 
				SET @sProcText = @sProcText + '              INSERT INTO Audit (ModifiedByID, TypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)'  + @sCRLF 
				SET @sProcText = @sProcText + '              VALUES (@ModifiedByID, @CrmActionID, ' +  '''' + @sTableName + '''' + ', @TablePKID, ' +  '''' + @sColumnName +  '''' + ', @OldValue , @NewValue, GETDATE())'  + @sCRLF 
				SET @sProcText = @sProcText + '          END'  + @sCRLF 
				SET @sProcText = @sProcText + '     END' + @sCRLF  + @sCRLF 
				Print @sProcText
	        		set @sProcText = ''
			    END

			FETCH 	NEXT 
			FROM 	crKeyFields 
			INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue
		
                      	
		END
	
	CLOSE crKeyFields
	DEALLOCATE crKeyFields

	SET @sProcText = @sCRLF  + ' GO' + @sCRLF  + @sCRLF 
	Print @sProcText

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Type]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Type]

GO

 

CREATE TABLE [dbo].[Type] (

            [TypeID] [uniqueidentifier] NOT NULL ,

            [TypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

            [SortOrder] [int] NOT NULL ,

            [isSystemData] [bit] NOT NULL ,

            [ModifiedByID] [uniqueidentifier] NOT NULL 

) ON [PRIMARY]

GO

INSERT INTO Type ( TypeID, TypeName, Type, Description, SortOrder, isSystemData, ModifiedByID ) 

         VALUES ( 'B4A3C05E-EB76-449B-9095-F2DE60069C09', 'Action', 'Insert', 'INS', 1, 0, '00000000-0000-0000-0000-000000000000' ) 

go

INSERT INTO Type ( TypeID, TypeName, Type, Description, SortOrder, isSystemData, ModifiedByID ) 

         VALUES ( 'B8C25EC1-B195-416E-84D4-F3629E07B92B', 'Action', 'Delete', 'DEL', 1, 0, '00000000-0000-0000-0000-000000000000' ) 

Go

INSERT INTO Type ( TypeID, TypeName, Type, Description, SortOrder, isSystemData, ModifiedByID ) 

         VALUES ( '16EE356E-C53F-455E-82E8-79F9D2DFE0EA', 'Action', 'Update', 'UPD', 1, 0, '00000000-0000-0000-0000-000000000000' ) 

Go

Rate

5 (2)

Share

Share

Rate

5 (2)