Suppose I want to track who changed contact information and what it used to be, who created the first entry in a series of edits, etc. I haven't found a solution I really like online so far. SQL Server 2017 has built-in change tracking, but I didn't see any way to capture data on which user executed the request that caused a row change. That last part is the trick, it seems.

A good solution has the following:

• Quick to query
• Decoupled from application code

My thinking is that tables I want to track have the following columns:

• CreatedById - user id for who created the original version of the row
• OriginalVersionId - change table id of the original version of a row, could use a better name
• UpdatedById - user id of person who created this version of the row
• PreviousVersionId - change table id of the most recent version of a row prior to the current one

I then set up a trigger on any table I want to track such that when it's edited, it creates a new entry in the change table and puts the id of the created change table entry into the "PreviousVersionId" column.

My main concern is that I've mostly done code-first and let EF handle the details, so I don't actually know if I can snag the id of the change table row right when I create it in order to use it with the source table.

If I can, this seems perfect. I can either cycle through the history like a linked list, or I can query the whole list using the OriginalVersionId. It doesn't require any application code to run, either.

Thoughts? Would you approach this a different way?

EDIT: I should mention that we're a small team on what is probably an unrealistic timeline. Easy-to-implement solutions that won't screw us long-term get bonus points!