Discussion :

[Sinclair]

Bonjour,

Je ne sais pas si je dois mettre ce post dans SQL ou Oracle mais comme c'est plus un pb d'algo ....

Je suis sous Oracle 8.1.5 et je dois effectuer une verification lors d'un insert. J'utilise donc un trigger on insert.

Voici ma table

RESERVATION(#Centre ,#Client, SemaineDebut, Duree)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
| Centre | Client | SemaineDebut | Duree |
--------------------------------------------------
|Thonon | toto    | 12                   | 5        |

Dans cet exemple, toto a réservé dans le centre thonon a partir de la semaine 12 pendant 5 semaines (donc semaines 12 13 14 15 16)

Il faut que je verrifie que toto ne peux pas faire des réservations qui se chevauchent quelque soit le centre(j'ai schématisé le pb pour que ca soit plus explicite)

pour cela donc je pense faire un trigger

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
CREATE TRIGGER CheckResa
BEFORE INSERT ON RESERVATION
FOR EACH ROW
BEGIN
....
END

et la je bloque un peu sur l'algo.
Qu'est ce qui serait le plus efficace ?

car si toto reserve pour la semaine du 15 pour 3semaines ca ne doit pas passer.

donc sur un INSERT INTO RESERVATION((Centre ,Client, SemaineDebut, Duree) VALUES ('Thonon', 'toto', 15, 3)

il faudrait que je récupère les semaines que je veux insérer ( 15; 16; 17 )

et que pour chaque ligne de la table RESERVATION avec TOTO comme client, je récupère les semaines qu'il a réservé (12; 13; 14; 15; 16) avec un curser par exemple et que je compare les deux liste de semaine ? (c'est possible déja ca ?)

est ce que ca n'est pas un peu lourd ?
Voyez vous une autre méthode ?

Merci d'avance si vous avez une ptite idée

[orafrance]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TRIGGER CheckResa 
BEFORE INSERT ON RESERVATION 
FOR EACH ROW 
PRAGMA autonomous_transaction;
dummy NUMBER;
BEGIN 
SELECT 1 
INTO dummy
FROM DUAL
WHERE NOT EXISTS ( SELECT 1
                            FROM reservation 
                          WHERE :NEW.semaindebut <= semainedebut + duree);
 
EXCEPTION WHEN NO_DATA_FOUND 
THEN
RAISE_APPLICATION_ERROR(-20001,'Erreur de chevauchement');
END;
/

Le SELECT me semble clair

En revanche attention à l'autonomous_transaction qui permet d'éviter une erreur mutating table. Pour mémoire, il est interdit de faire un SELECT sur la table qui porte le trigger (cf. Résolution du problème de la table mutante)

PS : Attention je n'ai pas testé ce code, il convient donc de l'utiliser avec prudence

[Sinclair]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TRIGGER CheckResa 
BEFORE INSERT ON RESERVATION 
FOR EACH ROW 
PRAGMA autonomous_transaction;
dummy NUMBER;
BEGIN 
SELECT 1 
INTO dummy
FROM DUAL
WHERE NOT EXISTS ( SELECT 1
                            FROM reservation 
                          WHERE :NEW.semaindebut <= semainedebut + duree);
 
EXCEPTION WHEN NO_DATA_FOUND 
THEN
RAISE_APPLICATION_ERROR(-20001,'Erreur de chevauchement');
END;
/

Le SELECT me semble clair

En revanche attention à l'autonomous_transaction qui permet d'éviter une erreur mutating table. Pour mémoire, il est interdit de faire un SELECT sur la table qui porte le trigger (cf. Résolution du problème de la table mutante)

PS : Attention je n'ai pas testé ce code, il convient donc de l'utiliser avec prudence

Merci de ta réponse, pour le code ce n'est pas grave, c'est surtout la logique qui m'importe.

seulement, selon ta requete,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT 1 
FROM reservation 
WHERE :NEW.semaindebut <= semainedebut + duree

c'est bien ce que j'avais pensé a faire mais imagine que notre ami toto veuille faire une réservation a partir de la semaine 10 pendant 2 semaines.
donc les semaines 10; 11 ce n'est pas un chevauchement mais le New.Semainedebut = 10 et c'est bien <= a 12 semainedebut + duree

pour rejeter l'insert,
il faut prendre les cas suivant :

(:new.semainedebut < semainedebut + duree) && (:new.semainedebut + duree <semainedebut)

donc c'est formidable, en expliquant, je crois que j'ai compris mon pb, il faut que je rajoute une clause dans le where.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT 1 
FROM reservation 
WHERE :NEW.semaindebut <= semainedebut + duree)
AND :NEW.semaindebut + duree >semainedebut

Penses tu qu'a ce moment la mon raisonnement est bon ?

En tout cas merci de m'avoir guidé.

[Sinclair]

euh ... double post... désolé

[orafrance]

j'ai pas tout compris

mais si la durée est explimée en jour il faut comparer :NEW.semainedebut et :NEW.semainedebut + duree/7 (ou 5 si tu compte que les ouvrés)

En revanche, j'ai fait une coquille

Il faut aussi ajouter :
AND ID != :NEW.ID

pour éviter de comparer la ligne en cours de modif avec elle-même

[Sinclair]

non non c'etait bien semaine, j'ai voulu édité mais je me suis trompé j'ai fait "citer"

mais donc c bien des semaines...

OK pour le ID mais bon j'avais compris

[orafrance]

A part ça j'ai toujours pas compris ton problème existenciel sur le l'ajout de la durée d'un coté ou de l'autre

[Sinclair]

A part ça j'ai toujours pas compris ton problème existenciel sur le l'ajout de la durée d'un coté ou de l'autre

haha
bon je vais essayer de mieux m'exprimer.


Si j'ai bien compris ce que tu me proposes (je me trompe peut etre)

WHERE :NEW.semaindebut <= semainedebut + duree);

Dans ton déclancheur, tu propose de sélectionner les réservation dont la semaine de fin de séjour est supérieure ou égale a la date de début du séjour que je veux insérer.
et si tu en trouves, tu rejettes l'insert ?

mais on peut tres bien avoir une reservation de la semaine 20 pour 3 semaine et insérer un sejour a partir du la semaine 10 pour 3 semaine.

Dans ce cas
:NEW.semaindebut 10 <= semainedebut + duree 23
mais la ligne ne doit pas etre rejetée



mais en fait j'ai pas du bien comprendre

[Admin]

En faite tu dois vérifier 3 choses :

La premiere :
Que ta date de début de séjour ne soit pas comprise entre le début d'un autre séjour et sa fin.

La seconde ;
Que ta date de fin de séjour ne soit pas comprise entre le début d'un autre séjour et sa fin.

Et dernierement que le séjour que tu va inséré n'englobe pas de part et d'autre un autre séjour.
Car dans ce cas la ta date de début n'est pas dans un séjour, et ta date de fin non plus, et pourtant tu chevauche un séjour existant.

Donc je dirait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT 1
     FROM reservation
// Pour vérifier que ta semaine de début ne chevauche pas un séjour
     WHERE (:NEW.semaindebut => semainedebut  AND :NEW.semaindebut <= semainedebut + duree -1)                        
// Pour vérifier que ta semaine de fin ne chevauche pas un séjour 
OR (:NEW.semaindebut + :NEW.duree -1 => semainedebut  AND :NEW.semaindebut + :NEW.duree-1<= semainedebut + duree -1)   
//Pour eviter un chevauchement de part et d'autre              
OR (:NEW.semaindebut <= semainedebut AND :NEW.semaindebut + :NEW.duree => semainedebut + duree)
 
AND ID != :NEW.ID;       // Merci Orafrance :-)

Si cette requete te renvoie 1 lignes c'est que il y a chevauchement.

Les -1 servent car 12 + 5 semaines = 17 et non 16, donc -1 pour obtenir 16

Je reprends ton exemple : Semaine Reservé 12->16

Cas N° 1)
Si tu veux inseré un sejour de on va dire semaine 10->12 (donc 3 semaine de resa), ca doit planter car semaine 12 déjà reservé.

voila la requete :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Where (10 => 12 AND 10<=12+5-1) <- revoie FALSE
OR (10+3-1 => 12 AND 10+3-1<=12+5-1) <- renvoie TRUE
OR (10 <= 12 AND 10+3 => 12+5) <- revoie FALSE

Cas N° 2)
Maintenant faisons pareil avec un séjour semaine 1->5 (5 semaines) ;

Là ca doit marcher.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Where (1 => 12 AND 1<=12+5-1) <- revoie FALSE
OR (1+5 -1=> 12 AND 1+5-1<=12+5-1) <- Renvoie FALSE
OR (1 <= 12 AND 1+5 => 12+5) <- revoie FALSE

Cas N° 3)
Si on chevauche dans l'autre sens (début de séjour).

Donc on va faire une réservation de la semaine 14 à 18 (5 semaines)

Ca doit planter car semaine 14,15,16 déjà reservés :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Where (14=> 12 AND 14<=12+5-1) <- revoie TRUE
OR (14+5-1 => 12 AND 14+5-1<=12+5-1) <- Renvoie FALSE
OR (14 <= 12 AND 14+5 => 12+5) <- revoie FALSE

Cas N° 4)
Reservation après premier séjour Semaine 34 et 35 (2 semaines de resa)
Là ca doit marcher.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Where (34=> 12 AND 34<=12+5-1) <- revoie FALSE
OR(34+2-1 => 12 AND 34+2-1<=12+5-1) <- Renvoie FALSE
OR (34 <= 12 AND 34+2 => 12+5) <- revoie FALSE

Cas N° 5)
Reservation a l'interieur d'un séjour existant
Reservation semaine 13 et 14 par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Where (13=> 12 AND 13<=12+5-1) <- revoie TRUE
OR(13+2-1 => 12 AND 13+2-1<=12+5-1) <- Renvoie TRUE
OR (13 <= 12 AND 13+2 => 12+5) <- revoie FALSE

Cas N° 6)
Et enfin Dernier cas de figure, reservation qui chevauche de part et d'autre une autre resa :
Reservation d'un séjour de la semaine 11 à 18 (8 semaines)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Where (11=> 12 AND 11<=12+5-1) <- revoie FALSE
OR(11+8-1 => 12 AND 11+8-1<=12+5-1) <- Renvoie FALSE
OR (11 <= 12 AND 11+8 => 12+5) <- revoie TRUE

Je sais pas si j'ai été clair.

[Admin]

note à moi même : tu peux virer les -1 en jouant avec les strictements < et >

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT 1
     FROM reservation
     WHERE (:NEW.semaindebut => semainedebut  AND :NEW.semaindebut <  semainedebut + duree )                        
OR (:NEW.semaindebut + :NEW.duree  > semainedebut  AND :NEW.semaindebut + :NEW.duree <= semainedebut + duree )     
OR (:NEW.semaindebut <= semainedebut AND :NEW.semaindebut + :NEW.duree => semainedebut + duree)
 
AND ID != :NEW.ID;

[Sinclair]

Très clair, ca marche nickel

Merci bcp