17.10 - Example: Increase Order occurs before Reduce Discount - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

Assume the individual transactions have the following characteristics:

Times Increase Order Transaction Reduce Discount Transaction
Begin Time 2009-01-02 2009-01-09
Modification Time 2009-01-07 2009-01-10
End Time 2009-01-08 2009-01-11
  • Begin Time is the value of TEMPORAL_DATE for the example transactions. This time is used both to qualify rows for participation in the transaction, and to timestamp the modified rows.
  • Modification Time is the time when the modification is made by the transaction.
  • End Time is when the transaction is committed and completed.

Notice that Increase Order ends before Reduce Discount begins.

The following shows the states of the Orders and Parts tables after transactions have completed.

Orders table state after the Increase Order transaction:

Order_ID Part_ID Quantity Order_Validity
O1 P1 60 (2008-01-01, 2009-01-02)
O1 P1 120 (2009-01-02, 2011-01-01)

In the original state of the tables, the P1 rows qualified for the CURRENT transaction because their valid times overlapped current time, value of TEMPORAL_DATE at the time when the transaction started, shown as Begin Time in the table of transaction characteristics. The period (2008-01-01, 2011-01-01) overlaps 2009-01-02.

Additionally, the P1 row in the Orders table was modified, because the P1 row in the Parts table fulfilled the transaction WHERE test (discount >= 10).

The modification of the P1 row in the Orders table leaves a history row showing the state of the row prior to the modification. Notice the end time of the first row and the beginning time of the second row have both been timestamped with TEMPORAL_DATE at the time of the modification.

Parts Table state after the Reduce Discount transaction:

Part_ID Supplier_ID Price Discount Part_Validity
P1 S1 $10 10% (2008-01-01, 2011-01-01)

The P1 row was not changed, because the current time at the time of the Reduce Discount transaction was 2009-01-09. Using this current time, only the second row in the Orders table qualifies for the transaction, however that row fails the WHERE test (quantity < 100), due to the changes made by the preceding Increase Order transaction.