15.00 - Temporal Table Modifications - Teradata Database

Teradata Database Temporal Table Support

Teradata Database
Programming Reference

Temporal Table Modifications

Modifications to temporal tables can be current, sequenced, or nonsequenced. These operations apply to the valid-time dimension. The system automatically determines which rows are to be modified based on the interaction between the period of validity of each row and the period of applicability of the modification SQL.

With respect to the transaction-time dimension of transaction-time and bitemporal tables, a row is considered either open or closed. Open rows participate in database operations. Closed rows are historical snapshots of rows that have been deleted or modified subsequent to a prior state. After a row has been closed, it no longer participates in normal SQL operations, but can be viewed using temporal SQL.

Rows that have been closed in the transaction-time dimension remain as a permanent log of database operations on these rows. They are not available to be deleted or modified. However, these rows can be deleted and modified by users having the special NONTEMPORAL privilege, provided that capability is enabled in the database.

Period of Applicability

The period of applicability (PA) is the period specified implicitly or explicitly in a temporal query or DML statement. It is the period in valid time for which the query or modification applies. Teradata Database determines how to handle the SQL request based on the relationship between the PA of the statement and the PV in the valid-time column of the rows in the temporal table. PV is described in “Valid Time” on page 19.

Current Temporal Modification

Current temporal modifications are modifications to the current rows of a temporal table.

The period of applicability of a current modification implicitly begins from the current time and extends indefinitely, represented by an ending period bound of UNTIL_CHANGED. Current modifications need not explicitly specify a period in the modification SQL. The CURRENT keyword causes the system to automatically apply the modification to those rows whose period of validity overlaps the current time.

Note: Some proposed implementations of temporal tables suggest that a current modification also apply to future rows. In the Teradata Database implementation, a current modification applies only to current rows.

A current modification to any column creates history rows in the temporal table.

Sequenced Temporal Modification

Sequenced temporal modifications are modifications that apply for an explicitly specified time period, the PA.

Rows qualify for the modification if their valid-time period, the PV, overlaps the PA specified for the modification. Sequenced modifications can be made to rows with periods of validity in the past, present, or future.

Nonsequenced Temporal Modification

Nonsequenced temporal modifications are modifications to a temporal table that treat the temporal columns as any other column, and impose no special temporal semantics. Nonsequenced modifications may explicitly mention the valid-time column, but do not automatically create history rows.