We have a program which consists of client(multiple) -> gateway -> engine ... Now the customers would like a feature, which would enable them to see what and by who certain change was made, so we would need to save changes to some history table.
PROBLEM:
Because there could be multiple users connected to engine in parallel, how can we know, if we would implement history tracking using triggers, which user made certain change, how can we identify it in sql server (in some previous program, where connection to sql was sequential, they save, before processing, user id to a table and trigger used that id).
Regarding connection to SQL Server - some things are implemented using EF, some SP and some using dynamic query.
CDC is not an option, because not all customers posses enterprise version and even there some "hacking" would have to take place in order to have it work.
I read that CONTEXT_INFO could be used to save current user id to it and then in sql read that value from ... has anyone tried that?
Some even add columns to tables like modified date and user id, but we would like to avoid that if possible.
So ... what would be the best way to implement this? :)
First of all this could drastically impact your database performance and disk space...
But if you insist, you can figure a simple script that iterates over the list of all tables you want to monitor and dynamically create (via sp_executesql) the associated trigger.
Then having a name convention of those triggers might help you later to maintain them (e.g. to disable all of them while data intensive operations, etc).
I would suggest to try to convince your client to activate this feature only for few critical data tables. Also, always keep at hand a script that deactivate all those triggers.