Discussion :

[Naail]

Bonjour,

Je crée une petite application permettant de gérer les réservations d'un hôtel dans le cadre de mes études.
Je souhaite crée un trigger sous SQL server permettant de vérifier lors de l'enregistrement d'un réservation si la date de début choisi pour une chambre ne correspond pas déjà à la date d'une autre réservation. En d'autres termes empêcher d'enregistrement une réservation si la chambre est déjà réservé.

Merci de vos réponses.

ps: voici mes tables:

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
create table client
(numCli char(36) not null default newid(),
nomCli varchar(30),
pnomCli varchar(30),
rueCli varchar(40),
vilCli varchar(40),
cpCli char(5),
telCli char(10)
constraint pkCli primary key(numCli));
 
 
create table categorie
(codCat char(4)not null,
libelle varchar(30)
constraint pkCat primary key(codCat));
 
 
create table chambre
(numCham int not null ,
prix money,
codCat  char(4),
constraint pkCha primary key(numCham),
constraint fkCha foreign key (codCat) references categorie(codCat));
 
create table reservation
( numRes char(36) not null default newid(),
dateRes datetime,
dateDeb datetime,
dateFin datetime,
numCli char(36),
numCham int,
constraint pkRes primary key(numRes),
constraint fkRes1 foreign key (numCli) references Client(numCli),
constraint fkRes2 foreign key (numCham) references Chambre(numCham));

[SQLpro]

Il serait beaucoup plus performant de mettre une contrainte CHECK plutôt qu'une trigger. EN effet, les triggers agissent après l'insertion et leur code est exécuté au sein de la transaction. En revanche les contraintes agissent avant l'insertion.

Pour ce faire vous devez passer par une fonction qui renverra 0 ou 1 s'il y a "overlaping" d'intervalle.

Pour l'overlaping d'intervalle, inspirez vous de l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/gestiontemps/#L1.2.2

A +

[jero44]

Entierrement d'accord avec SQLPro une solution s'appuyant sur les CI sera beaucoup plus prerformante que celle qui utilise les trigger.
Néanmoins voici une solution possible sous la forme de déclencheur (pensez également à gérer les UPDATE)

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
 
create trigger iof_ins_resa on reservation
instead of insert as
begin
  declare @nbChevauchement int;
  select @nbChevauchement=count(*)
  from inserted i inner join reservation r
  on r.numCham = i.numCham 
  where i.dateDeb >= r.dateDeb and i.dateDeb<r.dateFin;
  if (@nbchevauchement =0) begin
   insert into reservation select * from inserted;
  end else begin
	raiserror('Reservation impossible',15,1);
  end;
end;

[SQLpro]

Suis pas sûr de moi, mais essaye :

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
19
20
21
22
23
24
25
26
CREATE FUNCTION dbo.RECOUPE (@DD DATETIME, @DF DATETIME, @CH INT)
RETURNS BIT
AS
BEGIN
IF @DD IS NULL OR @DF IS NULL RETURN NULL;
IF @DD > @DF RETURN NULL;
RETURN 
(SELECT 
   CASE 
      WHEN (@DD > dateDeb 
             AND (@DD < dateDeb OR @DF < dateFin))
         OR (dateDeb > @DD
             AND (dateDeb < @DF OR dateFin < @DF)) 
         OR (@DD = dateDeb 
             AND (@DF IS NOT NULL AND dateFin IS NOT NULL))	 THEN 1
      ELSE 0
   END
 FROM reservation
 WHERE numCham = @CH);    
END;   
GO
 
ALTER TABLE reservation
   ADD CONSTRAINT CK_OVERLAPS 
   CHECK (dbo.RECOUPE (dateDeb, dateFin, numCham) = 0);
GO

A +

[SQLpro]

Hou, quel horrible trigger !!!

NON, ne l'utilisez pas... Il part d'un principe faux : que vous n'allez jamais faire de modif autre qu'une seule ligne à la fois.
bref, il rendra votre base incohérente !!!!

Sachez qu'un trigger SQL Server est toujours ensembliste et que par conséquent l'apparition de variable dans son code est une hérésie car ne traitera qu'une seule ligne de l'ordre INSERT ou UPDATE !

A +

[Naail]

Je vous remercie beaucoup de m'avoir aidé je vais regarder du coté des CI

[Naail]

Suis pas sûr de moi, mais essaye :

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
19
20
21
22
23
24
25
26
CREATE FUNCTION dbo.RECOUPE (@DD DATETIME, @DF DATETIME, @CH INT)
RETURNS BIT
AS
BEGIN
IF @DD IS NULL OR @DF IS NULL RETURN NULL;
IF @DD > @DF RETURN NULL;
RETURN 
(SELECT 
   CASE 
      WHEN (@DD > dateDeb 
             AND (@DD < dateDeb OR @DF < dateFin))
         OR (dateDeb > @DD
             AND (dateDeb < @DF OR dateFin < @DF)) 
         OR (@DD = dateDeb 
             AND (@DF IS NOT NULL AND dateFin IS NOT NULL))	 THEN 1
      ELSE 0
   END
 FROM reservation
 WHERE numCham = @CH);    
END;   
GO
 
ALTER TABLE reservation
   ADD CONSTRAINT CK_OVERLAPS 
   CHECK (dbo.RECOUPE (dateDeb, dateFin, numCham) = 0);
GO

A +

J'ai une petite question (surement bête ) si il n'y as pas de données pour dateDeb et dateFin celà ne pose pas un problème?

[frais_d_eric]

Hou, quel horrible trigger !!!

NON, ne l'utilisez pas... Il part d'un principe faux : que vous n'allez jamais faire de modif autre qu'une seule ligne à la fois.
bref, il rendra votre base incohérente !!!!

Sachez qu'un trigger SQL Server est toujours ensembliste et que par conséquent l'apparition de variable dans son code est une hérésie car ne traitera qu'une seule ligne de l'ordre INSERT ou UPDATE !

A +

Bonjour à tous,
il y a quelque chose que je ne comprends pas. Je suis conscient du fait que la table inserted peut contenir plusieurs lignes. Mais si je comprends bien (ce dont je ne suis pas sûr ), la variable de ce trigger porte sur un count et ne sert qu'à évaluer un comptage. En quoi est-ce génant pour l'insert qui suit (si on passe sur l'*) ?

Merci bcp

[elsuket]

Bonjour,

INSERTED et DELETED sont deux tables virtuelles (qui font d'ailleurs que les triggers coûtent cher en ressources) qui ont strictement la même structure que la table cible de la mise à jour.

=> INSERTED contient :

- Lors d'un INSERT, les valeurs qui viennent (AFTER) ou que l'on veut stocker dans la table (INSTEAD OF)

- Lors d'un UPDATE, les nouvelles valeurs prises par les lignes affectées par la mise à jour

=> DELETED contient
- Lors d'une DELETE, les valeurs des lignes qui viennent d'être (AFTER) ou que l'on souhaite supprimer (INSTEAD OF)

- Lors d'un UPDATE, les anciennes valeurs des lignes qui sont affectées par la mise à jour

Il ne s'agit donc aucunement d'un comptage, et vous pouvez spécifier des jointures entre la table cible de la mise à jour et ces deux tables virtuelles.
Sur un UPDATE, vous pouvez également réaliser une jointure entre ces deux tables virtuelles.

Pour voir les problèmes que peuvent poser un trigger non-ensembliste, vous pouvez jeter un œil sur ce billet

@++

[SQLpro]

Bonjour à tous,
il y a quelque chose que je ne comprends pas. Je suis conscient du fait que la table inserted peut contenir plusieurs lignes. Mais si je comprends bien (ce dont je ne suis pas sûr ), la variable de ce trigger porte sur un count et ne sert qu'à évaluer un comptage. En quoi est-ce génant pour l'insert qui suit (si on passe sur l'*) ?

Merci bcp

Entre l'exécution de cette requête :

SELECT @nbChevauchement=count(*)
FROM inserted i INNER JOIN reservation r
ON r.numCham = i.numCham
WHERE i.dateDeb >= r.dateDeb AND i.dateDeb<r.dateFin;

et la seconde partie de votre code :

IF (@nbchevauchement =0) begin
INSERT INTO reservation SELECT * FROM inserted;
end else begin
raiserror('Reservation impossible',15,1);
end;

Quelqu'un d'autre a pu faire une mise à jour en // (ajout, modification, suppression). De ce fait l'intégrité de vos données n'est pas respecté et vous n'êtes donc pas ACID !

Si vous vouliez faire cela il faudrait piloter l'isolation de cette transaction au niveau SERIALIZABLE, ce qui serait catastrophique à la fois pour les performances comme pour la montée en charge...

A +

[elsuket]

Je ne reviendrai pas sur le fait que le contrainte de domaine sera bien plus efficace;
néanmoins le trigger suivant n'aurait-il pas rempli le même office si l'on fait abstraction des performances ?

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
19
20
21
22
23
24
CREATE TRIGGER iof_ins_resa
	ON dbo.reservation
INSTEAD OF INSERT
AS
BEGIN
	IF EXISTS
	(
		SELECT *
		FROM inserted AS I
		INNER JOIN dbo.reservation AS R
			ON R.numCham = I.numCham
			AND I.dateDeb >= R.dateDeb
			AND I.dateDeb < R.dateFin
	)
	BEGIN
		RAISERROR('Reservation impossible', 15, 1)
	END
	ELSE
	BEGIN
		INSERT INTO dbo.reservation
		SELECT mesColonnes
		FROM inserted
	END
END

@++

[SQLpro]

Même problème !!!!
Il faudrait faire un seul INSERT INTO avec la condition dans l'INSERT. Quelque chose comme :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
INSERT INTO dbo.reservation
        SELECT mesColonnes
        FROM inserted
WHERE NOT EXISTS
    (
        SELECT *
        FROM inserted AS I
        INNER JOIN dbo.reservation AS R
            ON R.numCham = I.numCham
            AND I.dateDeb >= R.dateDeb
            AND I.dateDeb < R.dateFin
    )

Et tester derrière lsi le nombre de ligne est 0 alors générer un message d'info disant que la réservation n'a pas été effectuée....

Personnellement j'ai donné un exercice à mes étudiant au CNAM sur ce sujet pour ce qui concerne la gestion des transactions. La plupart ce sont planté en faisant une transactions. Quelques uns on pensé à la faire avec un niveau SERIALIZABLE, mais un seul à pejnser à combiner l'INSERT.

A +

[frais_d_eric]

merci à tous pour ces réponses.
J'insiste encore un peu :

Quelqu'un d'autre a pu faire une mise à jour en // (ajout, modification, suppression). De ce fait l'intégrité de vos données n'est pas respecté et vous n'êtes donc pas ACID !

Si vous vouliez faire cela il faudrait piloter l'isolation de cette transaction au niveau SERIALIZABLE, ce qui serait catastrophique à la fois pour les performances comme pour la montée en charge...

A +

... pardonnez mon ignorance, mais ceci qui reviendrait à dire que si quelqu'un d'autre fait une insertion en parallèle, ça ne redéclenchera pas le trigger en parallèle (avec une nouvelle table insert ayant pour scope cette "instance du trigger") ?

merci bcp

[SQLpro]

Ce serait une autre transaction, donc une autre exécution du trigger, mais pour la perméabilité des transactions entre les différents utilisateurs cela dépend du niveau d'isolation.

Lisez les articles que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/sqlaz/techniques/#L1

A +

[frais_d_eric]

Un grand merci pour la qualité de toutes ces réponses.
Je pense (j'espère aussi ) ne pas être le seul que ça aide beaucoup.