title: DB2/400 - Auto generated timestamp on change (error)
body: <p>I'm trying to create a table with a timestamp column that autogenerates with 'current timestamp' on each update of the record. I'm on DB2/400 (version V5R3) using ODBC driver. </p>
  
  <p>That's the query:</p>
  
  <pre><code>CREATE TABLE random_table_name ( 
  ID                   integer not null generated always as identity,
  USERS_ID             varchar (30),
  DETAILS              varchar (1000),
  TMSTML_CREATE        timestamp  default current timestamp ,
  TMSTMP_UPDATE        timestamp not null generated always for each row on update as row change timestamp,
  PRIMARY KEY ( ID )
  </code></pre>
  
  <p>)</p>
  
  <p>I get this error (translated):</p>
  
  <pre><code>ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token EACH not valid. Valid tokens: BIT SBCS MIXED.
  </code></pre>
  
  <p>Without the 'TMSTMP_UPDATE' row the query works. How can i solve this?</p>
  
  <hr>
  
  <p><strong>EDIT:</strong> Ok, i understand that in my DB2 version, the only way is to use triggers, but today AS400 seems to be evil with me.
  I'm trying with this:</p>
  
  <pre><code>CREATE TRIGGER random_trigger_name
  AFTER UPDATE ON random_table_name
  REFERENCING NEW AS NEW_ROW
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
      SET NEW_ROW.TMSTM_UPDATE = CURRENT TIMESTAMP;
  END
  </code></pre>
  
  <p>Error (translated): </p>
  
  <pre><code>ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0312 - Variable TMSTM_UPDATE not defined or not available.
  </code></pre>
  
  <p>The column TMSTM_UPDATE exist and it's a normal timestamp.</p>
  
  <p><strong>EDIT 2:</strong> I've solved the trigger problem by replacing 'after' with 'before'. Now everything works as expected. Thank you all!</p>