ROW CHANGE expression
A ROW CHANGE expression returns a token or a timestamp that represents the last change to a row.
- Specifies that a BIGINT value representing a relative point in the modification sequence of a row is to be returned. If the row has not been changed, the result is a token that represents when the initial value was inserted. The result can be null. ROW CHANGE TOKEN is not deterministic.
- Specifies that a TIMESTAMP value representing the last time that a row was changed is to be returned. If the row has not been changed, the result is the time that the initial value was inserted. The result can be null. ROW CHANGE TIMESTAMP is not deterministic.
- FOR table-designator
- Identifies the table in which the expression is referenced. The table-designator must
uniquely identify a base table, view, or nested table expression
(SQLSTATE 42867). If table-designator identifies
a view or a nested table expression, the ROW CHANGE expression returns
the TOKEN or TIMESTAMP of the base table of the view or nested table
expression. The view or nested table expression must contain only
one base table in its outer subselect (SQLSTATE 42867). If the table-designator is
a view or nested table expression, it must be deletable (SQLSTATE
42703). For information about deletable views, see the
CREATE VIEW. The table designator of a ROW CHANGE TIMESTAMP expression must resolve to a base table that contains a row change timestamp column (SQLSTATE 55068).
- ROW CHANGE TOKEN and ROW CHANGE TIMESTAMP are not valid expressions for a column-organized table (SQLSTATE 42703).
- Return a timestamp value that corresponds to the most recent change
to each row from the EMPLOYEE table for employees in department 20.
Assume that the EMPLOYEE table has been altered to contain a column
defined with the ROW CHANGE TIMESTAMP clause.
SELECT ROW CHANGE TIMESTAMP FOR EMPLOYEE FROM EMPLOYEE WHERE DEPTNO = 20
- Return a BIGINT value that represents a relative point in the
modification sequence of the row corresponding to employee number
3500. Also return the RID_BIT scalar function value that is to be
used in an optimistic locking DELETE scenario. Specify the WITH UR
option to get the latest ROW CHANGE TOKEN value.
The preceding statement succeeds whether or not there is a row change timestamp column in the EMPLOYEE table. The following searched DELETE statement deletes the row specified by the ROW CHANGE TOKEN and RID_BIT values from the preceding SELECT statement, assuming the two parameter marker values are set to the values obtained from the preceding statement.
SELECT ROW CHANGE TOKEN FOR EMPLOYEE, RID_BIT (EMPLOYEE) FROM EMPLOYEE WHERE EMPNO = '3500' WITH UR
DELETE FROM EMPLOYEE E WHERE RID_BIT (E) = ? AND ROW CHANGE TOKEN FOR E = ?