Technical Article

Modification to Automate Audit Trigger Generation

,

This is a modification to Automate Audit Trigger Generation at http://qa.sqlservercentral.com/scripts/contributions/1073.asp by walkerjet. The changes were made to accommodate tables using different types for their primary keys, (i.e. int, smallint, char, etc.), add the ModifiedById and DTStamp columns, exclude legacy tables that do not have a primary key defined, exclude fields of type text, ntext, and image from the script generation. Finally enclose the entire script in BEGIN TRAN and COMMIT TRAN with a MARK.

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.

/*
The following changes were made:
1)  fnTableColumnInfo sets nAlternateType to 3 for text, ntext, and image columns.  This is used in the generate trigger procedures (vp_UpdateTrigger, vp_InsertTrigger, and vp_DeleteTrigger to prevent setting a trigger on the column and the resultant errors.

2)  The table PKID is stored as NVARCHAR(300) and thus allows different types for the primary keys of your tables.

3)  Column names are enclosed with [ ] to avoid errors when the table designs have allowed spaces in the column names (grrrr).
  NOTE:  This will not prevent errors if the column name has an apostrophe (Yes, I had a legacy table with [DateRec'd] and it throws off the entire
script!)

4)  Addition of vp_GenerateModifiedByAndDTStampColumns to create the script needed to add the ModifyByID and DTStamp columns to the tables.  In this rendition the ModifyByID is varchar(150) with a SUSER_SNAME() value set by the trigger and DTStamp with GETDATE().
    NOTE:  If ModifyByID or DTStamp exist then it will ensure they are the correct type and write the change script needed to convert them.

5) Addition of vp_DeleteGeneratedTriggers and sub procedures (vp_DelUpdateTrigger, vp_DelInsertTrigger, and vp_DelDeleteTrigger) to generate the script to remove the auditing .  NOTE:  Does not remove the ModifyByID and DTStamp columns.

6) Audit table stores the OldValue and NewValue as varchar(3000).

7) Renamed Table [Type] to [AuditType]

8) vp_GenerateTriggers modified to add:  'BEGIN TRAN ADDAUDITTriggers WITH MARK', ' COMMIT TRAN ADDAUDITTriggers', and exclude tables 'Audit', HTMLText', 'AuditType', and 'dtproperties'.*/
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

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

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

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

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[vp_DeleteGeneratedTriggers]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[vp_DeleteGeneratedTriggers]
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_GenerateModifiedByAndDTStampColumns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[vp_GenerateModifiedByAndDTStampColumns]
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].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Audit] GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[AuditType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[AuditType] 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
			WHEN t.name IN ('text', 'ntext','image') THEN 3
			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

CREATE TABLE [dbo].[Audit] (
	[AuditID] [int] IDENTITY (1, 1) NOT NULL ,
	[Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TablePKID] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ColumnName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OldValue] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NewValue] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DateModified] [datetime] NOT NULL ,
	[ModifiedById] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AuditType] (
	[TypeID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[TypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Type] [varchar] (10) 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] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DTStamp] [datetime] NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

GO

INSERT INTO AuditType ( 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 AuditType ( 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 AuditType ( TypeID, TypeName, Type, Description, SortOrder, isSystemData, ModifiedByID )

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

Go

CREATE  PROC vp_DelDeleteTrigger
	@sTableName varchar(128),
	@bExecute bit = 0
AS
BEGIN

	set nocount on
	DECLARE
		@sCRLF char(2),
		@sTAB char(1),
		@sProcText VARCHAR(4000)


	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''

	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
		        Print @sProcText
	        set @sProcText = ''



	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_DelInsertTrigger
	@sTableName varchar(128),
	@bExecute bit = 0
AS
BEGIN
	set nocount on
	DECLARE
		@sCRLF char(2),
		@sTAB char(1),
		@sProcText VARCHAR(4000)


	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''

	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
		        Print @sProcText
	        set @sProcText = ''



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

END

SET QUOTED_IDENTIFIER OFF


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



CREATE  PROC vp_DelUpdateTrigger
	@sTableName varchar(128),
	@bExecute bit = 0
AS
BEGIN
	set nocount on
	DECLARE
		@sCRLF char(2),
		@sTAB char(1),
		@sProcText VARCHAR(4000)


	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''

	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
		        Print @sProcText
	        set @sProcText = ''



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

END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO






CREATE Procedure vp_DeleteGeneratedTriggers
--'*************************************************************************
**
--'Procedure:	vp_DeleteGenTriggers
--'
--'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_DelInsertTrigger @Table, @Execute
			IF upper(@Action) = 'U' execute vp_DelUpdateTrigger @Table, @Execute
			IF upper(@Action) = 'D' execute vp_DelDeleteTrigger @Table, @Execute
                        IF upper(@Action) = 'A'
                           	BEGIN
					execute vp_DelInsertTrigger @Table, @Execute
					execute vp_DelUpdateTrigger @Table, @Execute
					execute vp_DelDeleteTrigger @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 <> 'AuditType'
				and name <> 'dtproperties'
				order by name

			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_DelInsertTrigger @TableName, @Execute
						END
					IF upper(@Action) = 'U'
						BEGIN
							execute
vp_DelUpdateTrigger @TableName, @Execute
						END
					IF upper(@Action) = 'D'
						BEGIN
							execute
vp_DelDeleteTrigger @TableName, @Execute
						END
		                        IF upper(@Action) = 'A'
						BEGIN
							execute
vp_DelInsertTrigger @TableName, @Execute
							execute
vp_DelUpdateTrigger @TableName, @Execute
							execute
vp_DelDeleteTrigger @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 ON
GO
SET ANSI_NULLS ON
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)

	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''
        SET     @sPrimaryKey = 'unknown'


	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
VARCHAR(150)'
+ @sCRLF
	SET 	@sProcText = @sProcText + 'DECLARE @CrmActionID	VARCHAR(10)'
+ @sCRLF
	SET 	@sProcText = @sProcText + 'DECLARE @TableName	VARCHAR(50)'
+ @sCRLF

	SET 	@sProcText = @sProcText + 'DECLARE @TablePKID
NVARCHAR(300)'  + @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 = [Type]
FROM
AuditType WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' +
'''' +   'Delete' + '''' + ''  + @sCRLF + @sCRLF


	-- 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
				IF (@nAlternateType = 3)
				   BEGIN
					SET @sProcText = @sProcText + ' --
Column  '+ @sColumnName +' is of type text, ntext, or image'  + @sCRLF
					SET @sProcText = @sProcText + ' --
Cannot use in INSERT, UPDATE,
OR DELETE Triggers' + @sCRLF  + @sCRLF
					Print @sProcText
				   END
				ELSE
				    IF (@sPrimaryKey <> 'unknown')
			              BEGIN
	        			Print @sProcText
				        set @sProcText = ''
					SET @sProcText = @sProcText + '
SELECT	@OldValue =
CONVERT(VARCHAR(50),d.[' + @sColumnName + ']),'  + @sCRLF
        	              		SET @sProcText = @sProcText + '
@TablePKID = CONVERT(NVARCHAR(300), d.[' + @sPrimaryKey + ']), '  + @sCRLF
					SET @sProcText = @sProcText + '
@ModifiedByID = d.ModifiedByID' +
@sCRLF
					SET @sProcText = @sProcText + '
FROM DELETED d '   + @sCRLF
					SET @sProcText = @sProcText + '
INSERT INTO Audit
(ModifiedByID, [Type], TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)'  + @sCRLF
					SET @sProcText = @sProcText + '
VALUES (SUSER_SNAME(),
@CrmActionID, ' +  '''' + @sTableName + '''' + ', @TablePKID,'  +  '''' + @sColumnName +  '''' + ', @OldValue, @NewValue, GETDATE())'  + @sCRLF + @sCRLF
		        		Print @sProcText
		        		set @sProcText = ''
				      END
				    ELSE
					Print '-- CANNOT Create DELETE
Trigger for Column ['+ @sColumnName + '], Table [' + @sTableName + '] does not have a primary key.'
			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 ON
GO
SET ANSI_NULLS ON
GO

CREATE  PROC vp_GenerateModifiedByAndDTStampColumns
	@sTableName varchar(128),
	@bExecute bit = 0
AS
BEGIN
	set nocount on
	DECLARE
		@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 @sProcText VARCHAR(4000),
		@ModifiedByIDPresent INT,
		@DTStampPresent INT

	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''
	SET 	@DTStampPresent = 0
	SET 	@ModifiedByIDPresent = 0
	-- 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
			IF (@sColumnName = 'ModifiedByID')
			   BEGIN
				if (@sTypeName = 'varchar' and
@nColumnLength = 150)
	           		   BEGIN
				      SET @ModifiedByIDPresent = 1
				      PRINT '-- Table ' + @sTableName + '
already has a column ModifiedByID of the correct type'
				   END
				ELSE
				   BEGIN
				    --  Column is present, but not of correct type
			      		SET @ModifiedByIDPresent = 2
				        PRINT '-- Table ' + @sTableName + '
already has a column DTStamp, BUT NOT of the correct type'
				   END
        		   END
			IF (@sColumnName = 'DTStamp')
			   BEGIN
				if (@sTypeName = 'datetime')
	           		   BEGIN
				      SET @DTStampPresent = 1
				      PRINT '-- Table ' + @sTableName + '
already has a column DTStamp of the correct type'
				   END
				ELSE
				   BEGIN
				    --  Column is present, but not of correct type
				      SET @DTStampPresent = 2
				      PRINT '-- Table ' + @sTableName + '
already has a column DTStamp, BUT NOT of the correct type'

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

	CLOSE crKeyFields
	DEALLOCATE crKeyFields

	-- Now Alter the Table for DTStamp
	   IF(@DTStampPresent = 0)
	   	BEGIN
		    SET @sProcText = ''
		    SET @sProcText = @sProcText + 'ALTER TABLE ' + @sTableName + + @sCRLF
		    SET @sProcText = @sProcText + @sTAB + 'ADD DTSTamp datetime' + @sCRLF
		    SET @sProcText = @sProcText + @sCRLF  + ' GO' + @sCRLF
+ @sCRLF
		    Print @sProcText
		END
	   IF(@DTStampPresent =  2)
	   	BEGIN
		    SET @sProcText = ''
		    SET @sProcText = @sProcText + 'ALTER TABLE ' + @sTableName + + @sCRLF
		    SET @sProcText = @sProcText + @sTAB + 'ALTER COLUMN DTSTamp datetime'
+ @sCRLF
		    SET @sProcText = @sProcText + @sCRLF  + ' GO' + @sCRLF
+ @sCRLF
		    Print @sProcText
		END
	-- Now Alter the Table for Modified by ID
	   IF(@ModifiedByIDPresent= 0)
	   	BEGIN
		    SET @sProcText = ''
		    SET @sProcText = @sProcText + 'ALTER TABLE ' + @sTableName + + @sCRLF
		    SET @sProcText = @sProcText + @sTAB + 'ADD ModifiedById varchar(150)'
+ @sCRLF
		    SET @sProcText = @sProcText + @sCRLF  + ' GO' + @sCRLF
+ @sCRLF
		    Print @sProcText
		END
	   IF(@ModifiedByIDPresent= 2)
	   	BEGIN
		    SET @sProcText = ''
		    SET @sProcText = @sProcText + 'ALTER TABLE ' + @sTableName + + @sCRLF
		    SET @sProcText = @sProcText + @sTAB + 'ALTER COLUMN ModifiedById varchar(150)' + @sCRLF
		    SET @sProcText = @sProcText + @sCRLF  + ' GO' + @sCRLF
+ @sCRLF
		    Print @sProcText
		END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
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
--'  RN        20 Jun 04  	Added :
vp_GenerateModifiedByAndDTStampColumns to generate ModifyBy and DTStamp columns
--'                                                   Added: Begin and
commit tran comments.
--'
--'*************************************************************************
**

--**********************************
--
-- 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
		Print 'BEGIN TRAN ADDAUDITTriggers WITH MARK'
		IF upper(@Table) <> 'ALL'
		   BEGIN
		execute vp_GenerateModifiedByAndDTStampColumns @Table, @Execute
                   	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 <> 'AuditType'
				and name <> 'dtproperties'
				order by name

			open table_cursor

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

			WHILE @@FETCH_STATUS = 0
				BEGIN
				execute
vp_GenerateModifiedByAndDTStampColumns @TableName, @Execute
 		                        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
	     Print   ' COMMIT TRAN ADDAUDITTriggers'
	END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
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)

	-- initialize variables
	SET	@sTAB = char(9)
	SET 	@sCRLF = char(13) + char(10)
	SET 	@sProcText = ''
        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
VARCHAR(150)'
+ @sCRLF
	SET 	@sProcText = @sProcText + 'DECLARE @CrmActionID	VARCHAR(10)'
+ @sCRLF
	SET 	@sProcText = @sProcText + 'DECLARE @TableName	VARCHAR(50)'
+ @sCRLF
	SET 	@sProcText = @sProcText + 'DECLARE @TablePKID
NVARCHAR(300)'  + @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 = [Type]
FROM
AuditType WHERE Typename = ' + '''' + 'Action' + '''' + ' AND Type = ' +
'''' +   'Insert' + '''' + ''  + @sCRLF + @sCRLF


	-- 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
				IF (@nAlternateType = 3)
				    BEGIN
					SET @sProcText = @sProcText + '--
Column  '+ @sColumnName +' is of type text, ntext, or image'  + @sCRLF
					SET @sProcText = @sProcText + '--
Cannot use in INSERT or UPDATE
Triggers' + @sCRLF  + @sCRLF
					Print @sProcText
		        		set @sProcText = ''
				    END
				ELSE
				    IF (@sPrimaryKey <> 'unknown')
			              BEGIN
	        			Print @sProcText
				        set @sProcText = ''
					SET @sProcText = @sProcText + '  IF UPDATE(['+ @sColumnName +'])'  + @sCRLF
					SET @sProcText = @sProcText + '
BEGIN'  + @sCRLF
					SET @sProcText = @sProcText + '
SELECT  @TablePKID =
CONVERT(NVARCHAR(300),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 + '
update CTbl set
ModifiedByID = suser_sname(), DTStamp = GETDATE()'  + @sCRLF
					SET @sProcText = @sProcText + '
from inserted i'
+ @sCRLF
					SET @sProcText = @sProcText + '
inner join ' +
@sTableName + ' CTbl'  + @sCRLF
					SET @sProcText = @sProcText + '
on i.[' +
@sPrimaryKey +'] = CTbl.[' + @sPrimaryKey +']'  + @sCRLF
					SET @sProcText = @sProcText + '
INSERT INTO Audit
(ModifiedByID, [Type], TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified)'  + @sCRLF
					SET @sProcText = @sProcText + '
VALUES (SUSER_SNAME(),
@CrmActionID, ' +  '''' + @sTableName + '''' + ', @TablePKID, ' +  '''' + @sColumnName +  '''' + ', @OldValue, @NewValue, GETDATE())'  + @sCRLF
					SET @sProcText = @sProcText + '
END'  + @sCRLF
					SET @sProcText = @sProcText + '
END' + @sCRLF  + @sCRLF
	        			Print @sProcText
	        			set @sProcText = ''
				      END
				    ELSE
					Print '-- CANNOT Create INSERT
Trigger for Column ['+ @sColumnName + '], Table [' + @sTableName + '] does not have a primary key.'
			    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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,069 reads