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