>>>Hi Using MM.NET 3.6 with C#, ASP.NET and SQL Server 2008 R2.
>>>
>>>I need to track all changes made to the database. There is no budget for a third party tool so I am looking for something free that will give me the ability to track changes made to all or selected tables, knowing who made the change (using the ID of the currently logged in MM.NET user). I will then need to run queries/reports against this data to answer questions like, "Who made this change when?".
>>>
>>>I've found a few articles on SQLServerCentral.com and am reading through them now (for example: http://www.sqlservercentral.com/articles/SQL+Puzzles/anaudittrailgenerator/2067/ but I'm not sure how to adjust that to store the MM.NET user id) and will do some more Googling, but was wondering if anyone here could recommend a solution.
>>
>>You can add some code in the ABusiness object that is generic to log the changes. It is tricky for a couple of reasons. First the different data types can be challenging especially if you are storing any images or other complex things. Also, the hook methods in MM are many. The post save events do not provide back the values before the save so you have to capture this in the PreSave hook methods and then confirm the logging after the postSave hook method fires. You would be best served by creating your audit tables and create business objects for them. Then add your code in ABusiness object to use those business objects just for saving the data in the audit logs. Put the audit logic in ABusiness object.
>>
>>The other issue is the different hook methods for different saves, updates, deletes; and depending on if you are using Entity Framework, or if it involves a single entity or an entity list. When I did this, I created my methods to perform the logging and then used all the hook methods to call my auditing but also needed a couple of overloaded methods because the hook methods provide the pre saved data differently. Entity versus datasets, etc.
>>
>>Tricky, but it can be done without a tremendous amount of code.
>>Timothy
>
>
>Thanks Timothy (that sounds so formal) :)
>
>So there's no way to do this in the database itself? In my VFP applications I use triggers to update other tables storing xml values of the changed data. I see various suggestions on SQL Server sites to use "mirrored" tables, but they all use SUSER_SNAME() to get the user name, but I *think* that mm.net uses impersonation to access the database so that will store the wrong user. Or am I off target there and it will get the right user? It's been so long since I did the database access part of this project that I can't remember now :)


Hi Frank,

You can absolutely do it at the database level and many other solutions have done this. The issue is you will want to provide the user name or id with each change so that can be logged as well. Best way to do that might be to instantiate all the business objects using a factory method that adds the logged in user to a business object property so it can be included with each insert, update, or delete. However if you use triggers, how will you get the username or id included?

I have done logging in the application primarily because I like to include application level information as well; such as from where in the application was this information changed. It is more difficult to do this in the database if you want this type of logging. Just getting the UserID might be tricky depending on how you are using your connections to the database.

Good luck
Timothy - yea more formal, but I am referred to by that name here so trying to get used to it. :-)