There are a number of ways to track data changes in SQL Server databases, many of which include table triggers to log the changes.  In this post I provide a streamlined approach to setting up those triggers and keeping them up to date.

A number of years ago I inherited an application that had a data auditing solution.  I am not sure of the origins of the solution, but it is similar to other ideas out on the Internet.  The main idea is an audit table to hold the history, and triggers on all the tables to log the changes.  This worked well for us, but as we made changes to the application it was challenging to keep the triggers up to date.  To solve that challenge I wrote a stored procedure called sp_build_triggers to generate the trigger creation scripts based on the Information Schema views in the database.  After making structure changes to a database (adding/removing columns or tables), I run sp_build_triggers, then copy the text it generates to a new query window and run it on the database.  This updates all the triggers previously created by the script.

This solution is geared toward custom applications where you can have the database calls send the date and the user making the change, as well as modifying the database to include columns for these values.  If you do not have that level of control, this solution could be modified to remove the user ID fields, which would then give a history of what changes were made when.  It also works best with single column primary keys.  It will function with multi-value keys, but only the first column will be listed in the audit table.  The solutions I use this with primarily use uniqueidentifiers and integers as keys.

This solution only logs updates and deletes.  Inserted records are tracked with the Created and CreatedByID columns.  To be clear, this solution does not cover auditing events, logins, structure, or permission changes.  SQL Server provides a built-in method for that.  (Google “SQL Server Audit Specification”)

Script Requirements and Setup:

  • Create Audit table (see CREATE TABLE statement below)

  • Create AuditTypes table with values populated (see statements below)

  • Each table to be audited needs the following columns:

o   Created [smalldatetime] – Default: Getdate()

o   CreatedByID [uniqueidentifier]

o   Modified [smalldatetime]

o   ModifiedByID [uniqueidentifier]

  • Create sp_build_triggers by running the script below.  There are two things to attend to at the top of the stored procedure.  The first one is listing all the computed columns (they will not be audited).  This is done by inserting the table and column name for each into the table variable.  The second is defining the tables for which you do not want audit triggers created, including the audit tables.

  • Note: If the table name has been changed between stored procedure executions, the original triggers will either need to be renamed or deleted.  The script does not automatically detect them if they are not named “tr_[Table Name]_[U/D]”.

Once the above setup items are complete you are ready to run “exec sp_build_triggers”.  Copy the text output, paste it in a new query window, and run it to create the triggers.  Now you can change some data and watch it appear in the audit table.

Happy coding!


Setup Tables

CREATE TABLE [dbo].[Audit] (

            [AuditID]                   [int]                             IDENTITY(1, 1) NOT NULL,

            [AuditTypeID]          [int]                             NOT NULL,

            [TableName]            [varchar](50)            NOT NULL,

            [TablePKID]              [varchar](36)            NOT NULL,

            [ColumnName]        [varchar](50)            NOT NULL,

            [OldValue]                [varchar](1000)        NULL,

            [NewValue]               [varchar](1000)        NULL,

            [DateModified]        [datetime]                 NOT NULL,

            [ModifiedByID]        [uniqueidentifier]   NULL,

            [SystemUser]           [varchar](30)            NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED ([AuditID])

GO

 

CREATE TABLE [dbo].[AuditTypes] (

            [AuditTypeID]          [int]                             IDENTITY(1, 1) NOT NULL,

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

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

            [Description]            [varchar](100)          NULL,

            [SortOrder]              [int]                            NOT NULL,

            [isSystemData]        [bit]                            NOT NULL,

            [ModifiedByID]        [uniqueidentifier]    NOT NULL

) ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[AuditTypes] ADD CONSTRAINT [PK_AuditTypes] PRIMARY KEY CLUSTERED ([AuditTypeID])

GO

 

--Inserting data into [dbo].[AuditTypes] (3 records)

SET IDENTITY_INSERT [dbo].[AuditTypes] ON

INSERT INTO [dbo].[AuditTypes]([AuditTypeID], [TypeName], [Type], [Description], [SortOrder], [isSystemData], [ModifiedByID]) VALUES (1, N'Action', N'Insert', N'INS', 1, 0, '00000000-0000-0000-0000-000000000000')

INSERT INTO [dbo].[AuditTypes]([AuditTypeID], [TypeName], [Type], [Description], [SortOrder], [isSystemData], [ModifiedByID]) VALUES (2, N'Action', N'Delete', N'DEL', 1, 0, '00000000-0000-0000-0000-000000000000')

INSERT INTO [dbo].[AuditTypes]([AuditTypeID], [TypeName], [Type], [Description], [SortOrder], [isSystemData], [ModifiedByID]) VALUES (3, N'Action', N'Update', N'UPD', 1, 0, '00000000-0000-0000-0000-000000000000')

SET IDENTITY_INSERT [dbo].[AuditTypes] OFF

 

Stored Procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

-- =============================================

--          Greg Hess Enhanced Code Generation Stored Procedure.

--          Stored procedure to generate code to create/alter triggers for auditing.

-- =============================================

 

*/

CREATE PROCEDURE [dbo].[sp_build_triggers]

            @RequestedTableName varchar(50)     = null

AS

 

SET NOCOUNT ON

 

DECLARE       @ColumnName varchar(50),

                        @DataType varchar(50),

                        @KeyColumnName varchar(50)

 

DECLARE @TableName varchar(50),

            @SQLString nvarchar(255)

 

 

-- Populate this table variable with a list of computed columns so those changes are not logged.

DECLARE @ComputedColumns table

(

            TableName varchar(50),

            ColumnName varchar(50)

)

--INSERT into @ComputedColumns values ('[Table]','[Column]')

 

 

-- Cursor to loop through all tables, with a few exceptions

DECLARE tab_c CURSOR

FOR

            SELECT name

            FROM sysobjects

            WHERE xtype = 'U'

                        AND name not in ('Audit', 'AuditTypes', 'sysdiagrams')                     -- exclude these tables

                        AND name not like 'aspnet_%'    -- exclude these tables

                        AND name not like 'tmp%'           -- exclude these tables

                        AND name = ISNULL(@RequestedTableName,name)           -- if a table was specified create for that table, otherwise create all

            ORDER BY name

 

OPEN tab_c

 

FETCH NEXT FROM tab_c

INTO @TableName

 

WHILE @@FETCH_STATUS = 0

BEGIN

            PRINT '-- Building triggers for ' + @TableName

           

 

-------------------------------------------------------------------------

-- Update Trigger

-------------------------------------------------------------------------

 

 

-- make sure we have some non-primary key columns to log an update for

IF EXISTS(select c.Column_Name           

            from Information_Schema.Columns c

                        left outer join Information_Schema.Table_Constraints tc

                                    on c.Table_Name = tc.Table_Name and tc.Constraint_Type = 'PRIMARY KEY'

                        left outer join Information_Schema.Key_Column_Usage kcu

                                    on tc.Table_Name = kcu.Table_Name and tc.Constraint_Name = kcu.Constraint_Name and c.Column_Name = kcu.Column_Name

            where c.Table_Name = @TableName

                        and kcu.Column_Name is null     -- we don't want to include the primary keys

            )

BEGIN

 

 

-- the opening statements

PRINT '-- Create/Alter the update trigger'

PRINT 'SET ANSI_NULLS ON'

PRINT 'GO'

PRINT 'SET QUOTED_IDENTIFIER ON'

PRINT 'GO'

PRINT '/*'

PRINT '-- ============================================='

PRINT '-- Author:                 Greg Hess Enhanced Code Generation Stored Procedure.'

PRINT '-- Create date: ' + cast(getdate() as varchar(20))

PRINT '-- Description:        Trigger to log updates and store history in audit_audit table.'

PRINT '-- ============================================='

PRINT ''

PRINT '*/'

 

-- determine if we are adding or modifying a trigger

IF OBJECTPROPERTY(OBJECT_ID('tr_' + @TableName + '_U'), 'IsTrigger') = 1

BEGIN

    PRINT 'ALTER trigger [dbo].[tr_' + @TableName + '_U] ON [dbo].[' + @TableName + '] FOR UPDATE'

END

ELSE

BEGIN

            PRINT 'CREATE trigger [dbo].[tr_' + @TableName + '_U] ON [dbo].[' + @TableName + '] FOR UPDATE'

END

 

PRINT 'AS'

PRINT ''

PRINT 'DECLARE @ModifiedByID UNIQUEIDENTIFIER'

PRINT 'DECLARE @CrmActionID INT'

PRINT 'DECLARE @TableName    VARCHAR(50)'

PRINT 'DECLARE @TablePKID      VARCHAR(36)'

PRINT 'DECLARE @ColumnName           VARCHAR(50)'

PRINT ''

PRINT 'BEGIN'

PRINT ''

PRINT 'SELECT  @CrmActionID = AuditTypeID FROM AuditTypes WHERE Typename = ''Action'' AND [Type] = ''Update'''

PRINT ''

PRINT ''

 

DECLARE col_c CURSOR

FOR

select c.Column_Name, c.DATA_TYPE,

            ISNULL((select c.Column_Name              -- Get the first Primary Key column

                                    from Information_Schema.Columns c

                                                left outer join Information_Schema.Table_Constraints tc

                                                            on c.Table_Name = tc.Table_Name and tc.Constraint_Type = 'PRIMARY KEY'

                                                left outer join Information_Schema.Key_Column_Usage kcu

                                                            on tc.Table_Name = kcu.Table_Name and tc.Constraint_Name = kcu.Constraint_Name and c.Column_Name = kcu.Column_Name

                                    where c.Table_Name = @TableName

                                                and kcu.Column_Name is not null          -- we want the primary key

                                                and kcu.ORDINAL_POSITION = 1),

                        (select c.Column_Name                -- If there is no primary key then we want to grab the first column of the table.

                                    from Information_Schema.Columns c

                                    where c.Table_Name = @TableName

                                                and c.ORDINAL_POSITION = 1)

                        ) as KeyColumnName

from Information_Schema.Columns c

            left outer join Information_Schema.Table_Constraints tc

                        on c.Table_Name = tc.Table_Name and tc.Constraint_Type = 'PRIMARY KEY'

            left outer join Information_Schema.Key_Column_Usage kcu

                        on tc.Table_Name = kcu.Table_Name and tc.Constraint_Name = kcu.Constraint_Name and c.Column_Name = kcu.Column_Name

            left outer join @ComputedColumns cc

                        on c.Table_Name = cc.TableName and c.Column_Name = cc.ColumnName

where c.Table_Name = @TableName

            and kcu.Column_Name is null     -- we don't want to include the primary keys

            and c.DATA_TYPE NOT IN ('image')        -- we can't log changes on image columns

            and cc.ColumnName IS null                                 -- exclude the computed columns

 

OPEN col_c

 

FETCH NEXT FROM col_c

INTO @ColumnName, @DataType, @KeyColumnName

 

WHILE @@FETCH_STATUS = 0

BEGIN

            PRINT '           -- Changes on ' + @ColumnName + ' column'

 

            PRINT '           IF UPDATE([' + @ColumnName + '])'

            PRINT '           BEGIN'

            PRINT '           INSERT INTO Audit (ModifiedByID, AuditTypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified, SystemUser)'

            PRINT '           SELECT i.ModifiedByID,'

            PRINT '                                   @CrmActionID,'

            PRINT '                                   ''' + @TableName + ''','

    PRINT '                               CONVERT(VARCHAR(36),i.' + @KeyColumnName + '), --TablePKID'

    PRINT '                               ''' + @ColumnName + ''','

            PRINT '                                   CONVERT(VARCHAR(1000),d.' + @ColumnName + '), -- Old Value'

            PRINT '                                   CONVERT(VARCHAR(1000),i.' + @ColumnName + '), -- New Value'

            PRINT '                                   GETDATE(),'

            PRINT '                                   SYSTEM_USER'

            PRINT '                       FROM INSERTED i LEFT OUTER JOIN DELETED d on CONVERT(VARCHAR(36),i.' + @KeyColumnName + ') = CONVERT(VARCHAR(36),d.' + @KeyColumnName + ') '

            IF @DataType <> 'xml'       -- we can't do this comparison on xml fields, so we will log all changes.

            BEGIN

                        PRINT '                       WHERE ISNULL(CONVERT(VARCHAR(1000),d.[' + @ColumnName + ']),'''') <> ISNULL(CONVERT(VARCHAR(1000),i.[' + @ColumnName + ']),'''')'

            END

    PRINT '       END'

    PRINT ''

 

           

            FETCH NEXT FROM col_c

            INTO @ColumnName, @DataType, @KeyColumnName

END

CLOSE col_c

DEALLOCATE col_c

 

 

PRINT 'END'

PRINT ''

PRINT 'GO'

 

END

ELSE

BEGIN

            PRINT '-- all columns are key columns, nothing for an update trigger to do'

END

 

 

-------------------------------------------------------------------------

-- Delete Trigger

-------------------------------------------------------------------------

 

 

-- the opening statements

PRINT '-- Create/Alter the delete trigger'

PRINT 'SET ANSI_NULLS ON'

PRINT 'GO'

PRINT 'SET QUOTED_IDENTIFIER ON'

PRINT 'GO'

PRINT '/*'

PRINT '-- ============================================='

PRINT '-- Author:                 Greg Hess Enhanced Code Generation Stored Procedure.'

PRINT '-- Create date: ' + cast(getdate() as varchar(20))

PRINT '-- Description:        Trigger to log deletes and store history in audit_audit table.'

PRINT '-- ============================================='

PRINT ''

PRINT '*/'

 

-- determine if we are adding or modifying a trigger

IF OBJECTPROPERTY(OBJECT_ID('tr_' + @TableName + '_D'), 'IsTrigger') = 1

BEGIN

    PRINT 'ALTER trigger [dbo].[tr_' + @TableName + '_D] ON [dbo].[' + @TableName + '] FOR DELETE'

END

ELSE

BEGIN

            PRINT 'CREATE trigger [dbo].[tr_' + @TableName + '_D] ON [dbo].[' + @TableName + '] FOR DELETE'

END

 

PRINT 'AS'

PRINT ''

PRINT 'DECLARE @ModifiedByID UNIQUEIDENTIFIER'

PRINT 'DECLARE @CrmActionID INT'

PRINT 'DECLARE @TableName    VARCHAR(50)'

PRINT 'DECLARE @TablePKID      VARCHAR(36)'

PRINT 'DECLARE @ColumnName           VARCHAR(50)'

PRINT ''

PRINT 'BEGIN'

PRINT ''

PRINT 'IF ((SELECT COUNT(*) FROM DELETED) != 0)'

PRINT 'BEGIN'

PRINT '           SELECT  @CrmActionID = AuditTypeID FROM AuditTypes WHERE Typename = ''Action'' AND [Type] = ''Delete'''

PRINT ''

 

 

DECLARE col_c CURSOR

FOR

select c.Column_Name,

            ISNULL((select c.Column_Name              -- Get the first Primary Key column

                                    from Information_Schema.Columns c

                                                left outer join Information_Schema.Table_Constraints tc

                                                            on c.Table_Name = tc.Table_Name and tc.Constraint_Type = 'PRIMARY KEY'

                                                left outer join Information_Schema.Key_Column_Usage kcu

                                                            on tc.Table_Name = kcu.Table_Name and tc.Constraint_Name = kcu.Constraint_Name and c.Column_Name = kcu.Column_Name

                                    where c.Table_Name = @TableName

                                                and kcu.Column_Name is not null          -- we want the primary key

                                                and kcu.ORDINAL_POSITION = 1),

                        (select c.Column_Name                -- If there is no primary key then we want to grab the first column of the table.

                                    from Information_Schema.Columns c

                                    where c.Table_Name = @TableName

                                                and c.ORDINAL_POSITION = 1)

                        ) as KeyColumnName

from Information_Schema.Columns c

            left outer join Information_Schema.Table_Constraints tc

                        on c.Table_Name = tc.Table_Name and tc.Constraint_Type = 'PRIMARY KEY'

            left outer join Information_Schema.Key_Column_Usage kcu

                        on tc.Table_Name = kcu.Table_Name and tc.Constraint_Name = kcu.Constraint_Name and c.Column_Name = kcu.Column_Name

where c.Table_Name = @TableName

            and c.DATA_TYPE NOT IN ('image')        -- we can't log changes on image columns

 

 

OPEN col_c

 

FETCH NEXT FROM col_c

INTO @ColumnName, @KeyColumnName

 

WHILE @@FETCH_STATUS = 0

BEGIN

            PRINT '           -- Changes on ' + @ColumnName + ' column'

 

            PRINT ' INSERT INTO Audit (ModifiedByID, AuditTypeID, TableName, TablePKID, ColumnName, OldValue, NewValue, DateModified, SystemUser)'

            PRINT ' SELECT d.ModifiedByID,'

            PRINT '                       @CrmActionID,'

            PRINT '                       ''' + @TableName + ''','

    PRINT '                   CONVERT(VARCHAR(36),d.' + @KeyColumnName + '),           --TablePKID'

    PRINT '                   ''' + @ColumnName + ''','

            PRINT '                       CONVERT(VARCHAR(1000),d.' + @ColumnName + '), -- Old Value'

            PRINT '                       NULL, -- New Value'

            PRINT '                       GETDATE(),'

            PRINT '                       SYSTEM_USER'

            PRINT '           FROM DELETED d '

            PRINT ''

 

            FETCH NEXT FROM col_c

            INTO @ColumnName, @KeyColumnName

END

CLOSE col_c

DEALLOCATE col_c

 

 

PRINT 'END'

PRINT 'END'

PRINT ''

PRINT ''

PRINT 'GO'

 

            FETCH NEXT FROM tab_c

            INTO @TableName

END

CLOSE tab_c

DEALLOCATE tab_c​
​​

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.