sql - How to create trigger on update with condition based on old and new data? -

i want update field server_prev_level_xp when new.server_xp > old.server_prev_level_xp

i use mariadb , documentation.

i try doesn't work :

create trigger level_update after update of server_xp on servers each row when (new.server_xp > old.server_prev_level_xp) begin     set new.server_prev_level_xp = old.server_prev_level_xp + old.server_prev_level_xp >> 1;     set new.server_level = old.server_level + 1; end; 

have error on line 4:

when (new.server_xp > old.server_prev_level_xp) 

what doing wrong?

the syntax used create trigger doesn't apply mariabd ([of <trigger column list>] , for each row [when (search condition)]). see create trigger.

one option use if statement.

remember: such set statement has no effect in after trigger because row change have occurred..

delimiter //  create trigger `level_update` /*after*/ before update on `servers` each row begin   if new.`server_xp` > old.`server_prev_level_xp`   .   .   .   end if; end//  delimiter ;