I have done a lot of research into implementing audit trails and agree with what Hatton suggests. Although it depends on the importance of the audit trails. If the data is sensitive in nature or has legal implications attached to it, then it is better to control auditing at the database level, which helps log things if some database administrator or rogue employee tries to update the database directly or bypass the official application. It also protects you if some new programmer comes onto the project and doesn't realize they are supposed to be adding audit-related code.
If you just want to track changes as a subtle feature, such as keeping track of the previous email address whenever someone updates it, then that is much easier to do in code. Adding all the database triggers and stored procedures involves significant work. I will say that having an audit trail has been of great help when troubleshooting problems and resolving disputes as to who did what. It is great to know that every single change is logged so I can speak with confidence when someone asks me to research an issue. It is a wonderful feature to have if the business user is willing to make the investment. Good luck, Mike Chabot On 10/31/07, C. Hatton Humphrey <[EMAIL PROTECTED]> wrote: > > I have a multiuser CSR application that has some audit functionality to > > track > > certain changes to customer records (for instance change of first name or > > last > > name, who changed it and what was it before?). It already logs these > > changes, > > but I think it could be more efficient. I was wondering if anyone could > > comment > > on how they would go about doing this. Right now, it is strictly > > code-based. > > The database is SQL Server 2000 and the server is ColdFusion 8. > > Make use of triggers for INSERT/UPDATE/DELETE. > > For SELECT I use stored procedures that log the request and then execute it. > > The downside of using triggers is that there are no parameters. You > can overcome this by adding a "LastChangedBy" column and using it in > the trigger. > > That way I let the database engine do what it does best. > > Hatton > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:292415 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
