Determining when a row was changed

Suppose that you issue the following statements to create, populate, and alter a table:

CREATE TABLE T1 (C1 INTEGER NOT NULL); 
INSERT INTO T1 VALUES (1); 
ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS 
  FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; 
SELECT T1.C2 FROM T1 WHERE T1.C1 = 1; 

FL 503 Because the ROW CHANGE TIMESTAMP column was added after the data was inserted, the following statement returns the time that the ROW CHANGE TIMESTAMP column was added or page was last modified:

SELECT T1.C2 FROM T1 WHERE T1.C1 = 1;

Assume that you then issue the following statement:

INSERT INTO T1(C1) VALUES (2);

Assume that this row is added to the same page as the first row. The following statement returns the time that value "2" was inserted into the table:

SELECT T1.C2 FROM T1 WHERE T1.C1 = 2;

FL 503 Note that the row with value "1" still does not have a value for the ROW CHANGE TIMESTAMP column.

If the default value is the time that the ROW CHANGE TIMESTAMP column was added, then the following statement returns the time that the column was added. If the default value is the time that the page was last modified, then the following statement returns the time that value "2" was inserted.

SELECT T1.C2 FROM T1 WHERE T1.C1 = 1;