Algo de de verification dans une procédure pour un INSERT

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:

---

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:

---

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

Code:

---

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 ;)

Citation:

---

Envoyé par orafrance

Code:

---

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:

---

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:

---

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é.

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

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 ;)

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 :)

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 ;)

Citation:

---

Envoyé par 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 ;)

---

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 :?

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.
:arrow: 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:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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.

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

Code:

---

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;

---

Très clair, ca marche nickel

Merci bcp :)