We have the following (unfortunate) scenario: we have a database where on-the-fly changes are being made by developers as quick remedies to dictionary tables (order types, document types, fee distributions, and so forth). This wouldn't be an issue if these particular developers didn't forget to check their changes into TFS, but alas, we face it. This causes an issue where the database project(s) in TFS do not properly reflect what is in the database.
Essentially, I want receive an e-mail or other type of notification (table insert with necessary information) when a change is made to 15-20 tables, what the change was, and who committed the transaction.
What is the best method to monitor this type of behavior? We are willing to invest in tools that allow for this ability. I've checked out RedGate SQL Monitor, but it doesn't seem to have this feature set (or I am missing it).
I've also seen usage of triggers, but we have a large amount of tables that would require monitoring and I don't know the performance degradation that might incur and if we can collect the appropriate data. I do like the trigger approach where it would insert the information into a table ([dbo].[TABLE_UPDATES]) because it would allow us to build an SSRS report to run against and pull data as needed, but I'm not sure I 100% trust this as a solution.
We currently use RedGate DLM Dashboard for monitoring schema changes, and it has proven quite invaluable to receive those updates. We just want to expand it to the data itself.
Disclaimer: I know - terrible practice by developers to apply things directly to a database without checking in their changes, but we are currently having to deal with this hurdle and reign these individuals from wildly shooting at the hip. We are wanting a long-term solution for accountability purposes as our database and company grows.
Thanks in advance.