Tables with transaction time automatically store in the table a snapshot copy of any row that is modified or deleted. This characteristic of these tables can be used to recover a prior state of the table using only SQL. This can be useful to quickly recover from a localized problem, such as if a table or set of tables have become corrupted by a user error, and need to be restored to a consistent state.
Assume a table that has transaction time needs to be restored to the state it was in at a point (time X) prior to the current time. Rows in the table can be classified based on whether they are open or closed, and on their transaction-time column period relation to time X:
| Row | Row State | BEGIN(TT) Beginning Transaction Time |
END(TT) End Transaction Time |
|---|---|---|---|
| 1 | Open | Prior to time X | UNTIL_CLOSED |
| 2 | Open | Equal to time X | UNTIL_CLOSED |
| 3 | Open | After time X | UNTIL_CLOSED |
| 4 | Closed | Prior to time X | Prior to time X |
| 5 | Closed | Prior to time X | Equal to time X |
| 6 | Closed | Prior to time X | After time X |
| 7 | Closed | Equal to time X | After time X |
| 8 | Closed | After time X | After time X |
These rows are represented graphically in the following.
