Create Trigger update Mysql

Asked

Viewed 2,343 times

2

I have two tables one apartment to another contract. When the status of the contract is changed to off. The status of the apartment is free. I have a foreign key in the contract table to relate to apartment.

I would like to know how to create a Trigger to update an apartment table field after updating a contract table field.

DELIMITER // 
CREATE TRIGGER upd_tab_b BEFORE UPDATE ON tab_b FOR EACH ROW BEGIN 
UPDATE tab_a 
SET campo_a = livre 
WHERE 1 = 1; 
-- a dúvida é como dizer atualize --quando o campo da tabela B for --igual a off
END;
// DELIMITER ;
mysql

  • Ramon, could give more details of what types of information you will update?

  • Maybe you need to Rigger in both tables, where the first Rigger sends an update that updates the second Rigger.... I don’t know... more details are better to direct you.

  • Ok. Well I have two tables one apartment to another contract. When the status of the contract is changed to off. The status of the apartment is free. I have a foreign key in the contract table to relate to apartment.

  • The doubt is how I say update the Taba.field = free Where Tabb.field = off and Taba.id = Tabb.id

  • Prefer [Edit] your question instead of leaving the details only in the comment. It helps a lot for the next ones to read your question. Try to make a [tour] to better understand how to improve your questions ;)

1 answer

3

In practice, to set the field tab_a.campo_a = tab_b.campo_b + 1, would look something like this:

DELIMITER //
CREATE TRIGGER upd_tab_b BEFORE UPDATE ON tab_b
FOR EACH ROW
BEGIN
    UPDATE tab_a SET campo_a = NEW.campo_b + 1
    WHERE 1 = 1; -- informe suas condições
END;//
DELIMITER ;

Worth to take a look at the syntax of a Trigger.

  • Good answer but not the condition I mentioned. After changing the status in a field of table B to tringger you have to change the status of the field of table A

  • Ramon, just know which key (Foreign key) interconnects the two tables and use in the condition. You can be sure it will work.

  • Victor did not work

  • BEGIN IF new.status_rent = 'cerrado' UPDATE apartment SET status_apart = 'libre' WHERE id_apart = new.apartment_id_apart; end if; END

  • Dear Ramon, you asked a general question and I answered it. Now you edited the question, specifying the context, which would change the tone of the answer. When so, please ask another question. Read this guide to understand the basic workings of Stackoverflow en.stackoverflow.com/tour

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.