What is the error of this trigger?

Question

What is the error of this trigger?

Navigation

#1 by (2 votes)

0

I have this table:

create table alecrim(
id_alecrim 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,
ano varchar(4),
primary key(id_alecrim)
) default charset = utf8;

And this trigger:

CREATE TRIGGER somaOvosIFS BEFORE UPDATE
ON alecrim
FOR EACH ROW
DELIMITER \
    begin
    SET NEW.totOvos = NEW.p1 + NEW.p2 + NEW.p3 + NEW.p4 + NEW.p5 + NEW.p6 + NEW.p7 + NEW.p8 + NEW.p9;
    IF(old.pend > 0) THEN
    NEW.pend = old.pend - 1;
    END IF;
END \

The purpose of the trigger is to update the fields in question (so far it works) and if the pend field is greater than 0 it updates its value with the operation in question.

Does anyone help?

mysql trigger

asked by anonymous 09.01.2018 / 18:01

1 answer

Function with cookies in PHP Check last record of an sql table

score 2 · Answer 1

Just my own experience!

The TRIGGER is fixed:

delimiter //
CREATE TRIGGER somaOvosAlecrim BEFORE UPDATE ON alecrim
FOR EACH ROW
BEGIN
 IF OLD.pend > 0 THEN
 SET NEW.totOvos = NEW.p1 + NEW.p2 + NEW.p3 + NEW.p4 + NEW.p5 + NEW.p6 + NEW.p7 + NEW.p8 + NEW.p9,
 NEW.PEND = OLD.pend - 1;
 ELSEIF OLD.pend = 0 THEN
 SET NEW.totOvos = NEW.p1 + NEW.p2 + NEW.p3 + NEW.p4 + NEW.p5 + NEW.p6 + NEW.p7 + NEW.p8 + NEW.p9;
 END IF;
END;//
delimiter ;