I am attempting to track changes to records and individual fields over time. I was thinking of creating a histoy table for each table I wanted to track the changes of. Changes will be Inserts, Updates and Delete's. I would like to add a new row to the history table when the target table/record/column is changed. Would the way forward be to use triggers? or is there another way of approaching this? I'm aware that the history tables could grow rapidly, as a guide the target tables have just under 2 million rows and approx 20 fields but I don't think all the fields would need to be tracked. I want to be able to use the histrory tables to produce some reports i.e. How many records have been updated this week etc.
If you know of any software out there that would help carry out this task then please let me know as I am not a SQL wizard!
Thanks for any advice / help you can give me.