MS SQL Server. T-SQL. Organization of "history of changes"

Question

C++ / C#

I wanted to make sure that any changes to any record in a particular table are tracked and you can always roll back to an older version.

On reflection, I decided that it would be good to implement this at the database level in order to exclude any overlays in the application.

We create mytable_history, with a signature identical to mytable, supplement the historyID and historyDate fields, hang triggers on mytable that, when inserted and modified, copy the corresponding record to history with a new historyID, and put down the date of the change.

The solution suits. Any change will always be reflected, and the old version is saved even if the site is hacked - the account by which the site goes to the database does not have access to the _history table.

And then I wanted more, to know which user of the system made this or that change. But at the database level, this is unknown. The user ID is known only at the application level, but for the database they are all “the same person” and go under a common account to the sql server.

The first thought was when connecting to create a variable with the user ID and use it in triggers. But the same connection can be used by different users of the site - ASP.NET keeps the number of connections in the pool ne and issues it as needed, so this is not good. Means it is necessary to transfer the identifier in each request to a DB.

Actually questions:
1) whether such design is correct in general? Maybe what I want is done differently? But it would not be desirable to take out system of versions from a DB.

2) how can I attach a variable to the request so that it does not affect the request, but is available to the AFTER UPDATE trigger.

linq2sql is used, i.e. theoretically, you can make your own DataContext and send the user id with each request. The only question is how can this be done?

Answer 1

For the first part of the question, I can say - yes, the design is quite normal. I'm using roughly the same approach (Oracle DBMS).
As far as keeping track of which specific user made a particular change, it depends on how you store information about users. If users are stored in the database in a separate plate, as I suppose, then you can do this.
Add the last_modified_by_user column to the mytable table, and thus the mytable_history table.
Accordingly, when a user performs an operation (insert / update / delete), you set the ID of this user, and when your after-insert, after-update, after-delete row-level trigger fires, this ID will be transferred to the audit table .
And how to insert the user ID initially into the mytable table? Well, create a server-side user-context that stores the user-id name associated with the user session, and fill in this field when making a request from the business logic layer.
Something like this.

Answer 2

No need to reinvent the wheel - this is already implemented at the MS SQL level https://msdn.microsoft.com/en-us/library/bb933994.aspx