June 30, 2013 at 6:36 pm
Hi All,
Can you please give me some suggestions on how I can maintain a Audit history during row updates?
I am doing a project which involves the User updating the Cost for each product using the Online User Interface.
When the user hits the Update button I need to copy the Product Key, old value with an start and end date into a history table including the Active Directory login.
And this information will be used in the Product cube to show how the cost has changed during the time and also to calculate the Revenue based on the cost during that time.
I am also wondering if it is best to maintain the Audit history in a different table or use the same Product table and just insert a row with end date time stamp for the old value and create a new row for the updated value.
Please give me your valuable suggestions on how to go about it, which way would be best since the update will happen using an Web User Interface.
Best Regards, Shilpa.
July 1, 2013 at 2:40 am
As you have mentioned about using the audit data for building cube, I would recommend to create a dedicated table to keep all audit history of Product changes.
Then you can create an audit trigger (for Update, Insert and Delete), which will copy all Product data from Product table together with some required audit details (eg. date change, user info etc.) into "Product_Audit" table whenever any data modification is performed in Product.
There is another way to track changes in SQL Server you ight consider. It's called CDC (Change data Capture). Have a look it too. However I think audit triggers are more convinient for futher use in building cubes.
July 1, 2013 at 3:37 am
There is a nice article on CDC from Pinal
i would recomend using of history table and some after triggers to use it in your db to track the changes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply