Introduction

In Today's IT world, data is everything, so providing data access to many users introduces a potential threat of unknown risks. We must take proper measures to capture data changes and updates to avoid such risks. Therefore, the data managers must apply tools to track each data activity done by the users who have read-write access to the data. This data tracking tool needs to be implemented at the field level for each available data element. In this article we will discuss such tools and cover the following topics:

1. Setting up the database tables and triggers
2. Generic and Custom triggers
3. Implementing the triggers
4. Testing and reading the data captured by triggers
5. Few do's and don'ts to implementing Auditing properly.

Data Tracking Tools

There are several ways by which we can capture the data activity. For example:

1. Capturing the data changes from the application code. This will not capture any changes made on the database directly.
2. Capturing the data changes using SQL Triggers:
   1. Using a trigger capturing the entire row of data getting changed. This will capture redundant data which is not needed.
   2. Using a trigger capturing a single field/data element getting changed or updated

This article will discuss method 2.(2) of all the options mentioned above. The advantages of this method will be capturing any data changes made from either the application side or the database side. Moreover, this will save the changes for the specific data fields instead of the complete data set. Another advantage of using this trigger is during the testing of the application. We can use the trigger to ensure the system is not making any data submission errors or any other issues that go undetected by the testers during manual testing.

Data Tracking Trigger Types

To create data tracking triggers, you can use one of the below methods:

1. A simple generic trigger with minimum maintenance – A simple trigger will capture all the data changes made by the user with exact database values. For example, if a user changes a City value from the Application Austin to Seattle. In the database the CityID gets changed from 10 to 20 (10 being the ID given to Austin and 20 being the ID given to Seattle). This simple trigger will capture the CityID’s instead of the City Names. Once we have implemented a simple trigger for a table, we do not have to make updates on the trigger even if there are some changes to the Table afterwards, hence no maintenance.
2. Custom trigger that collects information specific to each field - A custom trigger provide better details to the business users. For example, if a user changes a City value from the Application, Austin to Seattle. In the database the CityID gets changed from 10 to 20 (10 being the ID given to Austin and 20 being the ID given to Seattle). This custom trigger will capture the City Name and not the ID’s. Once we have implemented a custom trigger for a table, we have to make updates on the trigger every time there are some changes to the Table afterwards, hence we need to maintain this trigger with changes to the table.

I recommend using simple generic triggers when the data tracking needs to be kept internal and does not need to be provided daily to the business users. On the other hand, use the custom triggers when the data tracking must be shared daily with business users. We will discuss both types of triggers in detail and examples in the following sections. Below diagram gives a high-level overview of data capturing.

Initial Setup

To get started, we need to set up the audit table for capturing all the data activities. This section will cover the scripts to set up a simple generic trigger and a custom trigger. Create both tables by using the provided sample scripts. This table will directly store all the data changes from the application or database. There is need of only one generic audit table and one custom audit table in the application database.

We assume that the reader of this article has basic knowledge and understanding of SQL, including triggers, and have access to SSMS to practice this concept.

Creating Generic Audit Table

CREATE TABLE [Audit].[GenericDataTracking](
[GenericDataTrackingID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
[Type] [char](1) NOT NULL,
[TableSchema] [nchar](5) NULL,
[TableName] [nvarchar](128) NOT NULL,
[PK] [varchar](128) NOT NULL,
[FieldName] [nvarchar](128) NOT NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[UpdateDate] [datetime2](7) NOT NULL,
[UserName] [nvarchar](128) NOT NULL,
[Application] [nvarchar](150) NOT NULL
)

Column details:

1. GenericDataTrackingID - This is the primary key for the table.
2. Type - This column will capture the changes if any are due to the Update or delete action.
3. TableSchema - This column will contain the schema name for the updated table
4. TableName - This column will contain the table name for the updated Table data
5. PK - This column will contain the primary key of the Updated Row in the table. Note: All the tables we will be applying the triggers must always have a defined primary key.
6. FieldName - This column will contain the field name(Column Name) for updated field.
7. OldValue - This column will have the old value for the field ( the value before the change occurred).
8. NewValue - This column will contain the New value for the field ( the new value which has been updated now).
9. UpdatedDate - This column will contain the date and time when the change will occur.
10. Username - This column will contain the username of the user who will make this change.
11. Application - This column will contain the application name of the source of change. For example, SSMS or the Web Application

Creating Custom Audit Table

CREATE TABLE  [Audit].[DataTrackingCustom](
[DataTrackingCustomID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
[ClientID] [int] NOT NULL,
[Type] [char](1) NULL,
[TableSchema] [nchar](15) NULL,
[TableName] [nvarchar](128) NULL,
[PK] [varchar](128) NULL,
[FieldName] [nvarchar](128) NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[UpdateDate] [datetime2](7) NULL,
[UserName] [nvarchar](128) NULL,
[Application] [nvarchar](150) NULL,
[tabledisplayname] [nvarchar](128) NULL,
[fielddisplayname] [nvarchar](128) NULL,
[isviewable] [bit] NOT NULL
        )

The column details are defined here. These are used differently as compared to generic audit table.

1. DataTrackingCustomID - This is the primary key for the table.
2. ClientID - This column will contain the Client ID so you can easily display all the data changes for the client in one place.
3. TableDisplayName - This column will contain a user-friendly name for the updated table/section.
4. FieldDisplayName - This column will contain a user-friendly name of the updated Field/Column.
5. IsViewable - As we capture all the data changes, some columns may not be advisable to display on the Web application. We control that data from the trigger and use this flag for the same purpose.

These two audit tables will store all the updated/changed data captured using the triggers. We will get into the details of triggers in next the sections.

Creating Database Tables

Now, let us understand the triggers with the help of an example. First, let's create two tables which we will use to change/update the data set captured by the triggers. I have created the Below mentioned tables. We will be using the [lu].[Gender] table for the generic trigger and [Client].[ClientProfile] table for the custom trigger.

CREATE TABLE [lu].[Gender](
[GenderId] [int] IDENTITY(1,1) NOT NULL Primary Key, --Primary key
[Gendername] [nvarchar](250) NOT NULL, -- Gender names like - Male, Female
[IsActive] [bit] NOT NULL, -- Capture if the value is active or inactive
[createdby] [nvarchar](36) NULL, -- user who created this record
[datecreated] [datetime2](7) NOT NULL, -- date and time when the record was created
[updatedby] [nvarchar](36) NULL, -- user who last modified this record
[dateupdated] [datetime2](7) NULL -- date and time when this record was last modified
)

CREATE TABLE [Client].[ClientProfile](
[ClientProfileId] [int] IDENTITY(1,1) NOT NULL Primary Key,
[FirstName] [nvarchar](250) NULL,
[LastName] [nvarchar](250) NULL,
[GenderId] [int] NOT NULL,  --FK from Lu.Gender Table
[dob] [datetime2](7) NULL,
[CreatedBy] [nvarchar](50) NOT NULL,
[DateCreated] [datetime2](7) NOT NULL,
[ModifiedBy] [nvarchar](50) NULL,
[DateModified] [datetime2](7) NULL
)
-- adding gender as foreign key reference to clientprofile table
ALTER TABLE [client].[ClientProfile]  WITH CHECK ADD  CONSTRAINT [FK_ClientProfile_Gender_GenderID] FOREIGN KEY([GenderId])
REFERENCES [lu].[Gender]([GenderId])
GO

Please note: for executing the trigger, the tables should always have this column modifiedby and for this system to work there must be a primary key in each table.

Simple Generic Trigger

Below is the script for the simple generic trigger. First, create this trigger on the [lu].[Gender] table we have discussed in the previous section. Once this trigger is added to the [lu].[Gender] table, we will run some test scenarios to see the results.

Note: To run this trigger, the following things need to be done:

1. Populate the Updatedby column correctly with the Logged in User's Username whenever changes are made.
2. When deleting the entire row from the table, the code needs to populate the updatedby column first then make the hard delete.

Create TRIGGER [lu].[tr_gender] ON [lu].[Gender] FOR INSERT, UPDATE, DELETE

AS
DECLARE  @bit INT , @field INT , @fieldId INT , @maxfield INT ,
   @char INT , @fieldname VARCHAR(128) , @TableName VARCHAR(128) ,
   @SchemaName VARCHAR(128) , @PKCols VARCHAR(1000) ,  @sql VARCHAR(2000), 
   @UpdateDate VARCHAR(21) , @UserName VARCHAR(128) ,  @Type CHAR(1) ,
   @FullTableName VARCHAR(256), @PKSelect VARCHAR(1000),  @next bit,
   @oldvalue nVARCHAR(1000),  @newvalue nVARCHAR(1000)
       
SELECT @TableName = 'Gender' , @schemaName = 'lu'

----------------------------------------------------------------------------------------------
SELECT @FullTableName ='[' + @SchemaName + '].[' + @TableName + ']'

-- selecting the action Insert, delete or update
SET @Type = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
THEN 'U'  -- Set Action to Updated.
                      WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN 'I'  -- Set Action to Insert.
                      WHEN EXISTS(SELECT * FROM DELETED)
                        THEN 'D'  -- Set Action to Deleted.
                 ELSE NULL -- Skip. It may have been a "failed delete".   
                    END)
-- date for the action
SELECT @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) 
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
-- username if changes are done from SSMS
SELECT @UserName =Right(SYSTEM_USER,LEN(SYSTEM_USER)-9) --You may need to adjus this depending on the username structure for your SSMS
-- capture username if the action is done from the application
-- do not use this for provider table, state table
if APP_NAME() = 'Core Microsoft SqlClient Data Provider'  --Application Frameworkname, change as per your application framework name
BEGIN
if @type in ('U')
Begin
SELECT top(1) @UserName = i.modifiedby from inserted i
End
if @type in ('D')
Begin
SELECT top(1) @UserName = d.modifiedby from deleted d
End
IF @type IN ('I') -- We are not capturing inserts, so you may remove this step
BEGIN
SELECT TOP(1) @UserName = i.createdby FROM inserted i
END
END
 
-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') 
   + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
   FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
   WHERE   pk.TABLE_NAME = @TableName
   AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
   AND     c.TABLE_NAME = pk.TABLE_NAME
   AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
   ANDc.CONSTRAINT_SCHEMA = pk.TABLE_SCHEMA
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''' +
'''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''''' 
   FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
   WHERE   pk.TABLE_NAME = @TableName
   AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
   AND     c.TABLE_NAME = pk.TABLE_NAME
   AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- error handling
IF @PKCols IS NULL
BEGIN
   RAISERROR('no PK on table %s', 16, -1, @TableName)
   RETURN
END
-- initiating variables
SELECT @field = 0, 
   @maxfield = MAX(ORDINAL_POSITION) 
   FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName
--- main while loop-------------
WHILE @field < @maxfield +1
BEGIN
-- fetching the field names for each column
SELECT @fieldname = COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName   AND ORDINAL_POSITION = @field AND TABLE_SCHEMA = @SchemaName
-- fetching the column ID, these can be different from the ordinal position if the columns are dropped 
SELECT @fieldid = COLUMNPROPERTY(OBJECT_ID(@FullTableName), @fieldname, 'ColumnID')
-- fetching the ordinal position   
SELECT @field = MIN(ORDINAL_POSITION) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @SchemaName 
AND ORDINAL_POSITION > @field
-- capturing some values to do the condition for the updated action
SELECT @bit = (@fieldId - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@fieldId - 1) / 8) + 1
 Select @next = 1
-- ignoring these fields as audit captures data about who and when the data was changed, also the inserts ignored from the audit table as they are in the actual tables itself. And we are skipping the inserts also. Our Data tracking trigger is focussed on Updates and deleted
if @fieldname IN ('datemodified','datecreated','createdby','modifiedby') or @type = 'I'
Begin
Select @next = 0
end
-------------------------------------------------------------------------------------------------------------
IF @next = 1
BEGIN
SELECT @oldvalue ='d.'+@fieldname 
SELECT @newvalue = 'i.'+@fieldname 

--------------------------- Skip apostrophe in the updated values

IF CHARINDEX('''', @newvalue)>0 
SET @newvalue=REPLACE(@newvalue,'''','''''')
IF CHARINDEX('''', @oldvalue)>0 
SET @oldvalue=REPLACE(@oldvalue,'''','''''')
----------------------------------------------------------------------------

-- the frist condition checks for updates and next one for deletes
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0 OR @Type IN ('D')
BEGIN
  SELECT @sql = '
   insert [Audit].[GenericDataTracking] 
   ( Type, TableSchema, TableName, PK, FieldName, 
   OldValue, NewValue, UpdateDate, UserName,  Application)
   
   select ''' + @Type + ''',''' + @SchemaName +''',
   '''+ @TableName + ''',' + @PKSelect + ',
   ''' + @fieldname + '''' + ', '+@oldvalue + ',
   '+@newvalue+ ',''' + @UpdateDate + '''' + ',
   ''' + @UserName + ''''+ ',
   ''' + REPLACE(APP_NAME(), '''', '''''') + ''''
   + ' from #ins i full outer join #del d'
   + @PKCols + ' where i.' + @fieldname + ' <> d.' + @fieldname 
   + ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
   + ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
   
  EXEC (@sql)  -- excuting the sql into the GenericDataTracking table
        END
  END
END  -- while loop end

Testing the Generic Trigger

Now we have everything setup for the generic trigger. Let's test with a simple update command on the [lu].[Gender]. Follow the below steps for testing:

1. As we are testing the generic trigger in [lu].[Gender] table, below image shows the data available in this table.
2. The audit tracking table will appear to be empty currently. Below is the image for the generic audit table.
3. Now we can run some tests to update some data fields in the [lu].[Gender] table. I am using the below scripts for running the test. I am updating the gender value "Male" to "Male-ABC".

   

4. After running the above mentioned script, we will see the data being captured in the audit table. Below is the image from the audit table showing the captured updates.
5. In the audit table, we get the table name, schema name, type of update, primary key for the row that was updated, old value, new value, date/time of the update and the user who updated the field and the source of update.

Custom Trigger

Below is the script for the custom trigger. First, create this trigger on the [Client].[ClientProfile] table we have discussed in the previous section. Once this trigger is added to the [Client].[ClientProfile] table we will run some test scenarios to see the results.

Note: To run this trigger, the following things need to be done:

1. Populate the Modifiedby column correctly with the Logged in User's Username whenever changes are made.
2. When deleting the entire row from the table, the code needs to populate the Modifiedby column first then make the hard delete.

The purpose of custom trigger is specifically to provide understandable data tracking updates to the business users. This trigger provides proper naming for the field names and proper data names instead of any ID values. Run the below script to add the custom trigger to the [Client].[ClientProfile] table. 

USE [TAP]
GO
/****** Object:  Trigger [client].[tr_ClientProfile]    Script Date: 11/02/22 11:19:11 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [client].[tr_ClientProfile] ON [client].[ClientProfile] FOR INSERT, UPDATE, DELETE

AS
SET NOCOUNT ON
DECLARE @bit INT ,@field INT ,@fieldId INT ,@maxfield INT ,@char INT ,@fieldname nVARCHAR(128) ,@fieldname1 nVARCHAR(128) ,
@FieldDisplayName nVARCHAR(128),@TableDisplayName nVARCHAR(128),@SchemaName VARCHAR(128) ,@TableName nVARCHAR(128) ,@PKCols VARCHAR(1000) ,
@sql VARCHAR(max), @sql1 VARCHAR(max),@UpdateDate VARCHAR(21) ,@UserName VARCHAR(128) ,@Type CHAR(1) ,@PKSelect VARCHAR(1000) ,
@ClientID VARCHAR(1000) ,@FullTableName VARCHAR(256),@oldvalue nVARCHAR(1000),@newvalue nVARCHAR(1000),@oldvalue1 nVARCHAR(1000), @parentID char(10),
@newvalue1 nVARCHAR(1000),@isviewable char(1),@next bit,@lastfield  VARCHAR(1000),@lastusername VARCHAR(1000),@inserts bit, @name char(3), @followup char(1)

SELECT @TableName = 'ClientProfile' 
-- selecting schema
SELECT @schemaName = 'client'
------------------------------------------------------------
--putting the whole trigger in this condition to run only once and not in a nested trigger
IF TRIGGER_NESTLEVEL() <= 1  
Begin
SELECT @FullTableName ='['+ @SchemaName + '].[' + @TableName + ']'
SET @Type = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN 'U'  -- Set Action to Updated.
                        WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN 'I'  -- Set Action to Insert.
                        WHEN EXISTS(SELECT * FROM DELETED)
                        THEN 'D'  -- Set Action to Deleted.
                       ELSE NULL -- Skip. It may have been a "failed delete".   
                    END)
-- date for the action
SELECT @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) 
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
-- username if changes are done from SSMS
SELECT @UserName =Right(SYSTEM_USER,LEN(SYSTEM_USER)-9) 
-- capture username if the action is done from the application
if APP_NAME() = 'Core Microsoft SqlClient Data Provider'  
BEGIN
if @type in ('U')
Begin
SELECT top(1) @UserName = i.[ModifiedBy] from inserted i
End
if @type in ('D')
Begin
SELECT top(1) @UserName = d.ModifiedBy from deleted d
End
END

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') 
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
     INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        WHERE pk.TABLE_NAME = @TableName
AND  pk.TABLE_SCHEMA = @SchemaName
AND   CONSTRAINT_TYPE = 'PRIMARY KEY'
AND   c.TABLE_NAME = pk.TABLE_NAME
AND   c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
AND  c.CONSTRAINT_SCHEMA = pk.TABLE_SCHEMA

-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','') + '''' +
'''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''''' 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND  pk.TABLE_SCHEMA = @SchemaName
        AND   CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND   c.TABLE_NAME = pk.TABLE_NAME
        AND   c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
        AND  c.CONSTRAINT_SCHEMA = pk.TABLE_SCHEMA

--------Specific to Custom Trigger--
-- Get ClientID for insert for each update or deleted
SELECT @ClientID = COALESCE(@ClientID+'+','') + '''' + 
'''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''''' 
FROMINFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHEREc.TABLE_NAME = @TableName
ANDc.TABLE_SCHEMA = @SchemaName
ANDc.COLUMN_NAME = 'ClientProfileId'
--------Specific to Custom Trigger--
-- error handling
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @FullTableName)
RETURN
END
--------Specific to Custom Trigger--
--Display values for table 
SET @TableDisplayName =  'Client Profile'
--------Specific to Custom Trigger--
-- initiating variables
SELECT @field = 0, 
   @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName

--- main while loop-------------
WHILE @field < @maxfield + 1
BEGIN    
-- fetching the field names for each column
SELECT @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SchemaName AND ORDINAL_POSITION = @field

-- fetching the column ID, these can be different from the ordinal position if the columns are dropped
SELECT @fieldid = COLUMNPROPERTY(OBJECT_ID(@FullTableName), @fieldname, 'ColumnID')

-- inserting initial NULLS in old and new value variables 
select @oldvalue = NULL
Select @newvalue = NULL
-- fetching the ordinal position
SELECT @field = MIN(ORDINAL_POSITION) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName 
AND TABLE_SCHEMA = @SchemaName
AND ORDINAL_POSITION > @field
-- capturing some values to do the condition for the updated action
SELECT @bit = (@fieldId - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@fieldId - 1) / 8) + 1
--------Specific to Custom Trigger--
--display values for fields
SET @FieldDisplayName = CASE @fieldname

WHEN 'createdby' THEN 'Record Created By'WHEN 'datecreated' THEN 'Record Creation Date' WHEN 'Modifiedby' THEN 'Record Last Modified By' 
WHEN 'datemodified' THEN 'Record Last Modified Date' WHEN 'ClientProfileID' THEN 'Client Profile ID'
WHEN 'FirstName' THEN 'First Name'WHEN 'LastName' THEN 'Last Name' WHEN 'GenderID' THEN 'Client Gender'
When 'dob' THEN 'Date of Birth'END
-- Managing Is viewable field for all types--------------------------
select @isviewable = '1'
select @inserts = 0
if @type in('U')
begin
select @isviewable = '1'
end
--------Specific to Custom Trigger--
-- ignoring inserts and the created by and modified by fields
if @type in('I') or @fieldname  in ('createdby','datecreated','modifiedby','datemodified')
begin
select @inserts = 1
end


 -----------setting next at default 0
Select @next = 0
--------Specific to Custom Trigger--
---field specific values 
if @fieldname = 'GenderID'
BEGIN

select @oldvalue =c.Gendername from lu.Gender c inner join deleted d on d.GenderID = c.GenderID

select @newvalue = c.Gendername from lu.Gender c inner join inserted i on i.GenderID = c.GenderID

Select @next = 1
end

if @fieldname = 'dob'
BEGIN
select @oldvalue = d.dob from deleted d
select @oldvalue = isnull(@oldvalue,'NULL')
if @oldvalue <> 'NULL' set @oldvalue = convert(varchar,convert(datetime,left(@oldvalue,10)),106) 

select @newvalue = i.dob from inserted i
select @newvalue = isnull(@newvalue,'NULL')
if @newvalue <> 'NULL' set @newvalue = convert(varchar,convert(datetime,left(@newvalue,10)),106)

Select @next = 1
end
--------Specific to Custom Trigger--

--------------------------- Skip apostrophe in the updated values

if CHARINDEX('''', @newvalue)>0 
set @newvalue=Replace(@newvalue,'''','''''')
if CHARINDEX('''', @oldvalue)>0 
set @oldvalue=Replace(@oldvalue,'''','''''')
----------------------------------------------------------------------------
--------------------------- field specific value in insert
----------------------------------------------------------------------------
If @next = 1
Begin
Select @oldvalue = ''''+ @oldvalue+''''
Select @newvalue = ''''+ @newvalue+''''
end
----------------------------------------------------------------------------
--------------------non field specific
----------------------------------------------------------------------------
if @next = 0
begin
select @oldvalue ='d.'+@fieldname
select @newvalue = 'i.'+@fieldname
End
--------------------adjusting NULLS for field specific values--------------------------------------------------------

if @type = 'D' or @newvalue in (NULL, 'NULL','') 
begin
select @newvalue = '''--'''
end
If @oldvalue in (NULL, 'NULL', '')
Begin
select @oldvalue = '''--'''
end
If (@newvalue = '--' and @oldvalue = '--') or(@oldvalue = ' ' and @newvalue = 'NULL') OR (@oldvalue = '' AND @newvalue IS NULL)
Begin
Set @isviewable = 0
END

--if   @inserts = 0 
--BEGIN
if (substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D') ) AND  @inserts = 0 
BEGIN

SELECT @sql = '
insert [Audit].[DataTrackingCustom] ( Type,TableSchema,TableName,tabledisplayname,fielddisplayname, 
PK, ClientID,IsViewable,FieldName, OldValue, NewValue, 
UpdateDate, UserName,Application)
select ''' + @Type + ''','''+ @SchemaName  + ''',''' + @TableName + ''','''  + @TableDisplayName  + '''
,'''   + @FieldDisplayName  + ''','    + @PKSelect + ',' + @ClientID+ ', ' + @isviewable+' 
,''' + @fieldname  + ''''+ ', '+@oldvalue+','+@newvalue+',' + '''' + @UpdateDate  + ''''+ '
,'''  + @UserName + ''''+ ',''' + REPLACE(APP_NAME(), '''', '''''') + '''' + 
' from #ins i full outer join #del d'+ @PKCols+ ' where i.' + @fieldname + ' <> d.' + @fieldname + ' or (i.' + @fieldname + ' is null and d.'+ @fieldname + ' is not null)' + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)' 

EXEC (@sql)

END


END -----------------While End

END-------------nested trigger end

Testing the Custom Trigger

Now we have everything setup for the custom trigger. Let's test with a simple update command on the [client].[ClientProfile] table. Follow the below steps for testing:

1. As we are testing the custom trigger in [client].[ClientProfile] table, below image shows the data available in this table.

   

2. The audit tracking table will appear to be empty currently. Below is the image for the custom audit table.

   

3. Now we can run some tests to update some data fields in the [client].[ClientProfile] table.
   1. Test 1 - updating "dob" field value for a client. I am using the below script for running the test 1. I am updating the dob value "01-01-1980" to "01-01-1988".

      

   2. Test 2 - updating "gender field value for a client. I am using the below script for running test 2. I am updating the genderID value from 1 (Male) to 2(Female).

      

4. After running the above mentioned script, I will see the data being capture in the custom audit table. Below is the image from the audit table showing the captured updates.
5. In the above image we can see the data updates captured for the two test cases performed.

Conclusion

Triggers are a convenient and easy way to keep data tracking for your databases independent of how they are accessed. There is minor maintenance work involved with Custom triggers, whereas generic triggers are primarily maintenance-free. In this article we covered creation of Audit table, creating some test table and some Trigger execution script providing us with the results we needed. These triggers can be replicated on all the database tables where data auditing is required. This will help you to setup your data tracking system in place.