DB2 trigger after update, update singl;e column n the updated row in the same table

Question

Using DB2 on cloud ( LUW ) I want to update a single column value in a row, after any of the other values in that row get updated.

The following AFTER INSERT works

CREATE TRIGGER CENTRAL_REPOSITORY.TR_DATABASE_OBJECT_INS_CDT
 AFTER INSERT ON CENTRAL_REPOSITORY.DATABASE_OBJECT
 REFERENCING NEW AS N_ROW
 FOR EACH ROW
 UPDATE CENTRAL_REPOSITORY.DATABASE_OBJECT SET create_datetime = current_timestamp
    where id = N_ROW.id

The following AFTER UPDATE does not

CREATE TRIGGER CENTRAL_REPOSITORY.TR_DATABASE_OBJECT_UPD_UDT
 AFTER UPDATE ON CENTRAL_REPOSITORY.DATABASE_OBJECT
 REFERENCING NEW AS N_ROW
 FOR EACH ROW
 UPDATE CENTRAL_REPOSITORY.DATABASE_OBJECT SET update_datetime = current_timestamp
    where id = N_ROW.id;
 

The error I get after trying to run an insert on the table is

"An error occurred in a triggered SQL statement in trigger "CENTRAL_REPOSITO". Information returned for the error includes SQLCODE "-724", SQLSTATE "54038" and message tokens "CENTRAL_REPOSITORY.TR_DATABASE_OBJECT_UPD_".. SQLCODE=-723, SQLSTATE=09000, DRIVER=4.32.28"

Answer 1

You shouldn't be using an AFTER trigger,

Use a BEFORE trigger...

CREATE TRIGGER CENTRAL_REPOSITORY.TR_DATABASE_OBJECT_UPD_UDT
 BEFORE UPDATE ON CENTRAL_REPOSITORY.DATABASE_OBJECT
 REFERENCING NEW AS N_ROW
 FOR EACH ROW
  SET n_row.update_datetime = current_timestamp;

CREATE TRIGGER CENTRAL_REPOSITORY.TR_DATABASE_OBJECT_UPD_UDT
 BEFORE INSERT ON CENTRAL_REPOSITORY.DATABASE_OBJECT
 REFERENCING NEW AS N_ROW
 FOR EACH ROW
  SET n_row.update_datetime = current_timestamp;

EDIT You might consider defining the column a "row change timestamp", then you won't need to bother with the triggers at all.