Discussion :

[Cecolympe]

Bonjour,

J'ai été redirigée sur ce site par un membre d'un Site qui m'a assuré que cette communauté serait plus à même de répondre à mes questions.

Un projet pour un cours de Base de Données me donne du fil à retordre. En effet, nous n'avons vu au cours que les requêtes, et pas du tout la syntaxe des TRIGGER qui sont pourtant bien utiles pour notre projet assez complexe!
Après avoir pioché ici et là des réponses sur l'un ou l'autre tutoriel SQL sur les TRIGGER, les conditions, la gestion des dates,..., voilà le code que j'ai réussi à concocter:

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
delimiter //
 
CREATE TRIGGER visits_insert_t
BEFORE INSERT ON Visits V
FOR EACH ROW
BEGIN
    UPDATE Rollercoasters R
    SET attendance_rate = AVG (visitnbr AS (SELECT COUNT(*) 
					   FROM Visits V1, Rollercoasters R1 
					   WHERE V1.id_attraction=R1.id_attraction 
					   GROUP BY DAY (V1.Entrance_time) AS visitsday))
    WHERE R.id_attraction=NEW.id_attraction; /* A chaque ajout d'une visite, la moyenne journalière des visites est calculée 	et mise dans la table "Rollercoaster" là où l'ID correspond*/
 
    UPDATE Manege M
    SET attendance_rate = AVG (visitnbr AS (SELECT COUNT(*) 
					   FROM Visits V1, Manege M1 
					   WHERE V1.id_attraction=M1.id_attraction 
					   GROUP BY DAY (V1.Entrance_time) AS visitsday))
    WHERE M.id_attraction=NEW.id_attraction; /* Idem précédente pour la table "Manège" Note: un même id ne se retrouvera pas dans les 2 tables*/
 
    CASE WHEN(AttractionTime IS NULL) THEN (UPDATE Queue Q1
						SET NEW.Occupation = OLD.Occupation+1
						WHERE Q1.id_attraction=NEW.id_attraction;/* Ajoute 1 à la file d'attente correspondante lorsque la condition est vérifiée*/
 
						UPDATE Queue Q
						SET Timeperperson = AVG(SELECT (DATEDIFF (mi,NEW.AttractionTime,NEW.QueueEntranceTime)/Q1.Occupation)
										    FROM Visits V1, Queue Q1
										    WHERE V1.id_attraction=Q1.id_attraction)
						WHERE R.id_attraction=NEW.id_attraction; /* Mets à jour le temps d'attente moyen par personne présente dans la file d'attente*/
											)
		ELSE END; 
 
END//
 
CREATE TRIGGER visits_update_t
BEFORE UPDATE ON Visits V
FOR EACH ROW
BEGIN
	CASE WHEN(OLD.AttractionTime IS NULL AND NEW.AttractionTime IS NOT NULL) 
	THEN (UPDATE Queue Q
	         SET NEW.Occupation = OLD.Occupation-1
	         WHERE Q.id_attraction=V.id_attraction; /* Retire 1 à la file d'attente correspondante lorsque la condition est vérifiée*/ 
	         )
	ELSE END;
 
END//
 
delimiter ;

En gros, ça servirait à modifier des statistiques au sein d'autres tables, et d'incrémenter/décrémenter une file d'attente lorsque des visites ont lieu dans un parc d'attraction.

C'est surtout au niveau de la structure conditionnelle que je ne me sens pas à l'aise, le ELSE est-il nécessaire même si aucune action n'est demandé dans un autre cas que celui évoqué (je n'ai pas trouvé de cas similaire dans les tutos consultés)?
D'une manière générale, ai-je utilisé correctement le langage SQL?

Merci d'avance pour les remarques que vous pourrez me faire!

Cecolympe

EDIT: Bon, j'ai déjà trouvé beaucoup d'erreurs par moi-même et grâce à l'aide de la communauté des Zéros, je mettrai mon code pré-corrigé dans un autre post.

[CinePhil]

Non le ELSE n'est pas obligatoire dans un CASE.

[Cecolympe]

Voilà ce que j'ai obtenu grâce aux conseils, c'est un peu moins horrible...

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
CREATE TRIGGER visits_insert_t
BEFORE INSERT ON Visits
FOR EACH ROW
BEGIN
 
    UPDATE Rollercoasters R, (
    	SELECT AVG(nbVisit) AS visParJour, id_attraction
		FROM (
			SELECT Count(*) AS nbVisit, R1.id_attraction, DAY(V1.QueueEntranceTime) AS jours
			FROM Visits V1
				JOIN Rollercoasters R1 
					ON V1.id_attraction = R1.id_attraction
			GROUP BY R1.id_attraction, jours
		) AS td
		GROUP BY id_attraction
    ) AS moyenneParAttraction
    SET attendance_rate = visParJour
    WHERE R.id_attraction = moyenneParAttraction.id_attraction;
 
    UPDATE Manege M, (
    	SELECT AVG(nbVisit) AS visParJour, id_attraction
		FROM (
			SELECT Count(*) AS nbVisit, M1.id_attraction, DAY(V1.QueueEntranceTime) AS jours
			FROM Visits V1
				JOIN Manege M1 
					ON V1.id_attraction = M1.id_attraction
			GROUP BY M1.id_attraction, jours
		) AS td
		GROUP BY id_attraction
    ) AS moyenneParAttraction
    SET attendance_rate = visParJour
    WHERE M.id_attraction = moyenneParAttraction.id_attraction;
 
	IF (NEW.AttractionTime IS NULL)
	THEN
		UPDATE Queue
		SET Occupation = Occupation+1
		WHERE id_attraction = NEW.id_attraction;
 
		UPDATE Queue Q, (
		SELECT AVG(Attente) AS AttenteMoyenne, id_attraction
		FROM (
			SELECT (DATEDIFF (mi,V2.AttractionTime,V2.QueueEntranceTime)/Q1.Occupation) AS Attente
			FROM Visits V2
				JOIN Queue Q1
					ON V2.id_attraction = Q1.id_attraction)
			WHERE AttractionTime IS NOT NULL		
		) AS AttenteMoyenneParAttraction
		SET Timeperperson=AttenteMoyenneParAttraction
		WHERE Q.id_attraction=AttenteMoyenneParAttraction.id_attraction;
 
	END IF;
END
 
CREATE TRIGGER visits_update_t
BEFORE UPDATE ON Visits
FOR EACH ROW
BEGIN
	IF (OLD.AttractionTime IS NULL AND NEW.AttractionTime IS NOT NULL)
	THEN
		UPDATE Queue
	 	SET Occupation = Occupation - 1
	 	WHERE id_attraction = NEW.id_attraction;
	END IF;
END

[SQLpro]

De manière générale :
1) ne faites pas de trigger BEFORE, faites du AFTER, car rien ne dit que la transaction ne va pas être annulée par une contrainte, et mieux vaut que le ROLLBACK ait le moins de travail à faire
2) faites des triggers ensemblistes en non ligne à ligne. Ce mode FOR ECH ROW, nuit gravement aux performances, alors qu'il suffit d'ajouter ou de diminuer de COUNT(*) sur la pseudo table NEW.

Pour bien se former voir les cours SQL.

A +