RE: Update timestamp automatically when row changes

The only way I see Chuck's proposal work for UPDATEs is if you explicitly
specify a DEFAULT keyword on your INSERTs and UPDATEs. For example:

CREATE TABLE QTEMP/T1
(F1 INT NOT NULL WITH DEFAULT,
F2 TIMESTAMP NOT NULL WITH DEFAULT CURRENT_TIMESTAMP)

INSERT INTO QTEMP/T1 VALUES(33,DEFAULT)
UPDATE QTEMP/T1 SET (F1,F2) = (44,DEFAULT) WHERE F1 = 33

You could get away without specifying a DEFAULT keyword on the INSERT if you
specify the column list, i.e.:

INSERT INTO QTEMP/T1 (F1) VALUES(33)

but the same will not work for UPDATEs.

Other than that... trigger is the answer.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: Update timestamp automatically when row changes

If I understand what you are suggesting that is what I'm doing now. The
timestamp field is defined with default CURRENT_TIMESTAMP. I don't believe
that updates on row changes however.

I must have missed the PF default values thread. I'll take a look at it and
see if it will work.


-----Original Message-----
Subject: Re: Update timestamp automatically when row changes

Why not just use SQL CURRENT TIMESTAMP in the SQL DML. Not using SQL DML
for the DDL objects, or just prefer to avoid the CURRENT TIMESTAMP? Update
& Insert via a VIEW which excludes the TIMESTAMP column; define the
TIMESTAMP column with DEFAULT CURRENT TIMESTAMP.
If non-SQL RPG, presumably perform according to what is described in the
very recent thread on RPG400-L subject: PF Default values

Regards, Chuck