Comment mettre à jour une ligne existante dans MySQL après une insertion sans verrouillage de déclencheur ?

Déclencheur MySQL pour la mise à jour d'une ligne existante dans la même table après l'insertion

Énoncé du problème

Quand une nouvelle ligne est insérée dans la table ACCOUNTS, une ligne existante correspondante dans la même table avec pk égal à la colonne edit_on de la ligne nouvellement insérée doit être mise à jour en définissant sa colonne d'état sur « E ». Cependant, une erreur se produit lors de la tentative de mise à jour dans le déclencheur, indiquant que la table est déjà utilisée par l'instruction qui a invoqué le déclencheur.

Solution

En raison de la restriction de mise à jour d'une table dans un déclencheur qui est déjà modifié par l'instruction de déclenchement, une approche alternative est requise.

Stocké Procédure

Créez une procédure stockée qui gère à la fois les opérations d'insertion et de mise à jour en une seule transaction. Les étapes à suivre sont les suivantes :

1. Insérez la nouvelle ligne dans la table ACCOUNTS.
2. Mettez à jour la ligne existante où pk correspond à la colonne edit_on de la ligne nouvellement insérée, en définissant le statut colonne à 'E'.
3. Commitez la transaction pour finaliser la changements.

Syntaxe SQL

CREATE PROCEDURE setEditStatus(IN NEW_pk INT, IN NEW_edit_on INT)
BEGIN
  /* Insert new row into ACCOUNTS table */
  INSERT INTO ACCOUNTS (pk, user_id, edit_on, status)
  VALUES (NEW_pk, /* User ID */, NEW_edit_on, 'A');

  /* Update existing row */
  UPDATE ACCOUNTS
  SET status = 'E'
  WHERE pk = NEW_edit_on;

  /* Commit changes */
  COMMIT;
END;

Déclencheur

Modifier le déclencheur pour appeler la procédure stockée à la place de tenter la mise à jour directement.

DELIMITER $$

DROP TRIGGER IF EXISTS `setEditStatus`$$
CREATE TRIGGER `setEditStatus` AFTER INSERT on ACCOUNTS
FOR EACH ROW BEGIN
  CALL setEditStatus(NEW.pk, NEW.edit_on);
END$$

DELIMITER ;

Cette solution de contournement permet d'effectuer la mise à jour souhaitée sans violer la restriction de mise à jour de la même table dans un 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!