DB2 iSeries Trigger for added & modified timestamp and user fields

Question

Perhaps triggers are not needed for added/modifed dates, maybe there are appropriate functions to set their values, in any case:

My question is with the following fields,

created (timestamp)
updated (timestamp)
createdBy (string, to hold the created by user name)
updatedBy (string, to hold the updated by user name)

how do I alter the table such that on creation and update these fields hold the appropriate values?

Edit: I now just need to know how to set the updatedBy and updated timestamp fields each time the record is accessed.

Answer 1

Create the following table for a reference:

create table test(                    
  id integer generated always as identity,    
  content char(60),                           
  createdBy char(30) default user,            
  created timestamp default current timestamp,
  updatedBy char(30),                         
  updated timestamp default null,             
  primary key(id)                             
)   

This table has an auto incrementing primary key (id), a createdBy field which is set on insert, a created timestamp which is set on insert now we just need triggers to make the last two work as expected (there is a new feature to set updated on update without the use of triggers but the feature does not seem to allow a null value to show the record has never been updated so that does not work for me).

insert into test (content) VALUES ('first thing'),
  ('second thing')      

To see that the default values for created and createdBy have been set:

select * from test

To add update triggers:

CREATE TRIGGER mytrigger                   
  NO CASCADE BEFORE UPDATE ON test 
  REFERENCING NEW AS post                  
FOR EACH ROW MODE DB2ROW                   
SET                                        
 post.updated = CURRENT TIMESTAMP,         
 post.updatedBy = USER  

To see if the above is working, lets update the values in "content":

update co05arh/test
  set content = 'first thing updated'
  where id = 1

To see the new default values

select * from co05arh/test

We should then see something like

ID  CONTENT                 CREATEDBY   CREATED                     UPDATEDBY   UPDATED
1   first thing updated     KEN         2011-04-29 16:16:17.942429  KEN         2011-04-29 16:16:28.649543
2   second thing            KEN         2011-04-29 16:16:18.01629   <null>      <null>