Track Sql Server Table Record Changes (History).

Assume a SQL Server table called tblTickets with columns AddressIdentifier, WorkType, Status, PreviousStatus, Transitional, TransitionalPercent and ParcelNumber.

Update() function determines if the column value has been changed. If it has, then insert a new record to the history table.

ALTER TRIGGER [dbo].[trgTicketsInsUpd] ON [dbo].[tblTickets] 

FOR INSERT, UPDATE

AS

Set NoCount On
Update tblTickets
Set EditDate = GetDate()
From tblTickets T Inner Join Inserted I On T.ID = I.ID
-- For each field to be tracked by History, if changed or new
-- make a history record

If Update(AddressIdentifier)
    --  AddressIdentifier (varchar[9])
    Insert into tblHistoryArchive (TableName, FieldName, TableID1, NewValue, EditUserID, EditHostName, EditDate)
        Select 'tblTickets', 'AddressIdentifier', I.ID, left(I.AddressIdentifier,9), I.UserID, HOST_NAME(), getdate()
        from Inserted I Left Outer Join Deleted D on I.ID = D.ID
        where I.AddressIdentifier <> IsNull(D.AddressIdentifier,'')

If Update(WorkType)     
    -- WorkType (varchar[50])
    Insert into tblHistoryArchive (TableName, FieldName, TableID1, NewValue, EditUserID, EditHostName, EditDate)
        Select 'tblTickets', 'WorkType', I.ID, left(I.WorkType,50), I.UserID, HOST_NAME(), getdate()
        from Inserted I Left Outer Join Deleted D on I.ID = D.ID
        where I.WorkType <> IsNull(D.WorkType,'')

If Update(Status)
    -- Status (varchar[50])
    Insert into tblHistory (TableName, FieldName, TableID1, NewValue, EditUserID, EditHostName, EditDate)
        Select 'tblTickets', 'Status', I.ID, left(I.Status,50), I.UserID, HOST_NAME(), getdate()
        from Inserted I Left Outer Join Deleted D on I.ID = D.ID
        where I.Status <> IsNull(D.Status,'')

If Update(PreviousStatus)
    -- PreviousStatus (varchar[50])
    Insert into tblHistory (TableName, FieldName, TableID1, NewValue, EditUserID, EditHostName, EditDate)
        Select 'tblTickets', 'PreviousStatus', I.ID, left(I.PreviousStatus,50), I.UserID, HOST_NAME(), getdate()
        from Inserted I Left Outer Join Deleted D on I.ID = D.ID
        where I.PreviousStatus <> IsNull(D.PreviousStatus,'')

If Update(Transitional)
    -- Transitional bit
    Insert into tblHistory (TableName, FieldName, TableID1, NewValue, EditUserID, EditHostName, EditDate)
        Select 'tblTickets', 'Transitional', I.ID,  Case I.Transitional When 0 then 'N' else 'Y' End , I.UserID, HOST_NAME(), getdate()
        from Inserted I Left Outer Join Deleted D on I.ID = D.ID
        where I.Transitional <> IsNull(D.Transitional,0)

If Update(TransitionalPercent)      
    -- TransitionalPercent (smallint)
    Insert into tblHistory (TableName, FieldName, TableID1, NewValue, EditUserID, EditHostName, EditDate)
        Select 'tblTickets', 'TransitionalPercent', I.ID, cast(I.TransitionalPercent as varchar(50)), I.UserID, HOST_NAME(), getdate()
        from Inserted I Left Outer Join Deleted D on I.ID = D.ID
        where I.TransitionalPercent <> IsNull(D.TransitionalPercent,0)

If Update(ParcelNumber)
    -- ParcelNumber (int)
    Insert into tblHistory (TableName, FieldName, TableID1, NewValue, EditUserID, EditHostName, EditDate)
        Select 'tblTickets', 'ParcelNumber', I.ID, cast(I.ParcelNumber as varchar(50)), I.UserID, HOST_NAME(), getdate()
        from Inserted I Left Outer Join Deleted D on I.ID = D.ID
        where I.ParcelNumber <> IsNull(D.ParcelNumber,0)


Print 'Trigger  tblTickets.trgTicketsInsUpd (History Items Inserted)'