Because system time and valid time are independent dimensions any change to a bitemporal
table affects each dimension independently. The behavior of bitemporal tables can
be best understood by considering the consequences of changes to each dimension alone.
In the system-time dimension:
Any change to an existing row marks the original row as a closed row, with the end
of the system-time period timestamped to reflect the time of the change.
If the change was a deletion, no new rows are added to the table due to the system
time.
If the change updated the information in the row, Teradata Database automatically
inserts a new row into the table to reflect the new information, and the original
row is marked as closed, with system time ending at the time of the change.
Note: Because the system-time and valid-time dimensions are independent, valid-time temporal
changes to a row are treated the same as non-temporal changes, with respect to system
time. Such changes cause the original, unchanged row to be closed in system time and
stored in the table as a record of how the table existed before the change.
If a row is inserted into the table, Teradata Database timestamps the beginning bound
of the system-time period with the time of the insertion, and the ending bound is
marked as 9999-12-31 12:59:59:999999+00:00.
In a bitemporal table as in system-time tables, closed rows do not participate in
most SQL operations. because these rows are considered to have been deleted from the
table.
For the valid-time dimension, the results of changes depends on the relationship between
the PA of the change and the PV of the row:
If the PA overlaps a portion of the PV, Teradata Database automatically inserts a
new row into the table to reflect the new information. Depending on how the PA and
PV overlap, the PV of the new row will start or end at the same valid time as the
original row, and the other valid-time bound will be automatically timestamped with
the date or timestamp of the modification.
If the PA lies within the PV of the row, two new rows will be generated so that there
are rows in the table to reflect the changed row in addition to the original state
of the row both before and after the change.
If the PA completely overlays the PV of the row, the change is similar to a nontemporal
modification.
Note: Only rows that are still open in system time are subject to DELETE and UPDATE modifications
in bitemporal tables.