| Subject | Re: Fast Temporal data in Firebird |
|---|---|
| Author | chris.waldmann |
| Post date | 2011-02-08T11:13:12Z |
--- In firebird-support@yahoogroups.com, "robert.gilland" <robert.gilland@...> wrote:
I have implemented an audit trail log with before insert and before update triggers that store the new version also in the audit trail table. The actual version is stored twice, what is very handy to have fast access to the actual view and easy access to all data in the audit trail tables to find changes.
The trigger adds the w information (who, when, what, why) to the audit trail table. "what" is a foregin key to the unique key of the actual table record.
To find the data at a given time, I have a descending index on the change timestamp field and "select first 1" "where timestamp <= reporttime" "order by timestamp descending".
The two triggers can also calculate some predictive, future data and store in future data tables.
Good luck
Christian
>Hi Robert
> Hi,
> We need to design a system, that keeps past/present/future data based on a time axis.
>
> ie. The system needs to know what price a product will be at times in future, was in time in past, and is currently.
>
I have implemented an audit trail log with before insert and before update triggers that store the new version also in the audit trail table. The actual version is stored twice, what is very handy to have fast access to the actual view and easy access to all data in the audit trail tables to find changes.
The trigger adds the w information (who, when, what, why) to the audit trail table. "what" is a foregin key to the unique key of the actual table record.
To find the data at a given time, I have a descending index on the change timestamp field and "select first 1" "where timestamp <= reporttime" "order by timestamp descending".
The two triggers can also calculate some predictive, future data and store in future data tables.
Good luck
Christian