Does DB2 have the ability to automatically update a timestamp field as does MY SQL or does this field need to be manipulated through a SQL statement. I have declared a timestamp field with default currenttimestamp and is initialized on inserts. Now I would like to have it automatically adjusted on updates. If this possible?

Ric

You could use a trigger for that.

You can do that with a before update trigger.

Andy

Andy, Are you sure it is a before update trigger ... If i remember right, you cannot do INSERT Operation in a before trigger ..

Cheers
Sathyaram

The way I read the original post was that every time that an update occurs to a row of the table, he wants a timestamp column set to the current timestamp (presumably the column name is "updated_on" or something similar).
This scenario calls for a before update trigger.

Andy

Yes ... you r right

Cheers
sathyaram

Thanks for the responses. I am new to DB2 and just doing some testing. Do I have actually specify a column?

CREATE TRIGGER TriggerName
BEFORE UPDATE ON TABLENAME
fOR EACH ROW MODE DB2SQL
UPDATE TABLENAME SET LASTUPDATED = curenttimestamp
END

Does this look look like the correct logic? I am using DB2 8.1 on V5r3 with the Navigator contol. I have heard that there is a Windows Control Center similiar to Navigator. Has anyone used this? I am primarily a Visual Studio developer who is migrating to database responsibilites. Any recommendations on resource db2 books or training classes.

Ric

Try setting the transition variable instead of updating the table:

CREATE TRIGGER TriggerName
NO CASCADE BEFORE UPDATE ON TABLENAME
REFERENCING OLD AS OLD
NEW AS NEW
fOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NEW.LASTUPDATED = curent timestamp;
END

(Don't forget to set your SQL terminator to something besides semicolon when creating the trigger.)

I have tried the trigger above and the one listed below and keep getting an error 4207. The library exists, I have created tables and populated them. I have full admin rights on all objects. Any ideas what the issue may still be?

CREATE TRIGGER RACK_UPDATE
BEFORE UPDATE OF FLDSLOT1 ON CSSDATA.TBL_RACKSLOT
FOR EACH ROW MODE DB2SQL
WHEN (NEW.FLDSLOT1 <> OLD.FLDSLOT1)
BEGIN ATOMIC
UPDATE CSSDATA.TBL_RACKSLOT SET FLDUPDATED = CURRENT TIMESTAMP;
END

Is the terminator correct? This is what I have used in MY SQL and MS SQL script.

When I insert the referencing statements I get a different error. Number 42899. I have tried removing the WHEN statement.

Ric

The TERMINATOR is what the SQL program uses to end an SQL statement. In general, the terminator is a semicolon. Since the trigger has a semicolon in the code, you have to tell the DBMS that you want to terminate the CREATE TRIGGER STATEMENT with something else, like $ or ~.