July 26, 2012 at 2:38 pm
The ultimate goal is to allow a customer to rollback any changes. Whether it is a minute ago or a day ago. We don't want to manually have to do this.
I am currently proposing the following options that will take some additional planning and possibly application changes, but minimal:
1. any time a row is updated or deleted it will be moved to a corresponding "audit" or "history" table with certain extra columns for a change time, id, and a change_group_id or something like that.
2. Do not ever delete a row in these tables or modify application related data in rows. Always insert a new row and make it the parent in a hierarchy for UPDATES and update a deleted_date or obsolete_date column for DELETES.
The first one would be trigger based, while the second would be stored proc/application based (depending on if they are using stored procs or not for the specific operation).
Thoughts? Either of these would allow individual operations, groups of operations, and time ranges to be "undone."
Jared
CE - Microsoft
July 26, 2012 at 3:37 pm
SQLKnowItAll (7/26/2012)
The ultimate goal is to allow a customer to rollback any changes. Whether it is a minute ago or a day ago. We don't want to manually have to do this.I am currently proposing the following options that will take some additional planning and possibly application changes, but minimal:
1. any time a row is updated or deleted it will be moved to a corresponding "audit" or "history" table with certain extra columns for a change time, id, and a change_group_id or something like that.
2. Do not ever delete a row in these tables or modify application related data in rows. Always insert a new row and make it the parent in a hierarchy for UPDATES and update a deleted_date or obsolete_date column for DELETES.
The first one would be trigger based, while the second would be stored proc/application based (depending on if they are using stored procs or not for the specific operation).
Thoughts? Either of these would allow individual operations, groups of operations, and time ranges to be "undone."
Personally i am inclined to use a separate history/archive table for a couple of reasons: 1. it could be accomplished with ON UPDATE and ON DELETE triggers possibly with out any application changes other than to implement the roll back feature. and 2. it prevents the table from growing so large that you now have a performance drain if you don't watch your queries and indexes (Filtered index on however you define an old record).
In either case i would have a business rule on data retention cleaning old data after that time frame has passed so you dont keep data that has become stale and will never be used for a roll back so you dont look back 4 years from now and go "Why do we have 30TB of data and only use 10% of it".
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply