Comment puis-je mettre à jour la même table dans un déclencheur MySQL après une opération INSERT ?

Limitations des déclencheurs dans MySQL : mise à jour de la même table

Les déclencheurs MySQL fournissent un mécanisme pour effectuer des actions automatisées basées sur des événements de base de données. Cependant, il existe des limites à l'utilisation de déclencheurs pour mettre à jour la même table à l'origine de l'événement. Cela est dû à la restriction qui empêche un déclencheur de modifier une table déjà utilisée par l'instruction qui a invoqué le déclencheur.

Énoncé du problème

Dans le document fourni Dans ce scénario, la tâche consiste à mettre à jour une ligne spécifique dans une table ACCOUNTS lorsqu'une nouvelle ligne est insérée dans la même table. Cependant, la mise à jour doit être effectuée sur une ligne existante où la clé primaire (pk) correspond à la valeur edit_on de la nouvelle ligne.

Implémentation du déclencheur

Le déclencheur proposé la syntaxe vise à répondre à cette exigence :

DELIMITER $$

DROP TRIGGER IF EXISTS `setEditStatus`$$
CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTS
FOR EACH ROW BEGIN
    update ACCOUNTS set status='E' where ACCOUNTS.pk = NEW.edit_on ;
END$$

DELIMITER ;

Exécution Erreur

Malheureusement, l'exécution de ce déclencheur entraîne une erreur en raison de la restriction mentionnée précédemment : "Impossible de mettre à jour la table COMPTES... déjà utilisée par l'instruction qui a invoqué ce déclencheur."

Solution de contournement utilisant une procédure stockée

Étant donné que les déclencheurs ne peuvent pas être utilisés à cette fin, une approche alternative consiste à créer une procédure stockée. Une procédure stockée permet d'effectuer plusieurs opérations au sein d'une seule transaction, y compris l'insertion dans la table ACCOUNTS et la mise à jour de la ligne existante.

Ce qui suit est un exemple de procédure stockée qui accomplit la tâche :

CREATE PROCEDURE `updateAccountStatus` (IN NEW_ROW_ID INT)
BEGIN
    -- Insert into the ACCOUNTS table
    INSERT INTO ACCOUNTS (user_id, edit_on, status)
    VALUES (NEW.user_id, NEW.edit_on, 'A');

    -- Update the existing row
    UPDATE ACCOUNTS SET status = 'E' WHERE pk = NEW_ROW_ID;

    -- Commit the changes
    COMMIT;
END

En appelant la procédure stockée avec l'ID de la ligne nouvellement insérée, les opérations d'insertion et de mise à jour peuvent être exécutées en une seule transaction, en contournant la limitation du déclencheur.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!