|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-22.214.171.1243114 FGFRD 2 2021-10-15-126.96.36.199572
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-188.8.131.524961 FGFRD 2 2021-10-15-184.108.40.206572
So, as the FLD2 got changed automatically the timestamp field got changed too.
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...)