Re: Audit Logs WAS: temporal support patch

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';