Transaction time is the time period during which a fact, represented by all the information in a row, is or was known to be in effect in the database. It models the database reality, recording when rows have been added, modified, and changed in the database. Transaction-time periods are stored in a transaction-time column:
- The beginning of the transaction-time period is the time when the database became aware of a row, when the row was first recorded in the database. This is when the row was added to a table.
- The end of a transaction time period reflects when the fact was superseded by an update to the row, or when the row was deleted from the database. Rows containing information that is currently in effect have transaction-time periods with indefinite ending bounds, represented as UNTIL_CLOSED.
Transaction-time columns are defined by specifying AS TRANSACTIONTIME in the column definition, and have a period data type with an element type of TIMESTAMP(6) WITH TIME ZONE. You cannot normally set or modify the value of a transaction-time column. Vantage maintains these values automatically. (However, for database maintenance and troubleshooting, closed rows can be modified or deleted by administrators who have been granted the NONTEMPORAL privilege.)
- When a row is “deleted” from the table, the row is not physically deleted from the table. Instead, the transaction-time column is automatically modified to have an ending bound that specifies the time of the deletion, which marks the row as “closed,” and no longer available.
- When a row is “modified” in the table, the original row with the original values is marked as closed, and a copy of the row having the modified values is automatically inserted into the table.
The resulting snapshots of deleted and modified rows, which are retained in the table, provide a complete internal history of the table. Any prior state of a table having a transaction-time column can be reproduced. However, closed rows are unavailable to most DML modifications or deletions.
Add transaction-time columns to tables for which historical changes should be automatically tracked and maintained in the database. For example, transaction-time tables can be used for information that must retain a history of all changes, such as for tables used for regulatory compliance reporting.