Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    5

    Unanswered: Please help! Urgent! Can't create trigger

    CREATE TRIGGER "SYSTEM".CHECKDATETIME NO CASCADE BEFORE
    INSERT
    ON "SYSTEM".NON_MEMBER_BOOKING
    REFERENCING NEW AS N FOR EACH ROW
    MODE DB2SQL
    WHEN (
    (SELECT * FROM "SYSTEM".NON_MEMBER_BOOKING AS NON1 WHERE
    NON1.BOOKDATE = N.BOOKDATE AND SELECT * FROM "SYSTEM".NON_MEMBER_BOOKING AS NON2 WHERE
    NON2.BOOKTIME = N.BOOKTIME) OR
    (SELECT * FROM "SYSTEM".MEMBER_BOOKING AS MEM WHERE MEM.BOOKDATE = N.BOOKDATE AND SELECT * FROM "SYSTEM".MEMBER_BOOKING AS MEM2 WHERE MEM2.BOOKTIME = N.BOOKTIME)
    )
    BEGIN ATOMIC
    SIGNAL SQLSTATE '70001' ('THIS DATE AND TIME IS ALREADY BEEN BOOKED BY OTHERS!');
    END

    I cannot compile, I have no idea why, I am newbie in db2...

    It show me this error "SQLSTATE=42601"

    Please help, I am really urgent on it...
    Your help will be appreciated!

    THANK YOU SO MUCH!!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You made some apparent syntax errors.
    For example:
    1)
    WHEN (
    (SELECT * ... ) OR
    (SELECT * ... )
    )

    2)
    WHERE NON1.BOOKDATE = N.BOOKDATE AND SELECT * ...

    One way to eliminate the syntax errors may be:
    WHEN (
    EXISTS (SELECT * FROM SYSTEM.NON_MEMBER_BOOKING AS NON WHERE
    NON.BOOKDATE = N.BOOKDATE AND
    NON.BOOKTIME = N.BOOKTIME) OR
    EXISTS (SELECT * FROM SYSTEM.MEMBER_BOOKING AS MEM WHERE MEM.BOOKDATE = N.BOOKDATE AND MEM.BOOKTIME = N.BOOKTIME)
    )

    But, I don't know this will satisfy your requirement.

  3. #3
    Join Date
    Sep 2009
    Posts
    5
    CREATE TRIGGER KEN.CHECKDATETIME NO CASCADE BEFORE
    INSERT
    ON KEN.NON_MEMBER_BOOKING
    REFERENCING
    NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    WHEN (
    EXISTS (SELECT * FROM KEN.NON_MEMBER_BOOKING AS NON WHERE NON.BOOKDATE = N.BOOKDATE AND NON.BOOKTIME = N.BOOKTIME) OR
    EXISTS (SELECT * FROM KEN.MEMBER_BOOKING AS MEM WHERE MEM.BOOKDATE = N.BOOKDATE AND MEM.BOOKTIME = N.BOOKTIME)
    )
    BEGIN ATOMIC
    SIGNAL SQLSTATE '70001' ('THIS DATE AND TIME IS ALREADY BEEN BOOKED BY OTHERS!');
    END


    Still cannot ..i dono y.. any one please help?

  4. #4
    Join Date
    Sep 2009
    Posts
    5
    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 "END-OF-STATEMENT" was found following "BOOKED
    BY OTHERS!')". Expected tokens may include: "<delim_semicolon>". LINE
    NUMBER=13. SQLSTATE=42601

    END
    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 "END-OF-STATEMENT" was found following "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be processed.

    User response:

    Examine and correct the statement in the area of the specified token.

    sqlcode: -104

    sqlstate: 42601


    It show me this error

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Change statement termination character to other than semicolon.

    OR

    Remove BEGIN ATOMIC/END.
    Last edited by tonkuma; 09-08-09 at 09:22.

  6. #6
    Join Date
    Sep 2009
    Posts
    5
    U really my king!... I hug you!
    THank you very very much... is working!

Posting Permissions

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