Re: modelling history in a database

Paul Vernon wrote:
> "Alan Gutierrez" <ajglist_at_izzy.net> wrote in message
> news:GVh3a.10623$4F3.524954_at_news2.east.cox.net...
> [snip]
>
>>The DDL history tables assoicated triggers and past views are all
>>genrated by a Perl script. Becasue it is so easy to create history
>>tables, I maintain them for all the tables in my application.
>
> Do you use a trigger to populate your Trasnsaction table, or is it an
> application thing? If the latter then that makes your database application
> specific, which is a shame.
>
> I can do it with triggers in DB2 if I use CURRENT TIMESTAMP as the
> 'transaction id'. It gets a bit messy due to the need to cater for updates
> through UNION ALL views and ATOMIC statements, but it could still be workable
> in a production system.

Mr Vernon

The transaction table is fed through a stored procedure, so the

     application must remember to call this stored procedure before any
     DML.  Otherwise, the history tables are transparent, all triggers
     (actually, PostgreSQL rules). If a client is only interested in
     selects, the transaction need not be created.

I am designing for application independence. The code is PostgreSQL
     specific, however. I'll be looking for a way to create the
     transaction row automatically, through an event exposed by
     PostgreSQL, or perhaps just by checking to see if it is there when
     it is needed.

You'll have to excuse me. Your last message was thought provoking. I'm
     gathering myself so I can respond intelligently. I'm working on this
     history implementatation this weekend. I'll have more to say when I
     stitch things up. I'm certian to abandon the deleted column at your
     suggestion. Need to get some answeres about the PostgreSQL
     current_timestamp before I abandon the transaction_id.

Alan Gutierrez - ajglist_at_izzy.net Received on Sun Feb 16 2003 - 04:41:55 CET