17.00 - Bitemporal Table Modifications - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

The system-time and valid-time dimensions of bitemporal tables are independent of each other, and are affected just as they are in system-time and valid-time tables, with one important difference. In a bitemporal table only rows that are open in the system-time dimension (those that have a system-time period ending bound of UNTIL_CLOSED) participate in modifications. After a row is closed in system time, it is no longer active in the database.

Because of the system-time dimension, all modifications to rows in bitemporal tables automatically create closed rows in the system-time dimension. This is in addition to rows that might be created to account for changes in the valid-time dimension.

For example, if a row in a bitemporal table is deleted, the ending bound of the system-time period is automatically changed to reflect the time of the deletion, and the row is closed to further modifications. The database reality, reflected by the modified ending bound of the system-time period, is that the row has been deleted.

The valid-time period of the closed row remains unchanged. Because the deletion does not affect the ending bound of the valid-time period, the row information retains its character in the valid-time dimension as it existed at the time of the deletion.

The result of updates to rows in bitemporal tables are more complex, but are completely consistent with the idea of the system-time and valid-time dimensions acting independently.

For example, assume the contract terms are stored in a row of a bitemporal table. If the terms are changed during the period when the contract is valid, the row must be updated. Because this is a temporal table, Teradata Database automatically inserts a copy of the row to store the new terms. The PV of the new row is automatically set to begin at the time of the change, and end at the original end date of the contract. The beginning bound of the system-time period of the new row reflects when the new row was created, and the end of the system-time period is indefinite, set as UNTIL_CLOSED, which it will remain until the newly added row is deleted or modified.

The original row is automatically modified to have the end of the PV reflect the time of the change, when the old contract terms became obsolete. This row becomes a history row in the valid-time dimension. Note that both rows remain open rows in the system- time dimension, and as such, both are still available to all types of DML queries and modifications. These changes are purely a result of the valid-time dimension of the table.

Because the table also includes a system-time dimension, however, a copy is made of the original row, reflecting the original PV, but this row is now closed in the system-time dimension as of the time the contract terms changed. No further changes can be made to this row, because it is closed in system time. It provides a permanent “before” snapshot of the original row as it existed in the database before it was changed.

Note that the temporal operations performed on the row automatically by Teradata Database include independent actions that result from the table having both a system-time dimension and a valid-time dimension.

For more information about modifying ANSI bitemporal tables, see Modifying Rows in ANSI Bitemporal Tables.