Re: Audit Logs WAS: temporal support patch

2012/8/22 Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>:
> On 08/22/2012 08:34 AM, Gavin Flower wrote:
>
> About 10 years ago, I implemented some temporal features in a database to
> cope with insurance quotes that had to be valid for a specified number of
> days in the future that was invariant with respect to future changes in
> premiums with effective dates within the period of validity of the quote. If
> anyone is interested, I'll see if I can find my notes and write it up (but
> in a different thread!).
>
> Cheers,
> Gavin
>
> What you mean is not an audit logs, it's a business time. Pavel Stehule in
> the beginning of this thread gave a link to a description of SQL2011 design
> of this feature. Audit logs are more related to system time. For example IBM
> DB2 uses following syntax for system time (which is mostly
> SQL2011-conformant).
>
> CREATE TABLE policy (
> id INT primary key not null,
> vin VARCHAR(10),
> annual_mileage INT,
> rental_car CHAR(1),
> coverage_amt INT,
>
> sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
> sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
> trans_start TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID
> IMPLICITLY HIDDEN,
>
> PERIOD SYSTEM_TIME (sys_start, sys_end)
> );
>
> CREATE TABLE policy_history LIKE policy;
>
> ALTER TABLE policy ADD VERSIONING USE HISTORY TABLE policy_history;
>
> And the following syntax for querying for historical data.
>
> SELECT coverage_amt
> FROM policy FOR SYSTEM_TIME AS OF '2010-12-01'
> WHERE id = 1111;
>
> SELECT count(*)
> FROM policy FOR SYSTEM_TIME FROM '2011-11-30' TO '9999-12-30'
> WHERE vin = 'A1111';

I like this design - it is simple without other objects

Regards

Pavel