sql - MySQL Trigger for aggregating one table's rows to another table -

i have 2 mysql tables, 1 updated new rows needs aggregated(table 1). , 1 should automatically filled aggregated data using mysql trigger (table 2).

given tables schema:

example table 1: table aggregated

unique key uniqueid (uniqueid,ci,ai,status)

example table 2: aggregated table

unique key ix_unique_daily (ai,ci,day)

the sql trigger should like:

delimiter $$

create trigger yii_botclient2.st_unqiue_trigger after insert on yii_botclient2.st_unique each row begin

set @myday := date(new.date); set @today = date(); set isdaily := select count(id) `yii_botclient2`.`st_daily` `ai` = new.ai , st_daily.ci = new.ci , st_daily.day = @myday; set mydaily = ifnull(isdaily, 'no');  if (mydaily = 'no')     if new.status = 0         insert st_daily (`day`, `ai`,`ci`,`impressions`,`updated`) values (@today,new.ai,new.ci,1,now());     elseif new.status = 1          insert st_daily (`day`, `ai`,`ci`,`clicks`,`updated`) values (@today,new.ai,new.ci,1,now());     elseif new.status = 2         insert st_daily (`day`, `ai`,`ci`,`leads`,`updated`) values (@today,new.ai,new.ci,1,now());     elseif new.status = 3          insert st_daily (`day`, `ai`,`ci`,`ftds`,`updated`) values (@today,new.ai,new.ci,1,now());     end if; else     if new.status = 0         update st_daily set `impressions` = `impressions` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = @myday;     elseif new.status = 1          update st_daily set `clicks` = `clicks` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = @myday;     elseif new.status = 2         update st_daily set `leads` = `leads` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = @myday;     elseif new.status = 3          update st_daily set `ftds` = `ftds` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = @myday;     end if; end if;  end$$ 

delimiter ;

can please mysql trigger ?

declare variables, use = in set statements, date functions need parameter, if statements need then's compiles point complains tables not existing, if want further please provide tables in question text or sql fiddle.

drop trigger if exists st_unqiue_trigger;   delimiter $$  create trigger yii_botclient2.st_unqiue_trigger after insert on st_unique each row begin declare myday date; declare today  date; declare isdaily int; declare mydaily varchar(2); set myday = date(new.date); set today = date(now()); select count(id) isdaily `yii_botclient2`.`st_daily` `ai` = new.ai , st_daily.ci = new.ci , st_daily.day = myday; set mydaily = ifnull(isdaily, 'no');  if (mydaily = 'no')     if new.status = 0         insert st_daily (`day`, `ai`,`ci`,`impressions`,`updated`) values (today,new.ai,new.ci,1,now());     elseif new.status = 1         insert st_daily (`day`, `ai`,`ci`,`clicks`,`updated`) values (today,new.ai,new.ci,1,now());     elseif new.status = 2         insert st_daily (`day`, `ai`,`ci`,`leads`,`updated`) values (today,new.ai,new.ci,1,now());     elseif new.status = 3         insert st_daily (`day`, `ai`,`ci`,`ftds`,`updated`) values (today,new.ai,new.ci,1,now());     end if; else     if new.status = 0         update st_daily set `impressions` = `impressions` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = myday;     elseif new.status = 1         update st_daily set `clicks` = `clicks` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = myday;     elseif new.status = 2         update st_daily set `leads` = `leads` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = myday;     elseif new.status = 3         update st_daily set `ftds` = `ftds` + 1 st_daily.ci = new.ci , st_daily.ai = new.ai , st_daily.day = myday;     end if; end if;  end $$  delimiter ;