Triggers sur insertion avec SQL Server 2005

Version imprimable

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:

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

09/06/2009, 17h08
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 +

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:

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;

Suis pas sûr de moi, mais essaye :

Code:

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 +

09/06/2009, 17h29
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 +
09/06/2009, 17h50
Naail

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

Citation:

Envoyé par SQLpro

Suis pas sûr de moi, mais essaye :

Code:

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 :oops:) si il n'y as pas de données pour dateDeb et dateFin celà ne pose pas un problème?

29/06/2009, 09h58
frais_d_eric

Citation:

Envoyé par 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 +

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 :aie:), 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
29/06/2009, 23h57
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

@++ ;)
30/06/2009, 18h39
SQLpro

Citation:

Envoyé par frais_d_eric

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

Citation:

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 :

Citation:

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 +

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:

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

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

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 +

01/07/2009, 11h45
frais_d_eric

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

Citation:

Envoyé par SQLpro

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
01/07/2009, 15h05
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 +
01/07/2009, 15h23
frais_d_eric

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