Skip to content

Instantly share code, notes, and snippets.

@edeustace
Created April 13, 2012 15:01
Show Gist options
  • Save edeustace/2377468 to your computer and use it in GitHub Desktop.
Save edeustace/2377468 to your computer and use it in GitHub Desktop.
A stored procedure for sqlserver 2008 to generate history tables and triggers
/************************************************************************************************************
Inspired by: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84331
Created By: Bryan Massey
Created On: 3/11/2007
Comments: Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ( @TableName + '_History') to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
4) Writes simple script to pre-populate the Audit table with the current values of the Audited table.
Usage:
EXEC [dbo].[AutoGenerateAuditTableAndTrigger] @TableName = N'tblBlah', @CreateTrigger = N'Y', @ExecuteProcedure = N'Y'
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY - Modified By - Description of Changes
************************************************************************************************************/
CREATE PROCEDURE DBO.AutoGenerateAuditTableAndTrigger
@TableName VARCHAR(200),
@CreateTrigger CHAR(1) = 'Y', -- optional parameter; defaults to "Y"
@ExecuteProcedure CHAR(1) = 'N'
AS
DECLARE @SQLTable VARCHAR(MAX), @SQLTrigger VARCHAR(MAX), @FieldList VARCHAR(6000), @FirstField VARCHAR(200), @HistoryTableName VARCHAR(MAX)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)
SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ''
SET @SQLTable = ''
DECLARE @FieldName VARCHAR(100), @DataType VARCHAR(50)
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10), @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)
DECLARE CurHistoryTable CURSOR FOR
-- query system tables to get table schema
SELECT CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType,
CONVERT(VARCHAR(10),SC.max_length) AS FieldLength, CONVERT(VARCHAR(10), SC.precision) AS FieldPrecision,
CONVERT(VARCHAR(10), SC.Scale) AS FieldScale,
CASE SC.Is_Nullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls
FROM Sys.Objects SO
INNER JOIN Sys.Columns SC ON SO.object_ID = SC.object_ID
INNER JOIN Sys.Types ST ON SC.system_type_id = ST.system_type_id
WHERE SO.type = 'u' AND SO.Name = @TableName
ORDER BY SO.[name], SC.Column_Id ASC
OPEN CurHistoryTable
FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
WHILE @@FETCH_STATUS = 0
BEGIN
SET @HistoryTableName = @TableName + '_History'
-- create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FieldList = @FieldName
SET @FirstField = @FieldName
END
ELSE
BEGIN
SET @FieldList = @FieldList + ', ' + @FieldName
END
-- if we are at the start add the std audit columns in front
IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = 'CREATE TABLE [DBO].[' + @HistoryTableName + '] (' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[ID] [INT] IDENTITY NOT NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[Operation]' + @TAB + 'CHAR (1) NOT NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[DateOfAction]' + @TAB + 'DATETIME NOT NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '['+ @TableName +'_ID]' + @TAB + '[INT] NOT NULL,' + @CRLF
END
-- SET @SQLTable = @TAB + @SQLTable + '/*@FieldName:'+ @FieldName +', @DataType: ' + @DataType +' @FieldLength: ' + @FieldLength +'*/' + @CRLF
-- IGNORE 'sysname'
IF UPPER(@DataType) IN ('SYSNAME')
GOTO END_Gen
-- IGNORE ID - we generate it ourselves.
IF UPPER(@FieldName) IN ('ID')
GOTO END_Gen
SET @SQLTable = @SQLTable + @TAB + '[' + @FieldName + '] ' + '[' + @DataType + ']'
IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY')
BEGIN
-- //TODO: @FieldLength is double here - why?
SET @SQLTable = @SQLTable + '(' + @FieldLength + ')'
END
ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC')
BEGIN
SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')'
END
SET @SQLTable = @SQLTable + ' NULL'
SET @SQLTable = @SQLTable + ',' + @CRLF
END_Gen:
FETCH NEXT FROM CurHistoryTable INTO @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
END
CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable
-- finish history table script and code for Primary key
SET @SQLTable = @SQLTable + ' )' + @CRLF + @CRLF
SET @SQLTable = @SQLTable + 'ALTER TABLE [dbo].[' + @HistoryTableName + ']' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'ADD CONSTRAINT [PK_' + @HistoryTableName + '_ID] PRIMARY KEY NONCLUSTERED ([ID] ASC)' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];' + @CRLF + @CRLF
-- ADD FOREIGN KEY
SET @SQLTable = @SQLTable + 'ALTER TABLE [dbo].[' + @HistoryTableName + '] WITH CHECK ADD' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'CONSTRAINT [FK_' + @HistoryTableName + '_' + @TableName + ']' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'FOREIGN KEY ([' + @TableName + '_ID])' + @CRLF
SET @SQLTable = @SQLTable + @TAB + 'REFERENCES [dbo].[' + @TableName + '] ([ID])' + @CRLF + @CRLF
PRINT @SQLTable
-- execute sql script to create history table
IF @ExecuteProcedure = 'Y'
EXEC(@SQLTable)
SET @SQLTrigger = ''
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
IF @CreateTrigger = 'Y'
BEGIN
-- create history trigger
SET @SQLTrigger = '/************************************************************************************************************' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF
SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [' + @TableName + '_UpdateTrigger] ON DBO.' + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AFTER INSERT, DELETE, UPDATE' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'DECLARE @dtNow datetime,' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' @DCount int,' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' @ICount int' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + '-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SET NOCOUNT ON;' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @dtNow = GETDATE()' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @DCount = Count(*) FROM deleted' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT @ICount = Count(*) FROM inserted' + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'INSERT [dbo].[' + @HistoryTableName + ']'+ @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'SELECT [Operation] = CASE WHEN @DCount > 0 and @ICount > 0 THEN ''M''' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' WHEN @ICount > 0 THEN ''A''' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' ELSE ''D''' + ' END,' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' [DateOfAction] = @dtNow, I.*' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + 'FROM inserted AS I' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' LEFT OUTER JOIN deleted AS D' + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ' ON I.ID = D.ID' + @CRLF
SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF + @CRLF
PRINT @SQLTrigger
-- execute sql script to create update/delete trigger
IF @ExecuteProcedure = 'Y'
EXEC(@SQLTrigger)
IF @@ERROR <> 0
BEGIN
PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************'
RETURN -1
END
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment