>>Looking for some basic concepts. I'm planning on developing a tool that will take snapshots of tables after data changes and compare them in order to develop some delta results. These results will be used primarily by already-existing calculation processes to verify financial balances, help determine impact on the overall system, create cases for regression testing, etc. I will include the ability to create sample data based on specific test case criteria. Before I start getting into it, I was hoping for some general feedback.
>
>Hi Renoir,
>
>My suggestion would be, instead of comparing existing data, save an "audit trail" as the data is being changed, i.e., use a trigger to store information about when data was changed to what table. Unlike the more or less primitive example in my introductory article - http://www.utmag.com/wconnect/wc.dll?9,7,10,1476 - you should aim for having data in a machine-readable format.
>
>Otherwise, make sure that each table has a PK, on which you can compare different versions, with standard SQL commands.
>
>HTH,
>
>Hilmar.

By data changes, I'm talking more of structural changes to the database. Sorry I didn't clarify that. My tool needs to work on the data in the tables as they exist at any given snapshot. These snapshots will happen at any time there was a structural change, or a significant/complex change to the code. It will be a way for us to make sure that the change has only the affect that we expect. It's kind of vaporware in my mind right now, so I'm definately open to all suggestions. For keeping up with the actual data in the tables, I do like your idea of tracking only the field-level changes rather than logging all fields at the record level, which is more common.