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 :
Demo at https://youtu.be/1Q3DmXJI5h8