Maintaining history of data

What is this about?

I tend to quote Fowler: “Usually when we see properties on a class,they represent questions we can ask of an object now. However there are times when we don’t want to just ask questions about a property of an object now,we also want to ask these questions about some point in the past when things may have changed. “

For example,we want to track every change that occurs at an Employee ( changing name,salary) and see the previous versions( what was the salary 2 years ago ?)

So what are the software solutions to do an audit log ? First,we should have a history table for each table that we want to maintain an audit log. E.g. Employee => EmployeeHstory

Solution 1 : Coding / Event Sourcing

That means code should maintain history himself – i.e. adding to the history table every modifications.

For Entity Framework this can be done easy,by overiding SaveChanges.

Demo at https://youtu.be/YZ9GDjFApns 

Solution 2 : Table Triggers

This can be done from database . There are triggers for insert,update,delete – and those triggers insert the data in the history table

Demo at https://youtu.be/9xsvCJ-1f7Q

Solution 3,Temporal Tables,Microsot  Sql Server 2016 :

Documentation at https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables 

Limitations at https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations 

Demo at https://youtu.be/1Q3DmXJI5h8