MySQL (mariaDB) trigger syntax always wrong

Question

I'm making a small game and I need to have a trigger on my members table, which will check the experience column, and anytime it gets bigger than certain values, the level will become level+1. This is the trigger:

DELIMITER $$
CREATE TRIGGER update_level AFTER UPDATE ON members
FOR EACH ROW BEGIN
 IF experience < 1000 THEN 
 SET members.level = 1
 ELSE IF experience < 2400 THEN 
 SET level = 2
 ELSE IF (experience < 4000) THEN 
 SET level=3
 ELSE IF (experience < 8000) THEN 
 SET level=4
 ELSE IF (experience < 15000) THEN 
 SET level=5
 ELSE IF (experience < 24000) THEN 
 SET level=6
 ELSE IF (experience < 35000) THEN 
 SET level=7
 ELSE IF (experience < 50000) THEN 
 SET level=8
 END IF
END
$$
DELIMITER ;

phpMyAdmin always responds with this error code:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

I spent about 2 hours trying to figure it out, but it looks like I can't do it by myself. What I have tried is to change the IF statement to a CASE statement (simple switch). I have tried it with WHEN xxx THEN SET. Also with UPDATE. Another thing I have tried was to add ' ' and " " everywhere around the column names and values - didn't help at all.

Can you please advise me about what's the best way to accomplish this? I'm pretty sure there is some small mistake there, and if not that, then my approach must be wrong.

• Michael Hampton: Where is the rest of the error message?
• Burgi: Shouldn't this logic be in the game code rather than the database?
• ypercubeᵀᴹ: I'd use a CASE expression, not a CASE statement. But why don't you remove this level column and calculate it (with a CASE expression), every time you need it? You won't need a trigger that way. And since you are on MariaDB, you can even add it as a VIRTUAL column!
• ypercubeᵀᴹ: The error is cause because you use experience. Use NEW.experience instead to access the new (updated) valued of the column.

Answer 1

When trying to compile the original trigger code, we encounter quite a few issues. As some colleagues have pointed out, the timing ("after") should be adjusted. There are some syntactical and logical problems, too (eg < instead of>). Maybe it would be better to use BETWEEN ... AND ... for defining the "levels".

-- test data
create table members (member_id int, experience int, level_ int);
insert into members values (1,2200,1),(2,3800,2),(3,7800,3);

Trigger (MySQL 5.7)

DELIMITER $$
create trigger update_level 
 before update on members
 for each row
begin
 if new.experience between 0 and 999 then
 set new.level_ = 1; 
 elseif new.experience between 1000 and 2399 then
 set new.level_ = 2;
 elseif new.experience between 2400 and 3999 then
 set new.level_ = 3 ;
 elseif new.experience between 4000 and 7999 then
 set new.level_ = 4 ;
 elseif new.experience between 8000 and 14999 then
 set new.level_ = 5 ;
 elseif new.experience between 15000 and 23999 then
 set new.level_ = 6 ;
 elseif new.experience between 24000 and 34999 then
 set new.level_ = 7 ;
 elseif new.experience between 35000 and 49999 then
 set new.level_ = 8 ;
 elseif new.experience >= 50000 then
 set new.level_ = 9 ; 
 end if;
end;
$$
DELIMITER ;

Testing

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
| 1 | 2200 | 1 |
| 2 | 3800 | 2 |
| 3 | 7800 | 3 |
+-----------+------------+--------+
3 rows in set (0.00 sec)
update members set experience = 8500 where member_id = 1 ;
update members set experience = 16000 where member_id = 2 ;
update members set experience = 25000 where member_id = 3 ;
mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
| 1 | 8500 | 5 |
| 2 | 16000 | 6 |
| 3 | 25000 | 7 |
+-----------+------------+--------+
3 rows in set (0.00 sec)
update members set experience = 36000 where member_id = 1 ;
update members set experience = 55000 where member_id = 2 ;
update members set experience = 10 where member_id = 3 ;
mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
| 1 | 36000 | 8 |
| 2 | 55000 | 9 |
| 3 | 10 | 1 |
+-----------+------------+--------+
3 rows in set (0.01 sec)

ALTERNATIVE

A VIEW may be a better solution for this scenario. We could use a different table layout for MEMBERS, and avoid storing levels ("level" depends on "experience", not on member_id ...), like so (MariaDB 10.2):

-- test data
create table members (member_id int, experience int);
insert into members values (1,2200),(2,3800),(3,7800);

Now we can find the current level associated with a member_id by SELECTing from the VIEW. No trigger required.

create or replace view member_level
as
select
 member_id
, experience
, case
 when experience between 0 and 999 then 'level 1'
 when experience between 1000 and 2399 then 'level 2' 
 when experience between 2400 and 3999 then 'level 3' 
 when experience between 4000 and 7999 then 'level 4' 
 when experience between 8000 and 14999 then 'level 5' 
 when experience between 15000 and 23999 then 'level 6' 
 when experience between 24000 and 34999 then 'level 7' 
 when experience between 35000 and 49999 then 'level 8' 
 when experience >= 50000 THEN 'level 9' 
 end as level_
from members ; 

Testing

select * from members;
member_id experience
1 2200
2 3800
3 7800
update members
set experience = 27500 
where member_id = 1 ;
select * from member_level;
member_id experience level_
1 27500 level 7
2 3800 level 3
3 7800 level 4

See dbfiddle here.

Another alternative (MariaDB version 5.2+, MySQL 5.7)

Use a virtual column, let the DDL do the work.

create table members (
 member_id int
, experience int
, level_ varchar(32) as (
 case
 when experience between 0 and 999 then 'level 1'
 when experience between 1000 and 2399 then 'level 2' 
 when experience between 2400 and 3999 then 'level 3' 
 when experience between 4000 and 7999 then 'level 4' 
 when experience between 8000 and 14999 then 'level 5' 
 when experience between 15000 and 23999 then 'level 6' 
 when experience between 24000 and 34999 then 'level 7' 
 when experience between 35000 and 49999 then 'level 8' 
 when experience >= 50000 THEN 'level 9' 
 end ) virtual
);
insert into members (member_id, experience) values (1,2200),(2,3800),(3,7800);
select * from members;
member_id experience level_
1 2200 level 2
2 3800 level 3
3 7800 level 4

Dbfiddle here.

Answer 2

NEW.level, not members.level. And perhaps it should be BEFORE UPDATE.

• a_vlad: think also and NEW.experience, and of course BEFORE