MySQL (mariaDB) trigger syntax always wrong

mariadbMySQLtrigger

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.

Best Answer

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.

Related Question