Why are you making this mistake and how do I reverse it?

Question

I have this table:

create table lagoa_azul(
id_lagoa_azul int not null auto_increment, 
sem_epi int not null,
p1 smallint,
p2 smallint,
p3 smallint,
p4 smallint,
p5 smallint, 
p6 smallint,  
p7 smallint,
p8 smallint,
p9 smallint,
totOvos int,
pend tinyint,
ext tinyint,
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_lagoa_azul)
) default charset = utf8;

and this Trigger:

delimiter //
CREATE TRIGGER IpoIdoLagoaAzul before insert ON lagoa_azul
FOR EACH ROW
BEGIN
    declare nump int;
    declare tovos int;

    set @nump := (select count(nullif(new.p1, 0)) + count(nullif(new.p2, 0)) + count(nullif(new.p3, 0)) + count(nullif(new.p4, 0))
+ count(nullif(new.p5, 0)) + count(nullif(new.p6, 0)) + count(nullif(new.p7, 0)) + count(nullif(new.p8, 0)) + count(nullif(new.p9, 0)) from lagoa_azul where sem_epi= new.sem_epi);

    set @tovos = new.p1+new.p2+new.p3+new.p4+new.p5+new.p6+new.p7+new.p8+new.p9;

    set new.ipo = (@nump/9)*100;
    set new.ido = @tovos/@nump; 

END; //
delimiter ;

When I run an INSERT INTO I have this error:

Operation failed: There was an error while applying the SQL script to the database.
Executing:

INSERT INTO `databaseentomo1.2`.`lagoa_azul` (`sem_epi`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`, `pend`, `ext`, `ano`) VALUES ('1', '2', '2', '2', '2', '2', '2', '2', '2', '0', '0', '0', '2018');

ERROR 1365: 1365: Division by 0
SQL Statement:
INSERT INTO `databaseentomo1.2`.`lagoa_azul` (`sem_epi`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`, `pend`, `ext`, `ano`) VALUES ('1', '2', '2', '2', '2', '2', '2', '2', '2', '0', '0', '0', '2018')

Answer 1

From what I saw, the variable @numb can be zero, giving error when executing the command @tovos/@nump. It would be better to check before this operation the value of @numb.

Another improvement you can make on your INSERT would exchange the values in quotation marks to integer values, respecting the TYPE of the columns.

Answer 2

I was able to solve my problem by using a function within Trigger.

a FUNCTION:

delimiter //
create function numpAlecrim (p1 int, p2 int, p3 int, p4 int, p5 int, p6 int, p7 int, p8 int, p9 int) returns int

begin
    declare nump int default 0;

    if p1<>0 then set nump := nump+1;
    end if;
    if p2<>0 then set nump := nump+1;
    end if;
    if p3<>0 then set nump := nump+1;
    end if;
    if p4<>0 then set nump := nump+1;
    end if;
    if p5<>0 then set nump := nump+1;
    end if;
    if p6<>0 then set nump := nump+1;
    end if;
    if p7<>0 then set nump := nump+1;
    end if;
    if p8<>0 then set nump := nump+1;
    end if;
    if p9<>0 then set nump := nump+1;
    end if;    
    return nump;
end //
delimiter ;

And I made little changes to TRIGGER:

delimiter //
CREATE TRIGGER IpoIdoLagoaAzul before insert ON lagoa_azul
FOR EACH ROW

BEGIN       
    set @tovos = new.p1+new.p2+new.p3+new.p4+new.p5+new.p6+new.p7+new.p8+new.p9;

    select numpAlecrim(new.p1,new.p2,new.p3,new.p4,new.p5,new.p6,new.p7,new.p8,new.p9) into @nump;

    set new.totOvos = @tovos;
    set new.ipo = (@nump/9)*100;
    set new.ido = @tovos/@nump; 

END; //
delimiter ;

If you have any more simplified code suggestions, especially in the role, I would be very grateful!