How to Maintain History of data in tables.

Question

How to Maintain History of data in tables.

hemanth T

SSC Eights!

Points: 807
More actions
September 24, 2011 at 2:56 am

#146351

Hi All,
I am part of development of an erp application.I need to capture the history of data which is mandatory for capturing history..details.
Can anyone give solution-how to handle this scenario by following some Database structure..for sample.
Thnks,
Hemanth.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 1

I am part of development of an erp application.I need to capture the history of data which is mandatory for capturing history..details.
Can anyone give solution-how to handle this scenario by following some Database structure..for sample.

Surely, You can do it typically by creating history tables with some History suffixes and add couple of more fields such as create date time and Insert into that table at the day end. You may create indexes on that table to retrieve data faster.

Regards,

Sudhir

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

On of the easiest ways to do this, and it will capture any and all changes, is to do as suggested above but use some very well written triggers (and can handle more than one row at a time) to insert any changed rows into the history table for either UPDATEs or DELETEs only. It is not necessary to capture the original insert because, if no modifications are made to it, it will reside in the original table. Capturing INSERTs does nothing for you except instantly double the amount of disk space that any table-pair should take.

You'll find some folks that recomend only capturing the column name and the original data. I'll strongly recommend against that because it would require the "value" column to handle any data and you'll also be in a world-of-hurt if you ever need to reassemble rows to a given point in time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Search auditing on this site. We have numerous articles on the subject.