Help with MYSQL TRIGGER

Question

I need to assemble a Trigger that before deleting a user it checks if there are occurrences linked to the user in question, and if there is, not allow delete, but never worked with Triggers.

Someone can help me?

I own the table usuario and the ocorrencias where the countryside ocorrencias.responsavel reference the ID user’s.

Answer 1

You don’t need a trigger. It is only necessary that you create a foreign key 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

FOREIGN KEY Constraints

The type of Constraint that maintains database Consistency through a Foreign key Relationship.

In free translation:

FOREIGN KEY RESTRICTIONS

The type of constraint that maintains database consistency by applying the relationship between foreign keys.

---

If you really need to perform the check with trigger (what is not recommended) you can do it this way:

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$

Recalling that the SIGNAL only works in the MySQL from the version 5.5.