On 2/21/07, Mike Chabot wrote: > 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.
+1 X 10^8 ;) My initial post was going to simply be triggers, however let me make this comment so the full power of a trigger can be explained: Any time SQL Server handles a transaction (Insert, Update or Delete) it checks to see if there is a trigger involved. If there is it executes this sequence of SQL statements while also creating two temporary tables - inserted and deleted. These contain the records about to be altered in the database. You can use these in combination with the original table to automagically log changes. There are two issues with this concept - first is the fact that you't tell *who* changed something using just triggers. What I've done in the past to compensate for this is add a "User_ID" field to every table that effectively records "Last Changed By" and is a part of every transaction. That way in my audit table I simply record that ID along with the old value and new value. Second is the fact that while you can look at the tables, extracting the field names can be a different issue. For this I actually used a lookup table that held the table name and column names of the columns I wanted to log. Oh, one third issue, you'll have to have a trigger for each table. Good luck! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270409 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
