Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    Unanswered: Tracking the history of changes.

    Hi all,

    I have several transaction tables on which I need to track the changes. That is I need to maitain
    the history of changes. Only few column values are changed often.

    Which is the best way for tracking the changes.

    1.Store the whole record after the change ?

    Or

    2.Store the ColumnName & its respective old & new value ?

    Or any other better.

    Note : UI part & SP's will take care of the tracking & no plans for triggers.

    Thanks in advance,

    HHA

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Depends on how you're going to use the tracking data, I guess.

    Assuming that there's just a few values changed often (as I interpret your explanation), I'd go for no. 2.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I create an exact copy of every table I want to track, then I create a trigger on each table to store the updates and deletes. The Inserts are in the base table, so noneed to copy that over. Just add three extra columns to the history tables.

    HIST_ADD_DT
    HIST_ADD_BY
    HIST_ADD_TYPE

    I even have some code that generates all of this code lying around somewhere...

    Oh, here it is

    http://weblogs.sqlteam.com/brettk/ar...0/20/2242.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •