Why are you giving this error and how do I reverse it?

Question

Why are you giving this error and how do I reverse it?

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

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 get 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')

mysql trigger

asked by anonymous 07.02.2018 / 21:58

2 answers

Photos Preload Trigger to update several lines MariaDB

score 0 · Answer 1

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

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

score 0 · Answer 2

I was able to solve my problem by making use of a function inside the 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 minor 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 hints, especially in function, I'll be very grateful!