This example demonstrates that current data modifications do not apply to future rows. Assume the following tables describe a company’s employees and departments:
CREATE MULTISET TABLE employee ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( eid INTEGER NOT NULL, ename VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, bdate DATE FORMAT 'yyyy/mm/dd', job_duration PERIOD(DATE) NOT NULL AS VALIDTIME, deptid INTEGER, mid INTEGER) PRIMARY INDEX ( eid ); CREATE MULTISET TABLE dept ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( deptid INTEGER NOT NULL, deptname VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC) UNIQUE PRIMARY INDEX ( deptid );
Assume the company management must cut down the staff of the SUPPORT department. Employees who have been with the company for less than three months must be discharged. The following query removes employees with job durations of less than three months:
CURRENT VALIDTIME DELETE employee FROM dept WHERE dept.deptname = 'SUPPORT' AND dept.deptid = employee.deptid AND BEGIN(job_duration) > CURRENT_DATE - interval '3' month;
Now assume that the system includes employee entries for new employees who have not yet started working at the company. The preceding DELETE statement will not remove these future employees. To remove them together with the current employees, the following statement could be used:
BT; /* delete currently employees in department with less than 3 months work*/ CURRENT VALIDTIME DELETE employee FROM dept WHERE dept.deptname = 'SUPPORT' AND dept.deptid = employee.deptid AND BEGIN(job_duration) > current_date - interval '3' month; /* delete all future employees */ SEQUENCED VALIDTIME DELETE employee FROM dept WHERE dept.deptname = 'SUPPORT' AND dept.deptid = employee.deptid AND BEGIN(job_duration) > TEMPORAL_DATE; ET;
Alternatively, the following SQL would accomplish the same:
REPLACE VIEW v1 AS NONSEQUENCED VALIDTIME SELECT employee.eid, dept.deptid FROM employee, dept WHERE dept.deptname = 'SUPPORT' AND dept.deptid = employee.deptid AND BEGIN(job_duration) > CURRENT_DATE - interval '3' month AND job_duration OVERLAPS PERIOD(TEMPORAL_DATE, UNTIL_CHANGED); SEQUENCED VALIDTIME PERIOD(TEMPORAL_DATE, UNTIL_CHANGED) DELETE employee FROM v1 WHERE v1.eid = employee.eid AND v1.deptid = employee.deptid;