Create trigger after update in the table itself and in another

1

First I have a table:

create table alecrim(
id_alecrim int not null auto_increment, 
sem_epi int not null unique,
p1 smallint,
p2 smallint,
p3 smallint,
p4 smallint,
p5 smallint, 
p6 smallint,  
p7 smallint,
p8 smallint,
p9 smallint,
totOvos int,
ano varchar(4),
primary key(id_alecrim)
) default charset = utf8;

And the table:

create table tb_indices_leste(
id_leste int not null auto_increment,
localidade varchar(30),
sem_epi int not null unique,
totOvos smallint,
pend tinyint,
ext tinyint,
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_leste)) default charset = utf8;

And I want to create a trigger with the purpose of after an UPDATE in the table ALECRIM the trigger "SETar" the field totOvos with the sum of the values of the fields p1 + p2 + ... + p9, however this sum must be from the line where hears the UPDATE and "SET" the same value in the totOvos field of the tb_indices_alecrim table on the line where the value of sem_epi is equal to the value of the semecep of ALECRIM in which it has suffered the UPDATE. Could someone help me with this?

    
asked by anonymous 01.01.2018 / 14:28

1 answer

1

Just use BEFORE UPDATE :

CREATE TRIGGER trigger_alecrim_update BEFORE UPDATE 
ON alecrim
FOR EACH row
    SET NEW.totOvos = NEW.p1 + NEW.p2 + NEW.p3 + NEW.p4 + NEW.p5 + NEW.p6 + NEW.p7 + NEW.p8 + NEW.p9;
    
01.01.2018 / 17:36