Creating a row change timestamp column

Every time a row is added or changed in a table with a row change timestamp column, the row change timestamp column value is set to the timestamp corresponding to the time of the insert or update operation.

The data type of a row change timestamp column must be TIMESTAMP. You can define only one row change timestamp column in a table.

When you create a table, you can define a column in the table to be a row change timestamp column. For example, create a table ORDERS with columns called ORDERNO, SHIPPED_TO, ORDER_DATE, STATUS, and CHANGE_TS. Define CHANGE_TS as a row change timestamp column.

CREATE TABLE ORDERS
   (ORDERNO SMALLINT,
    SHIPPED_TO VARCHAR(36),
    ORDER_DATE DATE,
    STATUS CHAR(1),
    CHANGE_TS TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL)

When a row is inserted into the ORDERS table, the CHANGE_TS column for the row is set to the timestamp of the insert operation. Any time a row in ORDERS is updated, the CHANGE_TS column for the row is modified to reflect the timestamp of the update operation.

You can drop the row change timestamp attribute from a column:

ALTER TABLE ORDER         
  ALTER COLUMN CHANGE_TS        
  DROP ROW CHANGE TIMESTAMP

The column CHANGE_TS remains as a TIMESTAMP column in the table, but the system no longer automatically updates timestamp values for this column.