Rows that are closed in transaction time provide a history of all modifications and deletions on tables that have a transaction-time column. The automatic history that tables with transaction time provide can be used for regulatory compliance auditing, so these rows are generally inaccessible to DML modifications. Because NONTEMPORAL DML statements can modify closed rows, the special NONTEMPORAL privilege is required. For more information on the NONTEMPORAL privilege, see NONTEMPORAL Privilege.
A nontemporal update is similar to a conventional update, but the transaction-time column is treated as any other column in the table. The transaction-time column values can be explicitly specified in the SET clause of the statement. A nontemporal update can be issued to update closed or open rows.
The qualification condition in the UPDATE statement considers both the open and closed rows in the table. Additionally, for a table with valid time, both valid and no-longer-valid rows participate in the update. If the statement references multiple temporal tables, a nonsequenced form of join is performed on the tables.
If the transaction-time column is modified, the following rules apply:
- The beginning bound must not be greater than the system time at the time of the update.
- The ending bound must be UNTIL_CLOSED or less than or equal to the system time at the time of the update.