Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to maintain Audit history during Update Operation Expand / Collapse
Posted Sunday, June 30, 2013 6:36 PM

Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 15, 2015 5:32 PM
Points: 66, Visits: 205
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.
Post #1468905
Posted Monday, July 1, 2013 2:40 AM


Group: General Forum Members
Last Login: Monday, November 28, 2016 10:12 AM
Points: 2,934, Visits: 5,475
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.

"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1468974
Posted Monday, July 1, 2013 3:37 AM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 31, 2015 5:26 AM
Points: 1,212, Visits: 798
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.
Post #1468981
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse