row-change-expression
|--ROW CHANGE--+-TOKEN-----+--FOR--table-designator-------------|
'-TIMESTAMP-'
A ROW CHANGE expression returns a token or a timestamp
that represents the last change to a row.
- TOKEN
- 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.
- TIMESTAMP
- 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 "Notes" section of "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).
Notes
- The values returned by the ROW CHANGE TOKEN expression can be
used with the RID_BIT scalar function by applications that use optimistic
locking.
Examples
- 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.
SELECT ROW CHANGE TOKEN FOR EMPLOYEE, RID_BIT (EMPLOYEE)
FROM EMPLOYEE WHERE EMPNO = '3500' WITH UR
The
above 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 above SELECT statement, assuming the two parameter marker values
are set to the values obtained from the above statement. DELETE FROM EMPLOYEE E
WHERE RID_BIT (E) = ? AND ROW CHANGE TOKEN FOR E = ?