I have programmed comprehensive audit trails and data change logs a few times before. I recommend using triggers on every database table that needs to be tracked, and force all queries to go through stored procedures. The benefit of using triggers is that if you go directly to the database and modify it, even that change is logged. It is a lot of work to create all the stored procs and triggers. There are programs that automatically create these auditing triggers for you. I think Apex SQL offers one. I evaluated most of these programs and decided to write custom code that did exactly what I wanted instead of relying on generic triggers.
Another option is to monitor and analyze the SQL Server transaction logs. Lumigent has a product that does this, although it is expensive. One SQL Server function that may help is Binary_checksum. It tells you if any row changed. If you want to report on just the specific data point that changed, you are going to have to write a lot of code to achieve that. Good luck, Mike Chabot On 2/20/07, Les Mizzell <[EMAIL PROTECTED]> wrote: > A client has requested a "change log" on their database. > > Basically, it's a SQL server database with several hundred fields used > for updating information on specific computer/data systems within a > group of businesses. What they're asking for is when client logs in and > updates their info, they want to keep a summary of what's been changed > in addition to updating the database itself. > > They do *not* want a full copy of the complete record each time a change > is made (which is what I would have done). They just want to see > something that says "On March 3, 2007, Bob changed field 64 on page 11 > of the form". > > Almost the only thing I can think of is to do a compare on every single > data field in the database on each submit to see what's changed, and > then add that to a log. Considering the number of fields - ack! > > I shouldn't complain too much, as they don't care how much it costs and > pay quickly - but, is there a more efficient way of doing this? > > Ideas? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270406 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
