ibm midrange - DB2 iSeries Trigger for added & modified timestamp and user fields -

perhaps triggers not needed added/modifed dates, maybe there appropriate functions set values, in case:

my question following fields,

created (timestamp) updated (timestamp) createdby (string, hold created user name) updatedby (string, hold updated user name) 

how alter table such on creation , update these fields hold appropriate values?

edit: need know how set updatedby , updated timestamp fields each time record accessed.

create following table reference:

create table test(                       id integer generated 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 auto incrementing primary key (id), createdby field set on insert, created timestamp set on insert need triggers make last 2 work expected (there new feature set updated on update without use of triggers feature not seem allow null value show record has never been updated not work me).

insert test (content) values ('first thing'),   ('second thing')       

to see default values created , createdby have been set:

select * test 

to add update triggers:

create trigger mytrigger                      no cascade before update on test    referencing new post                   each row mode db2row                    set                                          post.updated = current timestamp,           post.updatedby = user   

to see if above working, lets update values in "content":

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

to see new default values

select * co05arh/test 

we should see 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>