Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: updating Timestamp field

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You could use a trigger for that.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can do that with a before update trigger.

    Andy

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Yes ... you r right

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2004
    Posts
    5
    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

  8. #8
    Join Date
    Aug 2004
    Posts
    330
    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.)

  9. #9
    Join Date
    Aug 2004
    Posts
    5

    Trigger Problem

    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
    Last edited by ricgre; 09-13-04 at 12:40.

  10. #10
    Join Date
    Aug 2004
    Posts
    330
    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 ~.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •