Trigger pour incrémentation relative

Publié le par

Lorsqu’on pratique l’identification relative, il est impossible d’utiliser l’AUTO_INCREMENT pour numéroter la seconde colonne de la clé primaire relativement à la première qui est une clé étrangère. Par exemple, une chaîne hôtelière pourra avoir, dans sa base de données, une table « Hotel » et une table « Chambre » identifiée relativement à « Hotel ». C’est à dire que pour chaque identifiant d’hôtel dans la table « Chambre », le numéro de la chambre repart de 1.

Je décris ci-après la numérotation automatique des chambres, tout en SQL avec MySQL mais sans doute facilement adaptable dans le langage procédural de n’importe quel autre SGBD et pour d’autres besoins tels que les lignes de commande, de facture ou de livraison ou (ce qui m’a amené à étudier ce sujet) les numéros d’exercice d’un examen.

1. Le modèle de données
MCD :
Hotel -1,n—-Avoir—-(1,1)- Chambre

Note : les parenthèses autour des cardinalités 1,1 signifient que l’entité type Chambre est identifiée relativement à Hotel.

Tables :
Hotel (htl_id, htl_nom…)
Chambre (chb_id_hotel, chb_numero…)

Note : On voit ci-dessus que l’identifiant de l’hôtel participe à la clé primaire de la chambre.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Table Hotel
CREATE TABLE Hotel  
(
  htl_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  htl_nom VARCHAR( 50 ) NOT NULL ,
  UNIQUE (htl_nom)
) ENGINE = InnoDB  
CHARACTER SET utf8 COLLATE utf8_general_ci;
 
-- Table Chambre
CREATE TABLE Chambre  
(
  chb_id_hotel INT NOT NULL ,
  chb_numero INT NOT NULL ,
  PRIMARY KEY ( chb_id_hotel , chb_numero )
) ENGINE = InnoDB  
CHARACTER SET utf8 COLLATE utf8_general_ci;
 
ALTER TABLE Chambre  
ADD FOREIGN KEY ( chb_id_hotel )  
  REFERENCES Hotel (htl_id)  
  ON DELETE CASCADE ;

2. Le trigger d’incrémentation relative
Je pars du principe que le numéro de la chambre peut être saisi ou non par l’utilisateur de l’application qui va insérer les nouvelles chambres dans la table. Lorsque le numéro de chambre est laissé vide, c’est le SGBD qui doit numéroter automatiquement la chambre en fonction de l’identifiant de l’hôtel concerné, c’est à dire en affectant le plus haut numéro de chambre de cet hôtel déjà présente dans la table + 1 à la colonne chb_numero.

Commençons par insérer quelques hôtels puis quelques chambres avec leur numéro :

1
2
3
4
5
INSERT INTO Hotel (htl_nom)
VALUES ('Hôtel des flots bleus'), ('Hôtel de la gare'), ('Hôtel du parc');
 
INSERT INTO Chambre (chb_id_hotel, chb_numero)
VALUES (1, 1), (1, 2), (1, 3), (2, 1);

Nous avons les données suivantes dans la table Chambre :
chb_id_hotel / chb_numero
1 / 1
1 / 2
1 / 3
2 / 1

Voici maintenant le trigger :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER //
CREATE TRIGGER tre_chambre_numero  
  BEFORE INSERT ON Chambre  
  FOR EACH ROW
BEGIN
  DECLARE num INTEGER;
  IF (NEW.chb_numero IS NULL OR NEW.chb_numero = 0) THEN
    SET num =  
    (
      SELECT COALESCE(MAX(chb_numero), 0) + 1
      FROM Chambre
      WHERE chb_id_hotel = NEW.chb_id_hotel
    );
    SET NEW.chb_numero = num;
  END IF;
END//

Insérons maintenant quelques chambres aux 3 hôtels sans préciser le numéro :

1
2
INSERT INTO Chambre (chb_id_hotel)
VALUES (1), (2), (3), (3)

Nous avons maintenant les données suivantes dans la table Chambre :
chb_id_hotel / chb_numero
1 / 1
1 / 2
1 / 3
1 / 4 — Nouvelle chambre avec le bon numéro
2 / 1
2 / 2 — Nouvelle chambre avec le bon numéro
3 / 1 — Nouvelle chambre avec le bon numéro
3 / 2 — Nouvelle chambre avec le bon numéro

9 réflexions au sujet de « Trigger pour incrémentation relative »

  3. Bonjour,

    En fonction du SGBD, je vois un petit problème dans l’implémentation de ce trigger.
    lorsqu’on fait le SELECT MAX(chb_numero) if faut s’assurer qu’il n’y a pas une autre transaction qui insère une chambre en même temps pour le même hôtel. Pour cela il faut verrouiller la table. Ou alors pour permettre plus de concurrence, le trigger pourrait simplement verrouiller l’Hotel correspondant afin de sérialiser l’ajout concurrent d’une chambre pour le même hôtel.

    Cordialement,
    Franck.

  4. Le fait de lancer une requête INSERT ne verrouille t-il pas la table pour d’autres insertions tant que l’exécution de la requête n’est pas terminée ?

    Et dans ce cas le trigger ne laisse t-il pas le verrouillage tant qu’il n’a pas fini son exécution ?

    À quel SGBD penses-tu ?

  7. Dans le cas que vous citez, oui je comprends que chaque hôtel commencera par une chambre numérotée.

    Mais dans certains autres projets, certains projets n’ont pas besoin de redémarrer a 1 et peuvent continuer sur l’auto-increment …

    Je me trompe ?

Laisser un commentaire