Sample Header Ad - 728x90

How to update rows in GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP without changing timestamp

0 votes
1 answer
586 views
On Db2 11.5 on Linux I have (simplified example) the following table: CREATE TABLE ADMIN.TAB1 ( ID INTEGER NOT NULL, COL_TEXT CHAR(10), DATE_TIME TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN ); Some data: INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (1, 'A'); INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (2, 'A'); INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (3, 'A'); Display the data: SELECT ID, COL_TEXT, DATE_TIME FROM ADMIN.TAB1; Now I am required to update table with: UPDATE ADMIN.TAB1 SET COL_TEXT = 'B' WHERE ID = 1; But REQUIREMENT is only column COL_TEXT should be changed. According to "generated always" definition also DATE_TIME column is going to be changed, but it is now one time business requirement that this column should NOT! change, because some application actions are scheduled to executed according to DATE_TIME field and if new data is found in this DATE_TIME column then some actions are performed in some other tables. --- I was thinking: 1. Dropping "generated always". 2. Do the update. 3. Add back from "generated by default" to "generated always" --> but can't figure it out how to do this? How to update table without affecting timestamp column?
Asked by folow (523 rep)
Jun 12, 2024, 11:29 AM
Last activity: Apr 22, 2025, 02:01 PM