if statement - Variable is getting NULL after calculations in MySQL Trigger -

if statement - Variable is getting NULL after calculations in MySQL Trigger -

i have table called initial_fees. there column in table called initial_consult_fee cannot more 30,000 per year. below table.

create table `initial_fees` ( `idinitial_fees` int(11) not null auto_increment, `idportfolio` int(11) not null, `current_time_stamp` timestamp not null default current_timestamp on update current_timestamp, `initial_gross_fee` double not null, `initial_consult_fee` double not null, `updated_date` date not null, `idtransactions` int(11) default null, primary key (`idinitial_fees`) )

so, created trigger, check, whether initial_consult_fee above 30,000 or not. apart that, round values.

delimiter $$ create trigger `initial_fees_bins` before insert on `initial_fees` each row begin declare `initial` double; declare `ongoing` double; declare `total` double; declare `valuetobeentered` double; set `initial`:=(select sum(`initial_consult_fee` ) `initial_fees` `updated_date` between makedate(year(new.updated_date),1) , makedate(year(new.updated_date),365)); set `ongoing`:=(select sum(`ongoing_consult_fee` ) `ongoing_fees` `updated_date` between makedate(year(new.updated_date),1) , makedate(year(new.updated_date),365)); set `total` := `initial`+`ongoing`; if((new.initial_consult_fee+`total`) > 30000) set `valuetobeentered`:= 30000 - `total`; end if; if((new.initial_consult_fee+`total`) < 30000) set `valuetobeentered`:= new.initial_consult_fee; end if; set new.initial_gross_fee = round(new.initial_gross_fee,2), new.initial_consult_fee = round(`valuetobeentered`,2); end;

however, when come in data, , if new.initial_vision_fee+total below error

column 'initial_consult_fee' cannot null

what wrong code?

a few comments.

first, looking @ total consulting fees across portfolios. seems unusual. conform description, expect total based on factor, such portfolio.

second, problem occurring when there no matches in ingoing or outgoing tables. values of variables null.

you can readily prepare doing like:

set `total` := coalesce(`initial`, 0) + coalesce(`ongoing`, 0);

mysql if-statement jdbc triggers null