Post reply

COALESCE Vertically??

  • May 15, 2006 at 3:13 pm

    #638020

    CAn you expand on that pk idea??

  • May 17, 2006 at 5:42 am

    #638376

    Noeld,

    If you read the entire thread, you will notice that:

    • the triggers are indeed logging only changes*
    • a TRANSACTIONID** (the RECVER column) is indeed used in the log table for the PK.
    • there is NO pivoting at all. Either server side or client side. Anyway, who says it is being used by "applications". Very often a corporate client will decide to use Crystal or some other reporting tool. If the schema is not straightforward, he's hosed. By exposing views he at least has a way to get data meaningful to him.

    * Logging only changes can have a downside  ...what happens when the new value is really NULL the production table when a NULL in the log table means that column has not changed? I am handling this (in real life, not in the sample I posted) with a bitmap. Haven't found anything better. Can't avoid NULLs...FK, LOBS, XML, etc.

    ** A row version is used instead of a transaction id. A transaction id is meaningless in itself. When an object is persisted in the database it involves N tables. The transaction id must be the same for all inserted/updated/deleted rows but if you want to look at the state of the object at a specific point in time, the transaction id is not sufficient since many rows that were not modified in that transaction may still have belonged to the object at that time; they were simply not dirtied. To get a valid view of the object at some point in time, you need to get the closest row version, so you need PK/FK + a datetime in all your JOINs to time travel through your object states.

    Would be nice to extend SQL to support versionning:

    SET POINTINTIME 2005-05-22 14:05:22 157

    Select ....

    A row version is necessary at the table scope and for concurrency reasons (pessimistic locking does not scale and won't work in three tiers; and timestamps are just versionning fast food )

    At the transaction scope, a datetime is much more useful than a transaction id. At least it conveys some useful information. Do a DISTINCT of the transaction datetime of all tables/tuples used by the object and you get the object's history.

Viewing 2 posts - 16 through 16 (of 16 total)

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