How much writing vs. reading of this table(s) do you expect? I’ve used a single audit table, with columns for Table, Column, OldValue, NewValue, User, and ChangeDateTime – generic enough to work with any other changes in the DB, and while a LOT of data got written to that table, reports on that data were … Read more
In the project I’m working on, audit log also started from the very minimalistic design, like the one you described: event ID event date/time event type user ID description The idea was the same: to keep things simple. However, it quickly became obvious that this minimalistic design was not sufficient. The typical audit was boiling … Read more
One method that is used by a few wiki platforms is to separate the identifying data and the content you’re auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an … Read more
Use “Insert Only Databases” The basic idea is that you never update or delete data. Each table has 2 datetime columns from and to. They start with the value null in each (beginning of time to end of time) When you need to “change” the row you add a new row, at the same time … Read more
IF you are using EF 4 you can subscribe to the SavingChanges event. Since Entities is a partial class you can add additional functionality in a separate file. So create a new file named Entities and there implement the partial method OnContextCreated to hook up the event public partial class Entities { partial void OnContextCreated() … Read more
I just want to call out couple of points: Use code generators You can’t have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation … Read more
If using EF6’s DbContext you can use ChangeTracker in SaveChanges override to find added/modified entities of custom type, for example IAuditedEntity. public interface IAuditedEntity { string CreatedBy { get; set; } DateTime CreatedAt { get; set; } string LastModifiedBy { get; set; } DateTime LastModifiedAt { get; set; } } public override int SaveChanges() { … Read more