Trigger with multiple conditions

dajense · #1 (**permalink**) 12-28-09, 12:43

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

tonkuma · #2 (**permalink**) 12-28-09, 13:30

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.

tonkuma · #3 (**permalink**) 12-28-09, 13:39

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)
.....

dav1mo · #4 (**permalink**) 12-28-09, 13:41

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

dajense · #5 (**permalink**) 12-28-09, 14:10

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

tonkuma · #6 (**permalink**) 12-28-09, 14:15

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.....

Quote:

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.

stolze · #7 (**permalink**) 12-28-09, 14:48

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.

tonkuma · #8 (**permalink**) 12-28-09, 15:06

Quote:

IF-statements come to mind, for example.

or CASE statement(not CASE expression).

dav1mo · #9 (**permalink**) 12-28-09, 15:16

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

dajense · #10 (**permalink**) 12-28-09, 15:48

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,