Technical Article

Modification to Automate Audit Trigger Generation

,

This is a modification to Automate Audit Trigger Generation at http://www.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)
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_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
SELECTc.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
FROMsyscolumns c
INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
WHEREc.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 @CrmActionIDVARCHAR(10)'
+ @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TableNameVARCHAR(50)'
+ @sCRLF

SET @sProcText = @sProcText + 'DECLARE @TablePKID
NVARCHAR(300)'  + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ColumnNameVARCHAR(50)'
+ @sCRLF
SET @sProcText = @sProcText + 'DECLARE @OldValueVARCHAR(50)'
+ @sCRLF
SET @sProcText = @sProcText + 'DECLARE @NewValueVARCHAR(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*
FROMdbo.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*
FROMdbo.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 @CrmActionIDVARCHAR(10)'
+ @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TableNameVARCHAR(50)'
+ @sCRLF
SET @sProcText = @sProcText + 'DECLARE @TablePKID
NVARCHAR(300)'  + @sCRLF
SET @sProcText = @sProcText + 'DECLARE @ColumnNameVARCHAR(50)'
+ @sCRLF
SET @sProcText = @sProcText + 'DECLARE @OldValueVARCHAR(50)'
+ @sCRLF
SET @sProcText = @sProcText + 'DECLARE @NewValueVARCHAR(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*
FROMdbo.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

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,497 reads