I need to mount a trigger that before deleting a user it checks if there are any events linked to the user in question, and if it exists, do not allow delete, but never worked with Triggers .
Can anyone help me?
I have the usuario and ocorrencias table where the ocorrencias.responsavel field references the ID of the user.
You do not need a trigger . It is only necessary that you create a foreign key of the column and determine that it is not possible to delete if there is a child:
ALTER TABLE ocorrencias
ADD CONSTRAINT frk_ocorrencias_usuario
FOREIGN KEY(responsavel)
REFERENCES usuario(id)
ON DELETE RESTRICT
The type of constraint that maintains database consistency through a foreign key relationship.
Free translation:
If you really need to perform the check withThe type of constraint that maintains the consistency of the database by applying the relationship between foreign keys.
trigger (which is not recommended) you can do as follows:
DELIMITER $
CREATE TRIGGER trg_bd_usuario BEFORE DELETE
ON usuario
FOR EACH ROW
BEGIN
IF (SELECT 1 = 1 FROM ocorrencias WHERE responsavel = OLD.id) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Este usuário possui ocorrências e não pode ser deletado.';
END IF;
END$
Remembering that SIGNAL only works on MySQL from 5.5 version.