Auto-update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table

Auto-update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table

We all have faced this case in our career when we have auto-update a file field when there is any updating that happens in any of the rows. It can be an audit column, design requirement, or for any other purpose. 

In this article, we create a timestamp column using DDL which will get updated automatically when any of the file fields are altered, its working is similar to triggers.

Here Row Change Timestamp will be used in the DDL script to create the auto-update field.

ROW CHANGE TIMESTAMP

FieldName TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL

Auto-update timestamp field can't be NULL that is why it is important to use NOT NULL for the field.
Also, the use of ROW CHANGE TIMESTAMP is also compulsory as without this field will be the normal timestamp field and not the auto-update field.

And. Now the DDL script with auto-update field

DDL Script (Example)

CREATE TABLE DDL9 (                                                       
  FLD1 CHAR(10) NOT NULL WITH DEFAULT UNIQUE,                             
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT,                                  
  FLD3 TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL);

In the above DDL Table FLD3 is the auto-update timestamp table.

Data In the table

FLD1        FLD2  FLD3                        
d             3   2021-10-15-20.44.14.633114  
FGFRD         2   2021-10-15-20.43.31.246572  

Update the table with the below query.

UPDATE TableName SET FLD2 = 10 WHERE FLD1 ='d'

And, now the data in the table is

FLD1        FLD2  FLD3                       
d            10   2021-11-24-20.18.45.364961 
FGFRD         2   2021-10-15-20.43.31.246572 

So, as the FLD2 got changed automatically the timestamp field got changed too.

Related Post

Read also :

• CRTPF and Create Table in IBM i
• Primary Key in DDS and DDL tables
• Data Validation in DDS and DDL tables
• Column Heading in DDS Files and DDL tables
• Column Text in DDS and DDL tables
• Attributes of DDL table
• ADD Constraint in DDL tables
• Create Index and LF keyed in IBM i
• Create View and LF Non-Keyed in IBM i
• DDS to DDL Modernization : Level Check Error
• Identity column in DDL table
• QSQGNDDL API - IBM to convert DDS file into DDL table
• QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...)