function
delimiter $$ create function my_calculation( s1_ia_att decimal, s1_t1 decimal, s1_t2 decimal, s1_t3 decimal, s2_ia_att decimal, s2_t1 decimal, s2_t2 decimal, s2_t3 decimal, s3_ia_att decimal, s3_t1 decimal, s3_t2 decimal, s3_t3 decimal, s4_ia_att decimal, s4_t1 decimal, s4_t2 decimal, s4_t3 decimal, s5_ia_att decimal, s5_t1 decimal, s5_t2 decimal, s5_t3 decimal, s6_ia_att decimal, s6_t1 decimal, s6_t2 decimal, s6_t3 decimal, s7_ia_att decimal, s7_t1 decimal, s7_t2 decimal, s7_t3 decimal, s8_ia_att decimal, s8_t1 decimal, s8_t2 decimal, s8_t3 decimal ) returns int deterministic begin RETURN (s1_t1 + s1_t2 + s1_t3 - LEAST(s1_t1, s1_t2, s1_t3)) / 2 + CASE WHEN s1_ia_att > 95 THEN 5 WHEN s1_ia_att BETWEEN 81 AND 95 THEN 4 WHEN s1_ia_att BETWEEN 76 AND 80 THEN 3 WHEN s1_ia_att BETWEEN 61 AND 75 THEN 2 WHEN s1_ia_att < 60 THEN 0 (s2_t1 + s2_t2 + s2_t3 - LEAST(s2_t1, s2_t2, s2_t3)) / 2 + CASE WHEN s2_ia_att > 95 THEN 5 WHEN s2_ia_att BETWEEN 81 AND 95 THEN 4 WHEN s2_ia_att BETWEEN 76 AND 80 THEN 3 WHEN s2_ia_att BETWEEN 61 AND 75 THEN 2 WHEN s2_ia_att < 60 THEN 0 END; END $$ DELIMITER ;
And here is my trigger
delimiter $$ create trigger abc before insert on `test` for each row begin declare `value` decimal; SET NEW.s1_ia_tot = my_calculation( NEW.s1_ia_att, NEW.s1_t1, NEW.s1_t2, NEW.s1_t3 ); SET NEW.s2_ia_tot = my_calculation( NEW.s2_ia_att, NEW.s2_t1, NEW.s2_t2, NEW.s2_t3 ); SET NEW.s3_ia_tot = my_calculation( NEW.s3_ia_att, NEW.s3_t1, NEW.s3_t2, NEW.s3_t3 ); ...... end $$ delimiter ;
I did my trigger and the function and nothing happens in the ia_att_tot field is still null I can not find what the problem is in this code. Can someone say what is wrong in my code?
My table
CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `s1_ia_att` DECIMAL(12, 2) NULL, .... .... `s1_t1` DECIMAL(12, 2) NULL, `s1_t2` DECIMAL(12, 2) NULL, `s1_t3` DECIMAL(12, 2) NULL, .... .... `s1_ia_tot` DECIMAL(12, 2) NULL, ...... ..... PRIMARY KEY (`id`) );