How to retrieve an audit trail across two SQL Server tables and put them back in the right order?

Question

Background Context:

---

i have a database table called Project and a table called ProjectHistory with the same schema. I am using nhibernate so i also have a domain object called Project and a domain object called ProjectHistory (which derived from Project).

Goal

---

My goal is to retrieve data across these tables and put them back in order as the events that happened and have C# code output a typical audit trail screen of last n number of changes

Details

---

Both tables have the following fields and data types

• Id -(int )- primary key in Project, NOTE: no PK in the history table
• LastUpdated (datetime)
• Name (varchar)
• Description (varchar)
• Timestamp (timestamp)

the goal of the Projecthistory table is that whenever i do an update to the Project table, the ProjectHistory table get inserted a new row with the old record so i can have a full audit trail.

I accomplish this through using this trigger:

 ALTER TRIGGER ProjectTrigger
 ON Project
 AFTER UPDATE
 AS
    SET NOCOUNT ON

    insert into ProjectHistory
    select * from deleted

My issue is that i now need some C# code to put together an audit history where a user select the most recent n number of events.

Example:

---

Here is an example of events:

1. On Jan 10 - create project 1 (nothing in history yet)
2. On Jan 11 - create project 2
3. On Jan 15 - edit project 2 ( will put entry in history table of project 2 with Jan 11 date as lastupdated by)
4. On Jan 25 - edit project 1 (will put entry in history table of project 1 with Jan 10 date on it)

so, as you can see if i sorted descending by lastupdated field in the history table, it was put the result from event 4 before the result from event 3 which is out of order.

Code

---

I have tried the following code to try to put them together but I will explain the flaw below:

   public IEnumerable<Project> GetHistory<Project, ProjectHistory>(int numberOfChanges)
    {
        IEnumerable<Project> current;
        IEnumerable<Project> history;
            current = Session.Query<Project>()
                .OrderByDescending(r => r.LastUpdated)
                .Take(numberOfChanges);

             history = Session.Query<ProjectHistory>()
            .OrderByDescending(r => r.LastUpdated).Cast<Project>();

            IEnumerable<Project> all = current.Concat(history);
            return all.OrderByDescending(r => r.Id).ThenByDescending(r => r.LastUpdated);
        }
    } 

My main issues with the above code are:

1. The LastUpdated time on the history records are going to reflect the LastUpdated time of the previous Project update so i can't sort desc on that field in the history table and assume i will get a correct descending order of events.

   1. I could use the timestamp field for sorting (as that was the actual time that the record was entered) but it doesn't seem like you can sort on that field using C# nhibernate because that field is just a binary(8) so it translates to a byte[] in C# which doesn't support IComparable.

2. I can use lastUpdated field on the Project table because sorting by lastupdate field will get me the latest recent events in order. I thought of looping through just the project table and then doing a separate query per each project to grab the latest history project but them this doesn't support a case where multiple changes to the same project id occurred. The issue is i can't figure out a way to get the data in the correct order.

My main point is that I want to accomplish what I think is a pretty generic thing that must be solved already so i must be just missing the basic pattern here.

I already have code that can compare one Project object with another project object and return the field diffs (which works perfect) but I just need a suggestion on figuring out how i get this data out of the database across these tables in the right order (either from my query or code after the fact) so I can show "the last n number of changes" and then use my logic to show the field diff for each of those changes.

Answer 1

If I've understood your question correctly you can simply sort on the timestamp converted to long.

// The last row in your question, 0 means start from the start of byte[]
return all.OrderByDescending(r => BitConverter.ToInt64(r.Timestamp,0));

Answer 2

Option 1: Here's a link that allows you to use the timestamp in an order by clause. I haven't used this myself, so use at your own risk.

Option 2: Add an auto increment field to ProjectHistory and then you can sort your results by the id of the project and then the auto-increment id. I haven't use the cast operation much, so I'm not sure of those implications, but I'm sure you'll have more work with mapping.

Option 3: Have you investigated NHibernate Envers, which is a framework for supporting and audit trail? This will be a new learning curve, but it may save you a lot of work in the long run.

Answer 3

Here is my solution for one similar project. Create a view to union project and project history table. Let SQL server to do the heavy lifting.

Here is an example:

CREATE view [dbo].[VwProjectHistory] as select
    OtherFieldName,
    LastUpdated
from
    [dbo].[Project]

union all

select
    OtherFieldName,
    LastUpdated
from
    [dbo].[ProjectHistory]