Mysql trigger math calculation -

there 2 tables: 'replies' , 'posts' , trigger placed on 'replies' table.

every time new entry placed in replies table, trigger checks if conditions true values in row on posts table matching id new entry.

here have far:

create table posts( p_id int unsigned not null auto_increment primary key, p_health int unsigned not null default 0,  p_bump int unsigned not null default 0, p_time timestamp);  create table replies( r_id int unsigned not null auto_increment primary key, r_to int unsigned not null, r_time timestamp);  create trigger bump after insert on replies each row begin   if posts.p_bump < 5 posts.p_id = new.r_to , (select count(r_to) replies r_to = new.r_to)%10 = 10     update posts        set posts.p_bump = posts.p_bump + 1 ,       set posts.p_health = 0 posts.p_id = new.r_to;   end if; end;  insert posts() values(); insert replies(r_to) values(1); insert replies(r_to) values(1);  insert posts() values(); insert replies(r_to) values(2); insert replies(r_to) values(2); insert replies(r_to) values(2); 

'r_to' shorthand "reply to", stores id of post reply directed to. sqlfiddle says there error on line 4, try arithmetic operation select count(r_to) modulo of 10.

cannot create sqlfiddle, doesn't save schema if it's incorrect.

two issues here! first need change delimiter define trigger.

delimiter $$ create trigger bump after insert on replies each row begin   if posts.p_bump < 5 posts.p_id = new.r_to , (select count(r_to) replies r_to = new.r_to)%10 = 10     update posts        set posts.p_bump = posts.p_bump + 1 ,       set posts.p_health = 0 posts.p_id = new.r_to;   end if; end;  delimiter ; 

the second delimiter command isn't supported on sqlfiddle.com not able create fiddle way. don't know work around sqlfiddle, typing console should trick.

not quite sure if legit

if posts.p_bump < 5 posts.p_id = new.r_to , (select count(r_to) replies r_to = new.r_to)%10 = 10 

i think have

select count(r_to) @myvar replies r_to = new.r_to; if @myvar %10 = 10    update posts        set posts.p_bump = posts.p_bump + 1 ,       set posts.p_health = 0 posts.p_bump < 5 ,  posts.p_id = new.r_to; end if; 

this simpler query , think expect. someint % 10 never 10 can take values of 0 9 have come right conditions that.