I am attempting to create DB2 trigger that is while using procedure
VOTES_COUNT however when I attempt to produce the trigger I recieve the mistake:
An unpredicted token "" was discovered 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 @
Do you know me what I am doing wrong?
This can be done without saved 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 @