You will finally remove the row from the current table in order to demonstrate how the DELETE statement affects temporal tables. Here is the code for this action:
DELETE FROM dbo.Product WHERE ProductId = 1;-- on 15th December
The state of the current table is as follows after the preceding statement's execution:
ProductId |
ProductName |
Price |
ValidFrom |
ValidTo |
|
|
|
|
|
The state of the history table is as follows after the preceding statement's execution:
ProductId |
ProductName |
Price |
ValidFrom |
ValidTo |
1 |
Fog |
150 |
12.11.2017 |
28.11.2017 |
1 |
Fog |
200 |
28.11.2017 |
29.11.2017 |
1 |
Fog |
180 |
29.11.2017 |
30.11.2017 |
1 |
Fog |
180 |
30.11.2017 |
15.12.2017 |
As you expected, there are no rows the current table, but another row has been added to the history table. After executing the DELETE statement against a single row in a temporal table:
- Current table: The row has been removed
- History table: The row from the current table before deleting is copied to the history table, and only the period end date column is set to the system date
Use this opportunity to clean up the temporal table created in this section:
ALTER TABLE dbo.Product SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Product DROP PERIOD FOR SYSTEM_TIME;
DROP TABLE IF EXISTS dbo.Product;
DROP TABLE IF EXISTS dbo.ProductHistory;