Updating data in a system-period temporal table results in rows that are added to its associated history table.
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
A123 | 12000 | 2010-01-31- 22.31.33. 495925000000 |
9999-12-30- 00.00.00. 000000000000 |
2010-01-31- 22.31.33. 495925000000 |
B345 | 18000 | 2010-01-31- 22.31.33. 495925000000 |
9999-12-30- 00.00.00. 000000000000 |
2010-01-31- 22.31.33. 495925000000 |
C567 | 20000 | 2010-01-31- 22.31.33. 495925000000 |
9999-12-30- 00.00.00. 000000000000 |
2010-01-31- 22.31.33. 495925000000 |
UPDATE policy_info
SET coverage = 25000
WHERE policy_id = 'C567';
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
A123 | 12000 | 2010-01-31- 22.31.33. 495925000000 |
9999-12-30- 00.00.00. 000000000000 |
2010-01-31- 22.31.33. 495925000000 |
B345 | 18000 | 2010-01-31- 22.31.33. 495925000000 |
9999-12-30- 00.00.00. 000000000000 |
2010-01-31- 22.31.33. 495925000000 |
C567 | 25000 | 2011-02-28- 09.10.12. 649592000000 |
9999-12-30- 00.00.00. 000000000000 |
2011-02-28- 09.10.12. 649592000000 |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
C567 | 20000 | 2010-01-31- 22.31.33. 495925000000 |
2011-02-28- 09.10.12. 649592000000 |
2010-01-31- 22.31.33. 495925000000 |
UPDATE (SELECT * FROM policy_info
FOR SYSTEM_TIME AS OF '2010-01-31-22.31.33.495925000000')
SET coverage = coverage + 1000;
This update returns
an error because it implicitly attempts to update history rows. The
SELECT explicitly queries the policy_info table and
implicitly queries its associated history table (hist_policy_info).
The C567 row in hist_policy_info would be returned
by the SELECT, but rows in a history table that were accessed implicitly
cannot be updated.When you insert or update multiple rows within a single SQL transaction, the values for the row-begin column are the same for all the impacted rows. That value comes from a reading of the system clock at the moment the first data change statement in the transaction is executed. For example, all times associated with transaction ABC will have a time of T1.
Transaction ABC | Transaction XYZ |
---|---|
|
|
|
|
|
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
S777 | 7000 | T1 | max | T1 |
T888 | 8000 | T2 | max | T2 |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
S777 | 7000 | T1 | max | T1 |
X999 | 8000 | T1 | max | T1 |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
T888 | 8000 | T2 | T1 | T2 |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
S777 | 7000 | T1 | max | T1 |
X999 | 8000 | T2+delta | max | T1 |
Y555 | 9000 | T1 | max | T1 |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
T888 | 8000 | T2 | T2+delta | T2 |
Transaction ABC |
---|
T6: UPDATE policy_info SET policy_id = 'R111' WHERE policy_id = 'X999'; |
T7: COMMIT; |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
S777 | 7000 | T1 | max | T1 |
R111 | 8000 | T1 | max | T1 |
Y555 | 9000 | T1 | max | T1 |
policy_id | coverage | sys_start | sys_end | ts_id |
---|---|---|---|---|
T888 | 8000 | T2 | T2+delta | T2 |
CREATE VIEW viewA AS SELECT * FROM policy_info;
UPDATE viewA SET col2 = col2 + 1000;
A view that references
a system-period temporal table or a bitemporal table with a view definition
containing a FOR SYSTEM_TIME clause can be made updatable by defining
an INSTEAD OF trigger. The following example updates the regular_table table.CREATE VIEW viewB AS SELECT * FROM policy_info;
FOR SYSTEM_TIME BETWEEN
TIMESTAMP '2010-01-01 10:00:00' AND TIMESTAMP '2011-01-01 10:00:00';
CREATE TRIGGER update INSTEAD OF UPDATE ON viewB
REFERENCING NEW AS n FOR EACH ROW
UPDATE regular_table SET col1 = n.id;
UPDATE viewB set id = 500;