MYSQL trigger before delete (instead of deleting rather update-CodePudding

I have two tables, one containing candidate information and the other containing the personal information of everyone considered as a person in the database. However, a candidate should not be deleted from the database, their non-personal data should be kept for record. The deletion of a candidate must result in the deletion of his personal data only. So i am trying to write a before delete trigger on the candidate table that takes the id of the candidate we are trying to delete and sets all their personal info to null. When i run the trigger, it only returns a msg saying candidate was deleted however when i check the personal info table, the row for that candidate still has all the information. what might i be doing wrong? here is the code for the trigger:

USE `agence_interim`;

DELIMITER $$

DROP TRIGGER IF EXISTS agence_interim.candidat_BEFORE_DELETE$$
USE `agence_interim`$$
CREATE DEFINER = CURRENT_USER TRIGGER `agence_interim`.`candidat_BEFORE_DELETE` BEFORE DELETE ON `candidat` FOR EACH ROW
BEGIN
DECLARE errorMessage VARCHAR(255);
Update personne SET personne.id_personne = null, 
personne.nom = null, 
personne.prenom = null,
 personne.email = null,
 personne.telephone =null,
 personne.date_naissance =null,
 personne.description = null
WHERE personne.id_personne = OLD.id_personne;

    SET errorMessage = CONCAT('The personal information for candidate number ',
                        OLD.id_personne,
                        ' has been deleted');
                        
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = errorMessage;
            
END$$
DELIMITER ;

CodePudding user response：

I'm afraid this is not possible using a trigger in MySQL.

Using a SIGNAL statement in the trigger body aborts the DELETE action that spawned the trigger, but when the trigger is aborted, this also cancels all subordinate changes executed within the trigger body (and also any actions performed by triggers spawned by those changes, etc.).

There is no support for "instead of" triggers in MySQL. Either all changes succeed, or none of them succeed.

To do what you want, you can't use DELETE from the client.

You must use UPDATE.