0

I would like to store history of changes in table and also remember author of change. I created temporal table. This table contains ChangeAuthorId column which value I update every time when table is updated. I would like to display every changed value separately in one log row with date, old value, new value, author of change and column name.

Below is example database and code which works, but I would like to do it simpler. I think it's not perfect way to do it, because for example I may forget to add author of change. Have you got any ideas to do it better? Maybe there's some convetion or special database functions for that?

I know asking a database for every author of change may be bad idea, but it's just the quick example.

This is my example table definition:

CREATE TABLE Person   
( 
    PersonId int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1), 
    Name varchar(50) NOT NULL,
    Email varchar(50) NOT NULL,
    Country varchar(50) NOT NULL,
    ChangeAuthorId int NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)   
)   
WITH    
(   
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory)   
);

Person table records:

enter image description here

And PersonHistory table records:

enter image description here

This is example code to display changes made on Bart Simpson by anyone:

namespace TemporalTablesTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MyDbContext context = new MyDbContext())
            {
                var bart = context.People.Single(p => p.Name == "Bart Simpson");
                var bartHistory = context.PersonHistory.Where(x => x.PersonId == bart.PersonId).OrderByDescending(x => x.SysStartTime).ToList();

                // First element of history must be actual person values, so it can be easily compared in a loop below
                bartHistory.Insert(0, new PersonHistory
                {
                    Name = bart.Name,
                    Email = bart.Email,
                    Country = bart.Country,
                    ChangeAuthorId = bart.ChangeAuthorId,
                    SysStartTime = bart.SysStartTime,
                    SysEndTime = bart.SysEndTime
                });

                for (int i = 1; i < bartHistory.Count(); i++)
                {
                    Person changeAuthor;
                    if (bart.PersonId == bartHistory[i - 1].ChangeAuthorId)
                        changeAuthor = bart;
                    else
                        changeAuthor = context.People.Find(bartHistory[i - 1].ChangeAuthorId);

                    if (bartHistory[i - 1].Name != bartHistory[i].Name)
                        displayLog(bartHistory[i - 1].SysStartTime, changeAuthor, "Name", bartHistory[i].Name, bartHistory[i - 1].Name);

                    if (bartHistory[i - 1].Email != bartHistory[i].Email)
                        displayLog(bartHistory[i - 1].SysStartTime, changeAuthor, "Email", bartHistory[i].Email, bartHistory[i - 1].Email);

                    if (bartHistory[i - 1].Country != bartHistory[i].Country)
                        displayLog(bartHistory[i - 1].SysStartTime, changeAuthor, "Country", bartHistory[i].Country, bartHistory[i - 1].Country);
                }
            }
        }

        private static void displayLog(DateTime time, Person changeAuthor, string columnName, string oldValue, string newValue)
        {
            string changeAuthorName = changeAuthor != null ? changeAuthor.Name : "deleted user";
            Console.WriteLine($"{time}: User {changeAuthorName} changed {columnName} from {oldValue} to {newValue}");
        }
    }
}

And program output:

27.11.2016 16:20:31: User Bart Simpson changed Email from [email protected] to [email protected]
27.11.2016 16:16:12: User Homer Simpson changed Country from Scotland to USA
27.11.2016 16:16:04: User Homer Simpson changed Country from USA to Scotland
27.11.2016 16:15:14: User Homer Simpson changed Email from [email protected] to [email protected]
2

1 Answer 1

1

I would not shadow each field (except you have a strong requirement) but just have a table with Date, PersonId, FieldName and NewValue. OldValue is superfluous as it can be reconstructed from history.

An example record would be (leaving out Date)

ID Field   NewValue
2  Email   [email protected]
2  Country USA
2  Country Scotland
2  Email   [email protected]

You could generalize that more by having Table, PK, Fieldand NewValue instead.

Table  PK Field   NewValue
Person 2  Email   [email protected]
Person 2  Country USA
Person 2  Country Scotland
Person 2  Email   [email protected]
1
  • I was doing it similar way before, but I learnt about table versioning in SQL Server 2016 and I wanted to use it in my program. Maybe old idea is better, because with temporal tables I can't do migrations in EF and history tables take much more space. Nov 27, 2016 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.