Databases
StackOverflow2013
Postsdbo
POHow to retrieve an audit trail across two SQL Server tables and put them back in the right order?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to retrieve an audit trail across two SQL Server tables and put them back in the right order?
    text
    copied!<p><strong>Background Context:</strong> </p> <hr> <p>i have a database table called <strong>Project</strong> and a table called <strong>ProjectHistory</strong> with the same schema. I am using nhibernate so i also have a domain object called <strong>Project</strong> and a domain object called <strong>ProjectHistory</strong> (which derived from <strong>Project</strong>).</p> <p><strong>Goal</strong><br> <hr> 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</p> <p><strong>Details</strong><br> <hr> Both tables have the following fields and data types</p> <ul> <li>Id -(int )- <strong>primary key in Project, NOTE: no PK in the history table</strong></li> <li>LastUpdated (datetime)</li> <li>Name (varchar)</li> <li>Description (varchar)</li> <li>Timestamp (timestamp)</li> </ul> <p>the goal of the <strong>Projecthistory</strong> table is that whenever i do an update to the <strong>Project</strong> table, the <strong>ProjectHistory</strong> table get inserted a new row with the old record so i can have a full audit trail.</p> <p>I accomplish this through using this trigger:</p> <pre><code> ALTER TRIGGER ProjectTrigger ON Project AFTER UPDATE AS SET NOCOUNT ON insert into ProjectHistory select * from deleted </code></pre> <p>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.</p> <p><strong>Example:</strong> <hr> Here is an example of events:</p> <ol> <li>On Jan 10 - create project 1 (nothing in history yet)</li> <li>On Jan 11 - create project 2</li> <li>On Jan 15 - edit project 2 ( will put entry in history table of project 2 with Jan 11 date as lastupdated by)</li> <li>On Jan 25 - edit project 1 (will put entry in history table of project 1 with Jan 10 date on it)</li> </ol> <p><strong>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.</strong></p> <p><strong>Code</strong></p> <hr> <p>I have tried the following code to try to put them together but I will explain the flaw below:</p> <pre><code> public IEnumerable&lt;Project&gt; GetHistory&lt;Project, ProjectHistory&gt;(int numberOfChanges) { IEnumerable&lt;Project&gt; current; IEnumerable&lt;Project&gt; history; current = Session.Query&lt;Project&gt;() .OrderByDescending(r =&gt; r.LastUpdated) .Take(numberOfChanges); history = Session.Query&lt;ProjectHistory&gt;() .OrderByDescending(r =&gt; r.LastUpdated).Cast&lt;Project&gt;(); IEnumerable&lt;Project&gt; all = current.Concat(history); return all.OrderByDescending(r =&gt; r.Id).ThenByDescending(r =&gt; r.LastUpdated); } } </code></pre> <p>My main issues with the above code are:</p> <ol> <li><p>The <strong>LastUpdated</strong> time on the history records are going to reflect the <strong>LastUpdated</strong> time of the <strong>previous</strong> 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. </p> <ol> <li>I could use the <strong>timestamp</strong> 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.</li> </ol></li> <li><p>I can use <strong>lastUpdated</strong> field on the Project table because sorting by <strong>lastupdate</strong> field will get me the latest recent events in order. I thought of looping through just the <strong>project</strong> 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.</p></li> </ol> <p>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.</p> <p>I already have code that can compare one <strong>Project</strong> object with another <strong>project</strong> 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 <strong>in the right order</strong> (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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload