Auditing Solution - Generate Triggers automatically

--First the DDL which creates the table where the audits are stored. I call this Progress instead of Audit becuase in our database Progress is used to log all kinds of other events
CREATE TABLE [dbo].[Progress] (
[nProgressID] [int] IDENTITY (1, 1) NOT NULL ,
[cProgressType] [char] (10) NOT NULL ,
[nProgress] [int] NULL ,
[cProgress] [varchar] (255) NULL ,
[cSource] [char] (30) NULL ,
[cWho][CHAR] (10) NULL, 
[dtLastUpd] [datetime] NULL 
)
GO

CREATE TABLE [dbo].[ProgressTypes] (
[cProgressType] [char] (10) NOT NULL ,
[cProgressTypeDesc] [varchar] (255) NULL ,
)
GO

ALTER TABLE [dbo].[Progress] WITH NOCHECK ADD 
CONSTRAINT [PK_PROGRESS] PRIMARY KEY  CLUSTERED 
(
[nProgressID]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ProgressTypes] WITH NOCHECK ADD 
CONSTRAINT [PK_PROGRESSTYPES] PRIMARY KEY  CLUSTERED 
(
[cProgressType]
)  ON [PRIMARY] 
GO

 CREATE  INDEX [FK_PROT_PRO] ON [dbo].[Progress]([cProgressType]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_PRO_cSource] ON [dbo].[Progress]([cSource]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_PRO_dtLastUpd] ON [dbo].[Progress]([dtLastUpd]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Progress] ADD 
CONSTRAINT [FKC_PROT_PRO] FOREIGN KEY 
(
[cProgressType]
) REFERENCES [dbo].[ProgressTypes] (
[cProgressType]
)
GO
INSERT progresstypes VALUES ('AUDIT_DEL','Table Audit on DELETES')
INSERT progresstypes VALUES ('AUDIT_INS','Table Audit on INSERTS')
INSERT progresstypes VALUES ('AUDIT_UPD','Table Audit on UPDATES')
GO

--Now the two procs...


SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO


CREATE PROCEDURE dbo.utl_AuditTrigger_Create
@asTableNameCHAR(30)
,@asTriggerTypeCHAR(6)
,@asBody1VARCHAR(8000)
,@asBody2VARCHAR(8000)
,@asBody3VARCHAR(8000)
,@asBody4VARCHAR(8000)
,@asUpdateJoinVARCHAR(1000)
,@anErrorINT OUTPUT
AS
/********************************************************************************************************************************************************
Purpose: Actually creates the triggers

Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date WhoComment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
29/11/00JHAYNEInitial Version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/SET NOCOUNT ON

DECLARE
@lsSourceCHAR(30)
,@lsErrorVARCHAR(255)
,@lsDropTriggerVARCHAR(255)
,@lsHeaderVARCHAR(2000)
,@lsFooterVARCHAR(1000)
,@lsBody1VARCHAR(4000)
,@lsBody1aVARCHAR(4000)
,@lsBody2VARCHAR(4000)
,@lsBody2aVARCHAR(4000)
,@lsBody3VARCHAR(4000)
,@lsBody3aVARCHAR(4000)
,@lsBody4VARCHAR(4000)
,@lsBody4aVARCHAR(4000)
,@lsWhereVARCHAR(50)

--Initialise
SELECT @lsSource = OBJECT_NAME(@@PROCID)
SELECT @lsWhere = CHAR(13)

--Drop Trigger
SELECT
@lsDropTrigger = 
+ 'IF EXISTS (SELECT * FROM SysObjects WHERE Type = ''TR'' AND Name = ''trg_Audit_' 
+ RTRIM(@asTableName)
+ '_'
+ LEFT(@asTriggerType, 3)
+ ''') DROP TRIGGER trg_Audit_'
+ RTRIM(@asTableName)
+ '_' 
+ LEFT(@asTriggerType, 3)
EXEC(@lsDropTrigger)

SELECT 
@lsHeader = 
+ 'CREATE TRIGGER dbo.trg_Audit_'
+ RTRIM(@asTableName)
+ '_' 
+ LEFT(@asTriggerType, 3)
+ ' ON ' 
+ RTRIM(@asTableName) + CHAR(13)
+ 'FOR ' 
+ @asTriggerType + CHAR(13)
+ 'AS' + CHAR(13) + CHAR(10) + CHAR(13)
+ '--Generated programmatically. Please do not edit' + CHAR(13) + CHAR(10) + CHAR(13)
+ 'INSERT Progress' + CHAR(13)
+ CHAR(9) + '(' + CHAR(13)
+ CHAR(9) + 'cProgressType' + CHAR(13)
+ CHAR(9) + ',cSource' + CHAR(13)
+ CHAR(9) + ',cWho' + CHAR(13)
+ CHAR(9) + ',dtLastUpd' + CHAR(13)
+ CHAR(9) + ',cProgress' + CHAR(13)
+ CHAR(9) + ',nProgress' + CHAR(13)
+ CHAR(9) + ')' + CHAR(13)
+ 'SELECT' + CHAR(13)
+ CHAR(9) + '''AUDIT_' + LEFT(@asTriggerType, 3) + '''' + CHAR(13)
+ CHAR(9) + ',''' + RTRIM(@asTableName) + '''' + CHAR(13)
+ CHAR(9) + ',SUBSTRING(SUSER_SNAME(), CHARINDEX(''\'', SUSER_SNAME()) + 1, 10)' + CHAR(13)
+ CHAR(9) + ',GETDATE()' + CHAR(13)
+ CHAR(9) + ',LEFT(' + CHAR(13)

--Set Body
SELECT 
@lsBody1 = LEFT(@asBody1, 4000)
,@lsBody1a = SUBSTRING(@asBody1, 4001, 4000)
,@lsBody2 = LEFT(@asBody2, 4000)
,@lsBody2a = SUBSTRING(@asBody2, 4001, 4000)
,@lsBody3 = LEFT(@asBody3, 4000)
,@lsBody3a = SUBSTRING(@asBody3, 4001, 4000)
,@lsBody4 = LEFT(@asBody4, 4000)
,@lsBody4a = SUBSTRING(@asBody4, 4001, 4000)

--Set Join for UPDATE
IF @asTriggerType = 'UPDATE' SELECT @asUpdateJoin = CHAR(9) + 'JOIN Deleted D ON' + RTRIM(@asUpdateJoin)

SELECT @lsFooter = 
CHAR(13) 
+ 'FROM' + CHAR(13)
+ CHAR(9) + CASE WHEN @asTriggerType = 'DELETE' THEN 'Deleted ' ELSE 'Inserted' END
+ ' I' + CHAR(13)
+ RTRIM(@asUpdateJoin) + CHAR(13)
+ @lsWhere

--Create Trigger
IF @asBody2 IS NULL
EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsFooter)
ELSE
IF @asBody3 IS NULL
EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsBody2 + @lsBody2a + @lsFooter)
ELSE
IF @asBody4 IS NULL
EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsBody2 + @lsBody2a + @lsBody3 + @lsBody3a + @lsFooter)
ELSE
EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsBody2 + @lsBody2a + @lsBody3 + @lsBody3a + @lsBody4 + @lsBody4a + @lsFooter)

--Report progress
IF @anError = 0 
BEGIN
PRINT RTRIM(@asTriggerType) + ' trigger for table ' + RTRIM(@asTableName) + ' created'
END
ELSE
BEGIN
PRINT 'Could not create ' + RTRIM(@asTriggerType) + ' trigger for table ' + RTRIM(@asTableName)
COMMIT
END

SET NOCOUNT OFF


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO


CREATE PROCEDURE dbo.utl_AuditTriggerTable
@cTableNameCHAR(30)
,@cIsAuditINSTriggerCHAR(1)
,@cIsAuditUPDTriggerCHAR(1)
,@cIsAuditDELTriggerCHAR(1)
,@nErrorINT OUTPUT
AS
/********************************************************************************************************************************************************
Purpose: Create all Audit Triggers for given table

Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date WhoComment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
27/11/00JHAYNEInitial Version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/DECLARE
@cProcName CHAR(30)
,@cProgressVARCHAR(255)
,@cColNameCHAR(30)
,@cTypeCHAR(30)
,@cPKCHAR(1)
,@cIdentityCHAR(1)
,@cIdentityColCHAR(30)
,@cColChar_IVARCHAR(255)
,@cColChar_DVARCHAR(255)
,@cINSBody1VARCHAR(8000)
,@cINSBody2VARCHAR(8000)
,@cINSBody3VARCHAR(8000)
,@cINSBody4VARCHAR(8000)
,@cUPDBody1VARCHAR(8000)
,@cUPDBody2VARCHAR(8000)
,@cUPDBody3VARCHAR(8000)
,@cUPDBody4VARCHAR(8000)
,@cUpdateJoinVARCHAR(1000)
,@cINSLineVARCHAR(1000)
,@cUPDLineVARCHAR(1000)
,@cINSVarToUseTINYINT
,@cUPDVarToUseTINYINT
,@cIsFirstColCHAR(1)

--Initialise
SET NOCOUNT ON
SELECT @cProcName = OBJECT_NAME(@@PROCID)
SELECT @nError = 0
SELECT @cINSVarToUse = 1, @cUPDVarToUse = 1
SELECT @cIsFirstCol = 'Y'
SELECT @cINSBody1 = '', @cINSBody2 = '', @cINSBody3 = '', @cINSBody4 = ''
SELECT @cUPDBody1 = '', @cUPDBody2 = '', @cUPDBody3 = '', @cUPDBody4 = ''

--Declare Cursor ***--
DECLARE Columns CURSOR FOR
SELECT 
C.Name cName
,ST.Name cType
,CASE WHEN C.AutoVal IS NULL THEN 'N' ELSE 'Y' END cIdentity
,CASE WHEN SK.ColID IS NULL THEN 'N' ELSE 'Y' END cPK
FROM
SysObjects T 
JOIN SysColumns C ON C.ID = T.ID
JOIN SysTypes ST ON ST.XUserType = C.XUserType
LEFT OUTER JOIN SysIndexes SI ON SI.ID = T.ID AND (SI.Status = 2050 or SI.Status = 18450)
LEFT OUTER JOIN SysIndexKeys SK on SK.id = T.ID AND SK.IndID = SI.IndID AND SK.ColID = C.ColID
WHERE
T.Name = @cTableName
AND T.Type = 'U'
ORDER BY
4 DESC
,SK.KeyNo ASC
,C.ColOrder

SELECT @nError = @@ERROR
IF @nError <> 0 
BEGIN
PRINT' Could not declare cursor for Columns'
RETURN
END

--Open Cursor
OPEN Columns

--Loop through rows and create build up trigger strings
SELECT @cUpdateJoin = '', @cIdentityCol = '0'
WHILE 1=1
BEGIN
--Fetch Next Row
FETCH NEXT FROM Columns INTO @cColName, @cType, @cIdentity, @cPK

--Exit loop if last row reached
IF @@FETCH_STATUS  = -1 BREAK

--Check FETCH_STATUS as expected ***--
IF @@FETCH_STATUS <> 0 
BEGIN
SELECT @nError = -1
PRINT 'Row fetched is missing when looping through Table columns'
CLOSE Columns
DEALLOCATE Columns
RETURN
END

--Ensure a PK exists
IF @cIsFirstCol = 'Y'
BEGIN
IF @cPK = 'N'
BEGIN
SELECT @nError = -1
PRINT 'No Primary Key for table:' + RTRIM(@cTableName)
CLOSE Columns
DEALLOCATE Columns
RETURN
END
END

--Get first Identity Column
If @cIdentityCol = '0' AND @cIdentity = 'Y'
BEGIN
SELECT @cIdentityCol = 'I.' + @cColName
END

--Convert column to char
SELECT @cColChar_I = 
CASE 
WHEN RTRIM(@cType) = 'char' OR RTRIM(@cType) = 'varchar' OR RTRIM(@cType) = 'nvarchar' OR RTRIM(@cType) = 'text'
THEN 'I.' + RTRIM(@cColName)
WHEN RTRIM(@cType) = 'int' OR RTRIM(@cType) = 'smallint' OR RTRIM(@cType) = 'tinyint' OR RTRIM(@cType) = 'decimal' OR RTRIM(@cType) = 'numeric' 
THEN 'CONVERT(CHAR(30), ' + 'I.' + RTRIM(@cColName) + ')'
WHEN RTRIM(@cType) = 'datetime'
THEN 'CASE WHEN CONVERT(CHAR(8), I.' + RTRIM(@cColName) + ', 114) = ''00:00:00'' THEN CONVERT(CHAR(11), ' + 'I.' + RTRIM(@cColName) + ', 113) ELSE CONVERT(CHAR(20), ' + 'I.' + RTRIM(@cColName) + ', 113) END'
ELSE '?' 
END
SELECT @cColChar_D = REPLACE(@cColChar_I, 'I.', 'D.')
IF RTRIM(@cColChar_I) = '?' 
BEGIN
PRINT 'Unexpected datatype: ' + RTRIM(@cType) + ' for column: ' + RTRIM(@cColName) + ' of table:' + RTRIM(@cTableName)
CLOSE Columns
DEALLOCATE Columns
RETURN
END

--Primary Key column?
IF @cPK = 'Y'
--Primary Key
BEGIN
SELECT @cUpdateJoin = RTRIM(@cUpdateJoin) + ' AND D.' + RTRIM(@cColName) + ' = I.' + RTRIM(@cColName)
SELECT @cINSLine = '+ ''' + CASE WHEN @cIsFirstCol = 'Y' THEN RTRIM(@cColName) ELSE ', ' + RTRIM(@cColName) END + ':'' + RTRIM(' + RTRIM(@cColChar_I) + ')'
SELECT @cUPDLine = @cINSLine
END
ELSE
--Not a Primary Key
BEGIN
IF RTRIM(@cType) <> 'text'
BEGIN
SELECT @cINSLine = '+ CASE WHEN ISNULL(' + RTRIM(@cColChar_I) + ', '''') = '''' THEN RTRIM('''') ELSE '', ' + RTRIM(@cColName) + ':'' + RTRIM(' + RTRIM(@cColChar_I) + ') END'
SELECT @cUPDLine = '+ CASE WHEN ISNULL(' + RTRIM(@cColChar_I) + ', '''') = ISNULL(' + RTRIM(@cColChar_D) + ', '''')  THEN RTRIM('''') ELSE '', ' + RTRIM(@cColName) + ':'' + RTRIM(ISNULL(' + RTRIM(@cColChar_D) + ', ''NULL'')) + ''-'' + RTRIM(ISNULL(' + RTRIM(@cColChar_I) + ', ''NULL'')) END'
END
ELSE
BEGIN
SELECT @cINSLine = ''
SELECT @cUPDLine = '' --Text values not available in Inserted and Deleted trigger tables. Therefore decision taken not to audit text fields at all.
END
END

--Store INS line in a var
IF @cINSVarToUse = 1
BEGIN
--Is there enough room left in current var?
IF LEN(@cINSLine) + LEN(@cINSBody1) < 6900
SELECT @cINSBody1 = RTRIM(@cINSBody1) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
ELSE
BEGIN
SELECT @cINSVarToUse = 2
SELECT @cINSBody2 = CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
END
END
ELSE
IF @cINSVarToUse = 2
BEGIN
--Is there enough room left in current var?
IF LEN(@cINSLine) + LEN(@cINSBody2) < 6900
SELECT @cINSBody2 = RTRIM(@cINSBody2) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
ELSE
BEGIN
SELECT @cINSVarToUse = 3
SELECT @cINSBody3 = CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
END
END
ELSE
IF @cINSVarToUse = 3
BEGIN
--Is there enough room left in current var?
IF LEN(@cINSLine) + LEN(@cINSBody3) < 6900
SELECT @cINSBody3 = RTRIM(@cINSBody3) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
ELSE
BEGIN
SELECT @cINSVarToUse = 4
SELECT @cINSBody4 = CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
END
END
ELSE
IF @cINSVarToUse = 4
BEGIN
--Is there enough room left in current var?
IF LEN(@cINSLine) + LEN(@cINSBody4) < 6900
SELECT @cINSBody4 = RTRIM(@cINSBody4) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 

ELSE
BEGIN
SELECT @nError = -1
PRINT 'Overflow of variable space on INS column: ' + RTRIM(@cColName) + ' of table:' + RTRIM(@cTableName)
CLOSE Columns
DEALLOCATE Columns
RETURN
END
END

--Store UPD line in a var
IF @cUPDVarToUse = 1
BEGIN
--Is there enough room left in current var?
IF LEN(@cUPDLine) + LEN(@cUPDBody1) < 6900
SELECT @cUPDBody1 = RTRIM(@cUPDBody1) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
ELSE
BEGIN
SELECT @cUPDVarToUse = 2
SELECT @cUPDBody2 = CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
END
END
ELSE
IF @cUPDVarToUse = 2
BEGIN
--Is there enough room left in current var?
IF LEN(@cUPDLine) + LEN(@cUPDBody2) < 6900
SELECT @cUPDBody2 = RTRIM(@cUPDBody2) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
ELSE
BEGIN
SELECT @cUPDVarToUse = 3
SELECT @cUPDBody3 = CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
END
END
ELSE
IF @cUPDVarToUse = 3
BEGIN
--Is there enough room left in current var?
IF LEN(@cUPDLine) + LEN(@cUPDBody3) < 6900
SELECT @cUPDBody3 = RTRIM(@cUPDBody3) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
ELSE
BEGIN
SELECT @cUPDVarToUse = 4
SELECT @cUPDBody4 = CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
END
END
ELSE
IF @cUPDVarToUse = 4
BEGIN
--Is there enough room left in current var?
IF LEN(@cUPDLine) + LEN(@cUPDBody4) < 6900
SELECT @cUPDBody4 = RTRIM(@cUPDBody4) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
ELSE
BEGIN
SELECT @nError = -1
PRINT 'Overflow of variable space on UPD column: ' + RTRIM(@cColName) + ' of table:' + RTRIM(@cTableName)
CLOSE Columns
DEALLOCATE Columns
RETURN
END
END
SELECT @cIsFirstCol = 'N'

END

--Release Cursor
CLOSE Columns
DEALLOCATE Columns

--Finish off INS Body
IF @cINSVarToUse = 1
SELECT @cINSBody1 = RTRIM(@cINSBody1) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
IF @cINSVarToUse = 2
SELECT @cINSBody2 = RTRIM(@cINSBody2) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
IF @cINSVarToUse = 3
SELECT @cINSBody3 = RTRIM(@cINSBody3) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
SELECT @cINSBody4 = RTRIM(@cINSBody4) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)

--Finish off UPD Body
IF @cUPDVarToUse = 1
SELECT @cUPDBody1 = RTRIM(@cUPDBody1) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
IF @cUPDVarToUse = 2
SELECT @cUPDBody2 = RTRIM(@cUPDBody2) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
IF @cUPDVarToUse = 3
SELECT @cUPDBody3 = RTRIM(@cUPDBody3) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
SELECT @cUPDBody4 = RTRIM(@cUPDBody4) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)

--Create INSERT trigger for table
IF @cIsAuditINSTrigger = 'Y'
BEGIN
EXEC utl_AuditTrigger_Create 
@cTableName
,'INSERT'
,@cINSBody1
,@cINSBody2
,@cINSBody3
,@cINSBody4
,''
,@nError OUTPUT
IF @nError <> 0 RETURN --Error already reported
END

--Create DELETE trigger for table
IF @cIsAuditDELTrigger = 'Y'
BEGIN
EXEC utl_AuditTrigger_Create 
@cTableName
,'DELETE'
,@cINSBody1
,@cINSBody2
,@cINSBody3
,@cINSBody4
,''
,@nError OUTPUT
IF @nError <> 0 RETURN --Error already reported
END

--Create UPDATE trigger for table
IF @cIsAuditUPDTrigger = 'Y'
BEGIN
SELECT @cUpdateJoin = SUBSTRING(@cUpdateJoin,5, LEN(@cUpdateJoin) - 4)
EXEC utl_AuditTrigger_Create 
@cTableName
,'UPDATE'
,@cUPDBody1
,@cUPDBody2
,@cUPDBody3
,@cUPDBody4
,@cUpdateJoin
,@nError OUTPUT
IF @nError <> 0 RETURN --Error already reported
END

--End Proc
SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO