Transaction time and valid time are independent time dimensions that are used for different purposes. Bitemporal tables have both a transaction-time column and a valid-time column. Changes to bitemporal tables that happen automatically as a result of row modifications are independent for the transaction-time and valid-time dimensions. These dimensions must be considered separately when determining what will happen to a row as a result of a modification.
For example, if a row in a bitemporal table is deleted, the ending bound of the transaction-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 transaction-time period, is that the row has been deleted.
The valid-time period of the 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 historical, current, or future information. However, because the row was deleted, the row does not participate in further DML operations for the table, even though it remains in the table as a closed row in transaction time.
Because of the transaction-time column, all modifications to rows in bitemporal tables automatically create closed rows in the transaction time dimension, just as they do for transaction-time tables. This is in addition to rows that might be created to account for changes in the valid-time dimension.
For example, assume the terms of a contract 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 modified, as with an UPDATE statement. Because this is a temporal table, Teradata Database automatically inserts a copy of the row to store the new terms. The period of validity 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 transaction-time period of the new row reflects when the new row was created.
The original row is automatically modified to have the end of the period of validity reflect the time of the change, when the old terms become no longer valid. This row becomes a history row in the valid-time dimension. Note that both rows remain open rows in the transaction time dimension, and as such, 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 transaction-time dimension, another copy is made of the original row, reflecting the original period of validity, but the row is closed in the transaction time dimension at the time the terms changed. No further changes can be made to this row, because it is closed in transaction time. It provides a permanent “before” snapshot of the row as it existed in the database before it was changed.
Note that the actions which are performed automatically by Teradata Database on the row include independent actions that result from the table having both a valid-time column and a transaction-time column.