Now, Get RPG Codes in all three formats only on this website on every RPGLE related programming article.

Fixed, /Free and Fully Free RPG


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

Auto update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table
Auto-update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table, timestamp column in ddl, row change timestamp in ddl table, ddl as400, db2, db2 for i sql, sql, database modernixation
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

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain