DB2 trigger definition

Question

I'm trying to create DB2 trigger which is using the procedure VOTES_COUNT but when I try to create the trigger I get the error:

An unexpected token "" was found following "". Expected tokens may include: "SET status = 1".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.7.89

Here's my code:

CREATE PROCEDURE VOTES_COUNT (IN username VARCHAR(15), OUT votesCount INT)
DYNAMIC RESULT SETS 1

P1: BEGIN

DECLARE C1 CURSOR FOR 
    SELECT COUNT(*) AS R1
        FROM VOTES
        WHERE USER = username;
OPEN C1;    
FETCH C1 INTO votesCount;   
CLOSE C1;

END P1 @

CREATE TRIGGER UPDATE_LEVEL
AFTER INSERT ON VOTES
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
    DECLARE num INT;
    DECLARE status INT;
    CALL VOTES_COUNT(N.USERNAME, num);
    CASE
        WHEN num >= 300 
            THEN SET status = 5;
        WHEN num < 300 AND num >= 200
            THEN SET status = 4;
        WHEN num < 200 AND num >= 100
            THEN SET status = 3;
        WHEN num < 100 AND num >= 50
            THEN SET status = 2;
        ELSE
            SET status = 1;
    END CASE;
    UPDATE USERS SET STATUS = status WHERE USERNAME = N.USERNAME;
END @

Can you tell me what I'm doing wrong?

Minko Gechev Minko Gechev · Accepted Answer · 2012-01-15T12:24:59

You can do this without stored procedure:

CREATE TRIGGER UPDATE_LEVEL
AFTER INSERT ON VOTES
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    DECLARE votesCount INT;
    DECLARE userStatus INT;     
    SET votesCount = (SELECT COUNT(*)
    FROM USERS
    WHERE USERNAME = N.USERNAME); 

    IF votesCount >= 300 THEN
        SET userStatus = 5;
    ELSEIF votesCount < 300 AND votesCount >= 200 THEN
        SET userStatus = 4;
    ELSEIF votesCount < 200 AND votesCount >= 100 THEN
        SET userStatus = 3;
    ELSEIF votesCount < 100 AND votesCount >= 50 THEN
        SET userStatus = 2;
    ELSE
        SET userStatus = 1;
    END IF;

    UPDATE USERS SET STATUS = userStatus WHERE USERNAME = N.USERNAME;
END @

DB2 trigger definition

1 Answers