Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: Update trigger to update a time column ..

    Hi,

    I ve a test table defined with the following columns,

    Code:

    CREATE TABLE TEST(ID INT, TIME DATE);


    Here, whenever any of the table column is updated, the 'TIME' column should be updated with the date and time of update.

    Kindly let me know could i ve any simple triggers for this.

    Regards,
    Sn

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    trigger

    it is recommended not to use reserved words for column names : as time
    create a column with datatype timestamp to have date and time
    sample trigger :
    CREATE TRIGGER RECORD_HISTORY
    AFTER UPDATE OF QUOTE ON CURRENTQUOTE
    REFERENCING NEW AS NEWQUOTE
    FOR EACH ROW
    BEGIN ATOMIC
    update QUOTEHISTORY
    set quote=current timestamp;
    END
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    How about a before update trigger that overrides the transition variable.

    Code:
    CREATE TRIGGER TEST_BU
    AFTER UPDATE OF "TIME" ON TEST
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    SET NEW."TIME" = CURRENT TIMESTAMP;
    END

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    urquel,
    this trigger is working well, but you probably mean "before update"...
    The sintax "REFERENCING OLD AS OLD NEW AS NEW" is not needed it can be only: "REFERENCING NEW AS NEW".

    Code:
    CREATE TRIGGER TEST_BU
    BEFORE UPDATE OF "TIME" ON TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    SET NEW."TIME" = CURRENT TIMESTAMP;
    END@
    Save the file to file.sql and execute it by:
    db2 -td@ -f file.sql

    Hope this helps,
    Grofaty
    Last edited by grofaty; 12-13-06 at 06:04.

  5. #5
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the scripts guyz.. will let you know once i tested it.

    thanks again for the help

    Cheers
    Sn

  6. #6
    Join Date
    Apr 2005
    Posts
    127
    Hi,

    As per the advice, i ve tried the below trigger

    Code:
    CREATE TRIGGER TRIG_TEST
    AFTER UPDATE ON test
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    update test SET col3 = CURRENT TIMESTAMP;
    END
    @
    on my table,

    Code:
    create table test (col1 int, col2 int, col3 time stamp)
    Trigger compiled successfully, but it was not triggered when the following update call is made,

    Code:
    update test set col1 = 11 where col1 = 1
    the error thrown is ,
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0723N  An error occurred in a triggered SQL statement in trigger
    "ADMINISTRATOR.TRIG_TEST".  Information returned for the error includes
    SQLCODE "-724", SQLSTATE "54038" and message tokens
    "ADMINISTRATOR.TRIG_TEST".  SQLSTATE=09000
    It does not allow me change the Trigger as 'Before' Trigger and to include the 'NEW.' in the update statement defined in the trigger body.

    any help to resolve this issue will be appreciated.

    Thanks,
    Shefu

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    there is no "FOR" statement in your trigger and no NEW statement. IF you are referencing to NEW statement there can not be update cause in begin atomic statement!

    If you want to trigger get triggered before update of col1 and NOT before update of COL2 then try this out:
    Code:
    CREATE TRIGGER TRIG_TEST
    BEFORE UPDATE OF COL1 ON TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    SET NEW.COL3 = CURRENT TIMESTAMP;
    END@

    If you want to trigger get triggered before update of col1 and ALSO COL2 then try this out:
    Code:
    CREATE TRIGGER TRIG_TEST
    BEFORE UPDATE OF COL1, COL2 ON TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    SET NEW.COL3 = CURRENT TIMESTAMP;
    END@
    Hope this helps,
    Grofaty
    Last edited by grofaty; 12-13-06 at 06:16.

  8. #8
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the solution Grofaty, after including the 'OF' clause it works fine..


    cheers
    Sn

  9. #9
    Join Date
    Apr 2005
    Posts
    127
    HI,

    Am sorry that I am not able to compile the below suggested trigger,

    Code:
    CREATE TRIGGER TRIG_TEST
    AFTER UPDATE OF col1, col2 ON test
    REFERENCING NEW AS NEW
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    
        UPDATE test SET NEW.col3 = CURRENT TIMESTAMP;
    
    END
    @
    In the trigger body, when the NEW clause is used in the update statement, compilation error is thrown,
    if i remove the "NEW. " , the trigger is compiled and the output is wrong.

    DB2 version is 8.0
    OS : Windows2k

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    why do you use 'update statement' use just: SET NEW.COL3 = CURRENT TIMESTAMP;
    See my sample above.

    Quote Originally Posted by Shefu
    I am not able to compile the below suggested trigger
    What do you mean by "compile"?

    Please also provide any error message if something is not working.

    Quote Originally Posted by Shefu
    if i remove the "NEW. " , the trigger is compiled and the output is wrong.
    That is reasonable. Using "UPDATE test SET col3 = CURRENT TIMESTAMP" all the records in table are updated. The NEW statement only updates the records that are updated in original update statement (update test set col1 = 11 where col1 = 1) so only where col1=1. You must use NEW in trigger to update only affected records.

    Try copy/paste my suggested trigger statement.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 12-13-06 at 10:10.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Shefu,
    It does not look like you have looked at Grofaty's trigger closely. You need to use a "BEFORE UPDATE" trigger not an "AFTER UPDATE".

    Andy

  12. #12
    Join Date
    Apr 2005
    Posts
    127
    Hi,

    I guess i ve told about the errors i got while using BEFORE trigger and NEW clause in one of my previous posts, any way let me tell you what i did, i tried ur code directly for my test table, here goes the first trigger,

    Code:
    CREATE TRIGGER TRIG_TEST
    BEFORE UPDATE OF col1, col2 ON TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    MODE DB2SQL
    
    BEGIN ATOMIC
    
    	SET NEW.col3 = CURRENT TIMESTAMP;
    
    END
    @
    The following error thrown :

    Code:
    C:\DAdministrator>db2 -td@ -f grafTrig.db2
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "BEFORE" was found following "E TRIGGER
    TRIG_TEST ".  Expected tokens may include:  "AFTER".  LINE NUMBER=2.
    SQLSTATE=42601
    Hence changed the Before to AFTER and the trigger looked like,

    Code:
    CREATE TRIGGER TRIG_TEST
    AFTER UPDATE OF col1, col2 ON TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    MODE DB2SQL
    
    BEGIN ATOMIC
    
    	SET NEW.col3 = CURRENT TIMESTAMP;
    
    END
    @
    Again an error bursted out, as follows :

    Code:
    C:\DAdministrator>db2 -td@ -f grafTrig.db2
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0797N  The trigger "ADMINISTRATOR.TRIG_TEST" is defined with an unsupported
    triggered SQL statement.  LINE NUMBER=12.  SQLSTATE=42987
    again in the trigger, changed the SET statement to UPDATE statement :

    Code:
    CREATE TRIGGER TRIG_TEST
    AFTER UPDATE OF col1, col2 ON TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    MODE DB2SQL
    
    BEGIN ATOMIC 
    
    	UPDATE TEST SET NEW.col3 = CURRENT TIMESTAMP;
    
    END
    @
    For the above change i got the below error,

    Code:
    C:\DAdministrator>db2 -td@ -f  grafTrig.db2
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0206N  "COL3" is not valid in the context where it is used.  LINE NUMBER=9.
    SQLSTATE=42703

    Kindly let me know whats going on wrong here..

    Regards,
    Sn

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your syntax for the BEFORE UPDATE trigger is wrong. You should check the SQL Reference manual for the correct syntax. It should look like this:


    CREATE TRIGGER TRIG_TEST
    NO CASCADE BEFORE UPDATE OF col1, col2 ON TEST
    REFERENCING NEW AS NEW
    FOR EACH ROW
    MODE DB2SQL

    BEGIN ATOMIC

    SET NEW.col3 = CURRENT TIMESTAMP;

    END
    @

    Andy

  14. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I use this trigger to keep information on who was the latest one to alter a record and when it was done. You could also add an extra column nr_maint to keep track of the number of times the record was altered.

    By not explicitely specifying the columns, any UPDATE on the record will fire the trigger.

    Code:
    CREATE TRIGGER mytrigger
    NO CASCADE BEFORE UPDATE ON table1
    REFERENCING
    OLD AS pre
    NEW AS post
    FOR EACH ROW MODE DB2SQL
    SET
    	post.da_maint = CURRENT TIMESTAMP,
    	post.us_maint = USER,
    	post.nr_maint = pre.nr_maint + 1;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    just to update two years old thread.

    To get current timestamp column automatically refreshed after each insert/update there is new feature in v9.5.

    Sample:
    CREATE TABLE ADMIN.PERSON
    (
    PERSONID INT NOT NULL PRIMARY KEY,
    NAME CHAR(20),
    LAST_TIMESTAMP TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
    ) ;

    More info: DB2utor: Automate Row Change Timestamp Used for Optimistic Locking

    Hope this helps someone out there, because it is really excellent functionality.
    Regards,
    Grofaty

Posting Permissions

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