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?
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.
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
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.
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)
UPDATE CSSDATA.TBL_RACKSLOT SET FLDUPDATED = CURRENT TIMESTAMP;
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.
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 ~.