MS SQL Server. T-SQL. The organization "change history"

I wanted to make any changes to a particular record in a particular table were monitored and it was always possible to revert to an older version.

On reflection I prikol that it would be good to implement this on the DB level to avoid any lining application.

Create mytable_history, with a signature identical to mytable, supplemented by fields of historyID and historyDate, hang on mytable triggers when inserting or modifying copy an appropriate entry in the history with the new historyID, and put date changes.

Are satisfied with the solution. Any change will always be reflected, and the old version is retained even if the hacked website — access the account Ktorov website goes to DB to table _history no.

And then wanted to do more, to know which user made the change. But at the database level is unknown. The user ID is known only at the application level, database-are they all "one person" and go under the General account to the sql server.

My first thought was if you connect to create a variable with the user ID and use it in triggers. But the same connection can use different website users ASP.NET keeps in the pool n-e number of ports and outputs as needed, so that's no good. So you need to pass the ID in each request to the database.

Actually questions:
1) whether do such design? Maybe the fact that I want another make? But you do not want the same, to make the system versions of the database.

2) how to request to attach a variable so that it doesn't affect the request, but was available for the AFTER UPDATE trigger.

Used linq2sql, i.e. it is theoretically possible to make your DataContext, and to make sending user ID with each request. The only question is how it can be implemented?
October 10th 19 at 14:23
2 answers
October 10th 19 at 14:25
Solution
In the first part of the question I can say — Yes, the design is quite normal. I use the same approach (Oracle).

Part of keeping track of what user made a specific change — it depends on how you store information about the users. If the users stored in the database in a separate plate, I assume it is possible to do so.

Last_modified_by_user add a column in mytable, and, accordingly, the table mytable_history.
Accordingly, when a user performs an operation (insert / update / delete), you put the ID of the person this user, and when triggered, your after-insert, after-update and after delete row-level trigger this aydishnik will be transported into an audit table.

And how to insert aydishnik user initially, the table mytable? Well, to create the server-side user-context that stores the name / user id associated with the user session, and when the query is executed with the level of business logic to populate this field.

Something like that.
Thanks for the reply. I decided, instead of triggers are passed to the stored procedure, it is possible to make all necessary changes to application-level linq2sql entities without making absolutely no changes to the business logic. I'm thinking to write about this topic. - Vince_Donnelly commented on October 10th 19 at 14:28
October 10th 19 at 14:27
It is not necessary to invent a Bicycle - at the level of MS SQL is already implemented https://msdn.microsoft.com/en-us/library/bb933994.aspx

Find more questions by tags SQL ServerTransact-SQLC#