mysql trigger update set null value instead of intended number -

i'm trying create mysql trigger should take new inserted numbers 1 table (raw_us), compare last record (is new value higher last one, or lower last one), index them ranges values -10 10 , place table (indexed_us). database tables:

 create table raw_us(rdate date,pmi decimal(3,2));  create table indexed_us(idate date, ipmi decimal (2,2));  insert raw_us(rdate,pmi) values(20000101, 50);  insert indexed_us(idate,ipmi) values(20000101, 5); 

i wrote trigger code sort out new raw data (inserted table raw_us column pmi) - new 'pmi' value bigger or smaller last one, , check range new raw data belongs, , according particular range insert particular number indexed_us table impi column. here trigger code:

 delimiter //  create trigger ipmius before insert on raw_us  each row  begin  declare @old_pmi int;  set @old_pmi = (select pmi raw_us rdate = max(rdate));  if (@old_pmi < new.pmi)    if (50 < new.pmi) , (new.pmi < 60)      insert indexed_us(idate,ipmi) values (new.date,6);    elseif (60 < new.pmi) , (new.pmi < 70)      insert indexed_us(idate,ipmi) values (new.date,7);    end if;  elseif (@old_pmi > new.pmi)    if (50 > new.pmi) , (new.pmi > 60)      insert indexed_us(idate,ipmi) values (new.date,5);    elseif (40 > new.pmi) , (new.pmi > 50)      insert indexed_us(idate,ipmi) values (new.date,4);    end if;  end if; end; // delimiter ; 

and problem after updating trigger 'before insert' , fixing within ranges if statements ' if (number > new.pmi) , (new.pmi > number) ' error: have error in sql syntax; check manual corresponds mysql server version right syntax use near 'delimiter // create trigger ipmius before insert on raw_us each row ' @ line 1.

you have problem if expressions. writing "within range" (30 < new.pmi < 40) in way doesn't produce intended results.

to make clear, try this:

select     30 < 1 < 40 ; 

you response:

 | 30 < 1 < 40 | | ----------: | |           1 | 

this because mysql computing:

select     (30 < 1) < 40 ; 

the result of (30 < 1) false, , represented mysql 0, (0 < 40) true, , represented 1.

you need rewrite conditions this:

if (30 < new.pmi) , (new.pmi < 40)     -- whatever ... 

also, take account have corner cases not being taken account. instance, when 40 = new.pmi, want something happen. when checking ranges, practice have comparisons like:

`(lower_bound <= value) , (value < upper_bound)` 

(note <= , <).

note also, can write (note both <= , <=)

(lower_bound <= value) , (value <= upper_bound)

with equivalent form

value between lower_bound , upper_bound 

this practice when deal integers or decimals, not if use floats.

---

see whole example @ dbfiddle here