Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    17

    Unanswered: Trigger with multiple conditions

    Hi,
    I am trying to get a trigger with multiple conditions and case clause working, but I can't get the syntax right.

    Code:
    CREATE TRIGGER VALIDATION
    AFTER UPDATE OF VALUE ON DATA_VALUE
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW
     SELECT CASE 
      WHEN N.VALUE > 10
       THEN INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, 111, 1, 0, 0)
      WHEN N.VALUE > 20
       THEN INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, 111, 1, 0, 0)
    END;
    END
    I compared this with the example in

    DB2 Database for Linux, UNIX, and Windows

    but I just can't find where I am going wrong.

    Thanks for your help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There is no such syntax in CASE expression.

    CASE WHEN ... THEN INSERT ...

    And, you made simple syntax errors.
    For example, no matching BEGIN(or other keyword) for a END.
    No FROM keyword for SELECT.
    Incorrect use(or no use) of semicolons.
    Last edited by tonkuma; 12-28-09 at 14:51.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What example did you compared?

    For example, seeing
    Example 2:
    Code:
       CREATE TRIGGER REORDER
         AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
         REFERENCING NEW AS N
         FOR EACH ROW
         WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
         BEGIN ATOMIC
         VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO));
         END
    you may want to write...
    Code:
    .....
    FOR EACH ROW
      WHEN (N.VALUE > 10)
       INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, 111, 1, 0, 0)
      WHEN (N.VALUE > 20)
       INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, 111, 1, 0, 0)
    .....

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You probably want something more along the lines of:
    CREATE TRIGGER VALIDATION
    AFTER UPDATE OF VALUE ON DATA_VALUE
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW
    INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, case when n.value > 10 then ??? when > 20 then ??? when n.value > ? then .... else end, 1, 0, 0)
    ;
    END

    Dave Nance

  5. #5
    Join Date
    Jul 2009
    Posts
    17
    Thanks a lot for your help. Something is still wrong though. I tried both solutions, like this

    Code:
    CREATE TRIGGER VALIDATION
    AFTER UPDATE OF VALUE ON DATA_VALUE
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW 
     WHEN (N.VALUE > 10)
       INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, 111, 1, 0, 0)
      WHEN (N.VALUE > 20)
       INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, 111, 1, 0, 0)
    
    ;
    END
    Which produces the error: An unexpected token "WHEN" was found following "E_ID, 111, 1, 0, 0)

    Trying the solution from dav1mo like this:
    Code:
    CREATE TRIGGER VALIDATION
    AFTER UPDATE OF VALUE ON DATA_VALUE
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW 
     INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, case when n.value > 10 then 111,1,0,0 when > 20 then 222,2,0,0 when n.value > 30 THEN 333,3,0,0 
     else end 444,4,0,0)
    ;
    END
    I get: An unexpected token "," was found following ".value > 10 then 111".
    Expected tokens may include: "END". LINE NUMBER=5. SQLSTATE=42601

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please study DB2 SQL basics.
    Get PDF manual "DB2 SQL Reference".
    For your trigger, study CASE expression.
    DB2 SQL Reference ---> Chapter 2. Language Elements ---> Expressions ---> CASE expression(s).

    My example is not complete code. Giving some idea for you.

    I already pointed out.....
    And, you made simple syntax errors.
    For example, no matching BEGIN(or other keyword) for a END.
    No FROM keyword for SELECT.
    Incorrect use(or no use) of semicolons.
    Last edited by tonkuma; 12-28-09 at 15:23.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use CASE as an expression. For that, you can only have a single value as result of the expression. So dav1mo's approach won't work for you.

    You cannot have multiple WHEN clauses is your trigger body. The syntax diagram in the manual tells you this clearly. (If there are question on how to read syntax diagrams, also look in the manual - this is very well explained.)

    So you have to resort to some other mechanisms. IF-statements come to mind, for example.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    IF-statements come to mind, for example.
    or CASE statement(not CASE expression).

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The case statement I gave you does work and very well, but as Stolze pointed out, you can only have 1 column value supplied, so you would have to use multiple case statements within the insert statement, one for each column you want to be based on the new value. For instance:
    Code:
    CREATE TRIGGER VALIDATION
    AFTER UPDATE OF VALUE ON DATA_VALUE
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW 
     INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, case when n.value > 10 
                 then 111
           when > 20
                 then 222
           when n.value > 30
                THEN 333
                else 444 end,
    case when n.value > 10 
                 then 1
           when > 20
                 then 2
           when n.value > 30
                THEN 3
                else 4 end,0,0)
    ;
    END
    Dave Nance

  10. #10
    Join Date
    Jul 2009
    Posts
    17
    Hi, thanks a lot for your help. In the end dav1mo's solution worked after some slight adjustments:
    Code:
    CREATE TRIGGER VALIDATION
    AFTER UPDATE OF VALUE ON DATA_VALUE
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW 
     INSERT INTO RULE_VIOLATION VALUES (N.CELL_ID, N.QUESTIONNAIRE_ID, case when n.value > 10 
                 then 111
           when n.value > 20
                 then 222
           when n.value > 30
                THEN 333
                else 444 end,
    case when n.value > 10 
                 then 1
           when n.value > 20
                 then 2
           when n.value > 30
                THEN 3
                else 4 end ,0,0)
    ;
    I know people with only half knowledge of what they are doing can be a little frustrating, but I am two weeks away from my thesis deadline and under some pressure.
    Thanks again,

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by dav1mo View Post
    The case statement ...
    That's a CASE expression - not a CASE statement. Both are different constructs.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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