Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Tracking change history, by column...

    This one is giving me quite a bit more difficulty then I ever imagined it would...

    Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.

    I can't seem to find a good way to do this.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation, but isn't that what syslogs already does? If you get Log Explorer, I think it already has more info that you could possibly want!

    If you really want to "roll your own", it can be done but it takes a lot more work (and disk) than it seems.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that's a lot more work than just copying the entire row to history...

    Look at IF UPDATED() in the trigger
    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.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Well that's a lot more work than just copying the entire row to history...

    Look at IF UPDATED() in the trigger
    Yeah, I'm well aware of that unfortunately. Life would be much easier if I were to simply duplicate my db structure and slide another row in the history table everytime a change is made.

    Currently I was fiddling with the COLUMNS_UPDATED. The problem is dealing with an unknown number of columns being updated. The only way around this I've found so far is to write an evaluation for every single field. Ideally I'd like to create a loop that would return all the field names and be done with it. The issue is also slightly complicated by the fact that I need to store previous AND new values. This means I need to write BEFORE UPDATE and UPDATE triggers for each table.

    *sigh*
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just to add another wrinkle with the 1 table theory...what about the different datatypes?

    If you still want to go down this path, then I would probably use the INFORMATION_SCHEMA.Columns view to generate the triggers....
    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.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I was looking at going the NVARCHAR route. It tends not to bitch much about implicit type conversion.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wouldn't Log Explorer be easier, safer, and in the long run cheaper than "roll your own" solutions?

    -PatP

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    Wouldn't Log Explorer be easier, safer, and in the long run cheaper than "roll your own" solutions?

    -PatP
    Whereas prices aren't listed, I can't speak to the cost-effectiveness aspect.

    I don't see anything dangerous about appending records to history tables.

    And yeah, it would be a hell of a lot easier.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    "roll your own"
    -PatP
    ok...

    But how much data are you predicting?

    The simplicity of just moving the row has alway worked for us...

    And with all the overhead of checking every column...for every update...for every table?

    Can you say locking?

    It would be a bottleneck....

    Anyone else?
    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.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    ok...

    But how much data are you predicting?

    The simplicity of just moving the row has alway worked for us...

    And with all the overhead of checking every column...for every update...for every table?

    Can you say locking?

    It would be a bottleneck....

    Anyone else?
    Well, I'm wrestling with bloat vs. bottleneck. Right now we can afford either. To dump a row, I would basically need to duplicate my schema, an idea I'm not to keen on. Right now there aren't a great many updates, but we'll see...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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