Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: SQL delimiter in triggers

    Hi All,
    I have a trigger statement like below from previous DBAs. Without changing delimiter explicitely using option -td, it does work fine everywhere. But I could not find ";--" in any IBM manuals. I still wonder why it works. Does anybody know where it is documented? Thanks.

    Code:
    CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
    REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
        SET NEW_ROW.my_column= current timestamp;--
    END;
    Last edited by DBA-Jr; 03-08-06 at 21:54.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    ; is the default delimiter.

    Anytime you have a -- in an SQL statement, the remaining characters on the line are treated as comments.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2005
    Posts
    108
    Marcus_A,
    Thank you for reply.
    That's what thought. So, the "create trigger" statement should end at the following line:
    Code:
    SET NEW_ROW.my_column= current timestamp;--
    Then, "END;" will be treated as another statement. You can not use the same delimiter for both triggered SQL (body) and the "create trigger" SQL. If you do, DB2 would parse till the first delimiter, and anything after each delimiter would be treated as another statement if it does not start with "--". That would cause syntax error. But in my case, it does NOT cause any error.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
    REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
        SET NEW_ROW.my_column= current timestamp;--
    END;
    Everything after the "--" until the end of the line (and including the "--") is considered comment and is not processed by DB2. So the above is the same as
    Code:
    CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
    REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL  -- this is my first comment
    BEGIN ATOMIC
        SET NEW_ROW.my_column= current timestamp;-- this is some comment 
    -- this is another line of comment
    END;
    and for DB2 all of the above is the same as:
    Code:
    CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
    REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
        SET NEW_ROW.my_column= current timestamp;
    END;
    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

  5. #5
    Join Date
    Mar 2005
    Posts
    108
    and for DB2 all of the above is the same as:

    Code:
    CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
    REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
        SET NEW_ROW.my_column= current timestamp;
    END;
    I would think so. The strange thing is that my original statement works, but your equivalent statement fails because DB2 can not find "END" to pair with "BEGIN ATOMIC". "END;" is truncated by the first ";".

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Right ! It's a common problem, each statement between BEGIN ATOMIC and
    END must be terminated with ';', and you have to change the terminator of the trigger (the terminator after END, the ; that couses the problem) into something else like '#'
    Code:
    CREATE TRIGGER my_trigger NO CASCADE BEFORE UPDATE ON my_table 
    REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
        SET NEW_ROW.my_column= current timestamp;
    END#
    You will have to alter Command Center's settings to another terminator character.
    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

  7. #7
    Join Date
    Mar 2005
    Posts
    108
    I still hope someone can explain why my original code works. It's not supposed to work according the rule. So my question is: why does it work w/o error.
    Last edited by DBA-Jr; 03-11-06 at 12:57.

  8. #8
    Join Date
    Mar 2005
    Posts
    108
    Any explanation is appreciated.

Posting Permissions

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