Deleting data in a system-period temporal table

Deleting a row from a system-period temporal table removes the row from the table and adds one or two rows to the associated history table. The DELETE statement copies the existing row into the associated history table before the row is deleted from the system-period temporal table. The rows in the history table are added with the appropriate system timestamps.

In the following example, the owner of policy B345 decides to cancel insurance coverage. The data for the customer was deleted on September 1, 2016 (2016-09-01) from the example table created in the Creating a system-period temporal table topic and updated in the Updating data in a system-period temporal table topic.

The following table contains the POLICY_INFO table data before the delete.

DELETE FROM policy_info WHERE policy_id = 'B345';

The deletion of policy B345 affects the system-period temporal table and its history table, causing the following things to occur:

1. The original row is copied to the history table. The database manager updates the SYS_END column value to the timestamp of the first data change operation in the transaction.
2. The row where the POLICY_ID column value is B345 is deleted from the system-period temporal table.