Create Trigger after update in the table itself and in another table

Question

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 in order to after an UPDATE in the ROSEMARY table the Trigger "Setar" the totOvos field with the sum of the values of the fields P1+P2+...+P9, but this sum needs to be the line where you hear the UPDATE and "Setar" the same value in the totOvos field of table tb_indices_rosemary in the row where the value of sem_epi is equal to the value of sem_epi of ROSEMARY in which the UPDATE occurred. Could someone help me with this?

Answer 1

Just use the 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;