Trigger to devalue apartment price if garage linked to it is excluded

Question

I need to create a Rigger that devalues the value of the apartment when a garage linked to it is deleted, I have the following tables:

create table apartamento (
  numero varchar(5) not null,
  tipo varchar(20) default null,
  codigo_cond int(5) default null,
  valor double(10,2) default '0.00',
  primary key (numero),
  key fk_ap_cond (codigo_cond),
  constraint fk_ap_cond foreign key (codigo_cond) references condominio (codigo)
);

create table garagem (
  numero int(3) not null auto_increment,
  tipo varchar(20) default null,
  numero_ap varchar(5) default null,
  primary key (numero),
  key fk_gar_apartamento (numero_ap),
  constraint fk_gar_apartamento foreign key (numero_ap) references apartamento (numero)
);

And so far my Rigger is like this, I’m having trouble applying the condition of the garage to be deleted to devalue the price:

CREATE TRIGGER
`imobiliaria`.`desv_apartamento` AFTER DELETE ON `apartamento` 
FOR EACH ROW
BEGIN
UPDATE apartamento.valor
IF

END IF;
END

Answer 1

First, it is necessary to know what a Trigger. Trigger is a trigger programmed to be triggered when a particular event occurs in a specific database object.

CREATE TRIGGER desv_apartamento 
AFTER DELETE ON `garagem` 
FOR EACH ROW
BEGIN
UPDATE apartamento set
  valor = valor * .90
  WHERE numero = OLD.numero_ap;
END;

Note that the correct object to fire the Trigger is to delete a garage, so this Rigger will be fired every time a garage is deleted. It is therefore necessary to replace AFTER DELETE ON apartamento for AFTER DELETE ON garagem.

The instruction CREATE TRIGGER creates a trigger called desv_apartamento which is associated with the table garagem. It also includes clauses that specify trigger time, trigger event, and what to do when the trigger is activated:

The keyword AFTER indicates the trigger’s action time. In this case, the trigger is activated after each row deleted in the table. The other allowed keyword here is BEFORE.

The keyword DELETE indicates the trigger event; ie the type of operation that activates the trigger. In the example, the operations DELETE cause trigger activation. You can also create triggers for operations INSERT and UPDATE.

The instruction to follow FOR EACH ROW sets the body of the trigger; i.e., the instruction to be executed whenever the trigger is activated, which occurs once for each line affected by the trigger event.

Read more:

To read more about Trigger in Mysql, visit the page that was the basis of the answer theory: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

In this other example, you can see how to insert a condition in Trigger and return an error message if you want: Prevent the insertion by the user of the same number of CPF in three different fields