Un petit défi ?

Version imprimable

Un petit défi ?

Bonjour à tous,

J'ai un rendus de projet à faire, et je suis un peu perdu pour ce qui est des CHECK postgre, j'ai bien pigé le truc pour les CHECK simple sur une seul table, mais je n'arrive pas malgré mes recherches à réaliser un CHECK entre deux tables différentes.

Voici le script de la base sql "Camping" que j'ai réalisé :

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
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
CREATE TABLE camping.facture ( 
	id_facture bigserial NOT NULL,
	id_location bigserial NOT NULL,
	montant_facture float8 NOT NULL,
CONSTRAINT Pk_facture PRIMARY KEY ( id_facture )
 ) ;
 
CREATE INDEX idx_facture ON camping.facture ( id_location );
 
CREATE TABLE camping.option_location ( 
	id_location bigserial NOT NULL,
	id_option bigserial NOT NULL,
	quantite bigserial NOT NULL,
	debut_option date NOT NULL,
	fin_option date NOT NULL,
CONSTRAINT Pk_option_location PRIMARY KEY ( id_option )
 ) ;
 
ALTER TABLE camping.option_location ADD CONSTRAINT Ck_5 CHECK ( debut_option<fin_option ) ;
 
CREATE INDEX idx_option_location ON camping.option_location ( id_location );
 
CREATE TABLE camping.location ( 
	id_location bigserial NOT NULL,
	id_client bigserial NOT NULL,
	debut_loc date NOT NULL,
	fin_loc date NOT NULL,
	num_emplacement bigserial NOT NULL,
	arrhes bigserial NOT NULL,
	annule int2 NOT NULL,
CONSTRAINT Pk_location PRIMARY KEY ( id_location )
 ) ;
 
ALTER TABLE camping.location ADD CONSTRAINT Ck_4 CHECK ( debut_loc<fin_loc ) ;
 
CREATE INDEX idx_location ON camping.location ( id_client );
 
CREATE INDEX idx_location_0 ON camping.location ( num_emplacement );
 
CREATE TABLE camping.tarification_option ( 
	id_saison bigserial NOT NULL,
	id_option bigserial NOT NULL,
	prix_journee float8 NOT NULL,
CONSTRAINT Pk_tarification_option PRIMARY KEY ( id_saison )
 ) ;
 
CREATE INDEX idx_tarification_option ON camping.tarification_option ( id_option );
 
CREATE TABLE camping.tarification_emplacement ( 
	id_saison bigserial NOT NULL,
	id_type_emplacement bigserial NOT NULL,
	prix_journee float8 NOT NULL,
CONSTRAINT Pk_tarification_emplacement PRIMARY KEY ( id_saison )
 ) ;
 
CREATE INDEX idx_tarification_emplacement ON camping.tarification_emplacement ( id_type_emplacement );
 
CREATE TABLE camping.saison ( 
	id_saison bigserial NOT NULL,
	descriptif_saison varchar( 200 ) NOT NULL,
	debut_saison date NOT NULL,
	fin_saison date NOT NULL,
	annee int8 NOT NULL,
CONSTRAINT Pk_saison PRIMARY KEY ( id_saison )
 ) ;
 
ALTER TABLE camping.saison ADD CONSTRAINT Ck_1 CHECK ( debut_saison < fin_saison ) ;
 
ALTER TABLE camping.saison ADD CONSTRAINT ck_6 CHECK ( date_part('year',debut_saison)=annee ) ;
 
ALTER TABLE camping.saison ADD CONSTRAINT ck_7 CHECK ( date_part('year',fin_saison)=annee ) ;
 
CREATE INDEX idx_saison ON camping.saison ( annee );
 
CREATE TABLE camping.annee ( 
	annee int8 NOT NULL,
	ouverture date NOT NULL,
	fermeture date NOT NULL,
CONSTRAINT Pk_annee PRIMARY KEY ( annee )
 ) ;
 
ALTER TABLE camping.annee ADD CONSTRAINT Ck_0 CHECK ( ouverture < fermeture ) ;
 
CREATE TABLE camping.option_type_emplacement ( 
	id_option bigserial NOT NULL,
	id_type_emplacement bigserial NOT NULL,
	quantite bigserial NOT NULL,
CONSTRAINT Pk_option_type_emplacement PRIMARY KEY ( id_option )
 ) ;
 
CREATE INDEX idx_option_type_emplacement ON camping.option_type_emplacement ( id_type_emplacement );
 
CREATE TABLE camping.option ( 
	id_option bigserial NOT NULL,
	description_option bigserial NOT NULL,
	quantite_max bigserial NOT NULL,
CONSTRAINT Pk_option PRIMARY KEY ( id_option )
 ) ;
 
CREATE TABLE camping.emplacement ( 
	num_emplacement bigserial NOT NULL,
	id_type_emplacement bigserial NOT NULL,
CONSTRAINT Pk_emplacement PRIMARY KEY ( num_emplacement )
 ) ;
 
CREATE INDEX idx_emplacement ON camping.emplacement ( id_type_emplacement );
 
CREATE TABLE camping.type_emplacement ( 
	id_type_emplacement bigserial NOT NULL,
	description_type_emplacement varchar( 200 ) NOT NULL,
CONSTRAINT Pk_type_emplacement PRIMARY KEY ( id_type_emplacement )
 ) ;
 
CREATE TABLE camping.client ( 
	id_client bigserial NOT NULL,
	prenom varchar( 150 ) NOT NULL,
	nom varchar( 150 ) NOT NULL,
	adresse varchar( 150 ) NOT NULL,
	ville varchar( 150 ) NOT NULL,
	cp varchar( 10 ) NULL,
	tel varchar( 15 ) NULL,
CONSTRAINT Pk_client PRIMARY KEY ( id_client )
 ) ;
 
ALTER TABLE camping.facture ADD CONSTRAINT Fk_facture FOREIGN KEY ( id_location ) REFERENCES camping.location( id_location ) ON DELETE CASCADE ;
 
ALTER TABLE camping.option_location ADD CONSTRAINT Fk_option_location FOREIGN KEY ( id_location ) REFERENCES camping.location( id_location ) ON DELETE CASCADE ;
 
ALTER TABLE camping.option_location ADD CONSTRAINT Fk_option_location_0 FOREIGN KEY ( id_option ) REFERENCES camping.option( id_option ) ON DELETE CASCADE ;
 
ALTER TABLE camping.location ADD CONSTRAINT Fk_location FOREIGN KEY ( id_client ) REFERENCES camping.client( id_client ) ON DELETE CASCADE ;
 
ALTER TABLE camping.location ADD CONSTRAINT Fk_location_0 FOREIGN KEY ( num_emplacement ) REFERENCES camping.emplacement( num_emplacement ) ON DELETE CASCADE ;
 
ALTER TABLE camping.tarification_option ADD CONSTRAINT Fk_tarification_option FOREIGN KEY ( id_saison ) REFERENCES camping.saison( id_saison ) ON DELETE CASCADE ;
 
ALTER TABLE camping.tarification_option ADD CONSTRAINT Fk_tarification_option_0 FOREIGN KEY ( id_option ) REFERENCES camping.option( id_option ) ON DELETE CASCADE ;
 
ALTER TABLE camping.tarification_emplacement ADD CONSTRAINT Fk_tarification_emplacement FOREIGN KEY ( id_saison ) REFERENCES camping.saison( id_saison ) ON DELETE CASCADE ;
 
ALTER TABLE camping.tarification_emplacement ADD CONSTRAINT Fk_tarification_emplacement_0 FOREIGN KEY ( id_type_emplacement ) REFERENCES camping.type_emplacement( id_type_emplacement ) ON DELETE CASCADE ;
 
ALTER TABLE camping.saison ADD CONSTRAINT Fk_saison FOREIGN KEY ( annee ) REFERENCES camping.annee( annee ) ON DELETE CASCADE ;
 
ALTER TABLE camping.option_type_emplacement ADD CONSTRAINT Fk_option_type_emplacement FOREIGN KEY ( id_option ) REFERENCES camping.option( id_option ) ON DELETE CASCADE ;
 
ALTER TABLE camping.option_type_emplacement ADD CONSTRAINT Fk_option_type_emplacement_0 FOREIGN KEY ( id_type_emplacement ) REFERENCES camping.type_emplacement( id_type_emplacement ) ON DELETE CASCADE ;
 
ALTER TABLE camping.emplacement ADD CONSTRAINT Fk_emplacement FOREIGN KEY ( id_type_emplacement ) REFERENCES camping.type_emplacement( id_type_emplacement ) ON DELETE CASCADE ;

Je voudrais créer une CHECK entre la table 'option_location' et 'option' sur les champs 'quantite' et 'quantite_max', ceci dans le but d'éviter qu'une quantité saisie soit supérieur à 'quantite_max'

j'ai essayé un truc du genre:

Code:

1
2
 
ALTER TABLE camping.option_location ADD CONSTRAINT ck_10 CHECK (quantite <= option.quantite_max);

mais ça ne fonctionne pas et c'est pour cela que je viens vers vous ;)

Merci par avance pour votre aide

voici le MCD de la base :

http://imagik.fr/uploads/401417

15/04/2011, 15h25
vmolines

Normal que vous soyez perdu car une contrainte check ne peut contrôler que les données d'une ligne.

Si vous voulez faire un contrôle qui a une portée supérieure (ex : d'une ligne par rapport à une autre ou par rapport aux lignes d'une autre table), il faut passer par un trigger.

Citation:

Envoyé par vmolines

Normal que vous soyez perdu car une contrainte check ne peut contrôler que les données d'une ligne.

Ce que vous dites est faux !!!!

Démonstration...

tables pour l'exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE T_CLIENT_CLI (CLI_ID INT NOT NULL PRIMARY KEY, CLI_NOM VARCHAR(32) NOT NULL, CLI_REMISE_MAX FLOAT NOT NULL DEFAULT 0 CHECK (CLI_REMISE_MAX BETWEEN 0.00 AND 100.00)); CREATE TABLE T_FACTURE_FCT (FCT_ID INT NOT NULL PRIMARY KEY, CLI_ID INT NOT NULL, FCT_DATE DATE NOT NULL DEFAULT CURRENT_DATE, FCT_REMISE FLOAT NOT NULL DEFAULT 0 CHECK (FCT_REMISE BETWEEN 0.00 AND 100.00), CONSTRAINT FK FOREIGN KEY (CLI_ID) REFERENCES T_CLIENT_CLI (CLI_ID));
But de la contrainte : faire en sorte que la remise sur facture ne dépasse pas la remise maximale prévue pour le client...

Création d'une fonction de vérification :
Code:

1 2 3 4 5 6 7 8 9 CREATE OR REPLACE FUNCTION F_CHECK_MAX_REMISE (client_id int, remise float) RETURNS boolean AS $$ SELECT CASE WHEN CLI_REMISE_MAX < $2 THEN false ELSE true END FROM T_CLIENT_CLI WHERE CLI_ID = $1; $$ LANGUAGE 'sql';
Création de la contrainte à l'aide de la fonction :
Code:

1 2 ALTER TABLE T_FACTURE_FCT ADD CONSTRAINT K CHECK (F_CHECK_MAX_REMISE (CLI_ID, FCT_REMISE));
insertion d'une client test :

Code:

INSERT INTO T_CLIENT_CLI VALUES (1, 'DUPONT', 15);

Insertion d'une "bonne" facture :

Code:

INSERT INTO T_FACTURE_FCT VALUES (101, 1, '2011-04-15', 12.0)

Insertion d'une "mauvaise" facture :

Code:

INSERT INTO T_FACTURE_FCT VALUES (102, 1, '2011-04-15', 35.0)

Message d'erreur :

Citation:

ERREUR: la nouvelle ligne viole la contrainte de vérification « t_facture_fct » de la relation « k »
********** Erreur **********
ERREUR: la nouvelle ligne viole la contrainte de vérification « t_facture_fct » de la relation « k »
État SQL :23514

Ok, c'est pas très explicite !!!

Bref, apprenez SQL. Mon livre, comme mon site web peut vous être utile !!!
Voici ce qui figure en toutes lettres dans mon livre (2e édition, page 70/71) :
"

NOTA
La création de contraintes de validation complexes nécessite de bien connaître les possibilités offertes par l'ordre SQL SELECT d'extraction de données ainsi que l'écriture des UDF (User Define Function).

Exemple, contrainte de validation faisant référence appel à des UDF
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE S_COM.T_PATIENT_PTN (PTN_NUMERO_INSEE CHAR(13), PTN_CLEF_INSEE CHAR(2), PTN_NOM CHAR(32), PTN_PRENOM VARCHAR(25), CONSTRAINT CK_PTN_NNI CHECK (S_ROUTINES.F_VALIDE_NNI(PTN_NUMERO_INSEE, PTN_CLEF_INSEE)), CONSTRAINT CK_PTN_NOM CHECK (S_ROUTINES.F_VALIDE_NOM(PTN_NOM, 'MAJUSCULES')), CONSTRAINT CK_PTN_PRENOM CHECK (S_ROUTINES.F_VALIDE_NOM(PTN_NOM, 'MAJMIN')));
Cet exemple présente trois contraintes de validation faisant appel à des fonctions utilisateur (UDF), c'est à dire à des routines spécifiquement écrite pour un traitement particulier.
A la lecture de la définition de cette table on peut en déduire que
• la cohérence du numéro national d'identité (vulgairement appelé numéro de sécurité sociale) est vérifié par la routine F_VALIDE_NNI du schéma S_ROUTINES;
• le nom du patient doit respecter certaines règles (par exemple commencer par une lettre et pour le reste, n'être composé que des caractères A à Z et leurs déclinaisons avec accents, ainsi que les caractères espace, tiret, apostrophe) et figurer en majuscule, ceci étant obtenir à l'aide de la routine F_VALIDE_NOM du schéma S_ROUTINES;
• le prénom du patient obéira aux même règles que ci dessus mais en autorisant un mélange de majuscule et minuscule (par exemple seule les premières lettres de chaque partie du prénom seront en majuscules et les autres en minuscule).
"

A +

19/04/2011, 11h51
pierrelm

Malin ?

Est-ce qu'il est suffisamment malin pour rendre la main directement sur une requête du type :

Code:

SELECT * FROM T_FACTURE_FCT WHERE CLI_ID=1 AND FCT_REMISE=35.0

Si non, est-il possible de le rendre intelligent ?
19/04/2011, 11h55
SQLpro

Rare sont les SGBDR qui savent optimiser les plans de requêtes par les contraintes. Pas en l'état actuel de PG. SQL Server sait faire cela... Lisez l'article de MikeDavem sur le sujet :
http://blog.developpez.com/mikedavem...e-sur-les-per/

A +

Hé bien, je vois que je déchaine les passions !!

Pour revenir a vos réponses, j'ai donc créé un trigger qui se doit de remplir les contraintes suivantes :

Un emplacement ne peut pas être loué 2 fois en même temps

Un client ne peut pas avoir deux réservations qui se chevauchent

On ne peut pas louer pendant la fermeture du camping

Les tuples des relations location et option_location ne peuvent pas être modifiés une fois la
facture éditée

On voudrait de plus avoir quelques comportements automatiques :
1. Lors de l'ajout d'un tuple dans location, les arrhres sont calculés automatiquement (ils ne
prennent en compte que le coût de l'emplacement et pas les éventuelles options
supplémentaires; ils sont arrondis à l'euro près).
2. Lors de l'ajout d'un tuple dans facture, le montant se calcule automatiquement.
Dans les deux cas, le prix utilisé pour un jour donné est celui de la saison en cours. Par exemple, si
une personne vient le dernier jour de la saison d'été haute saison, elle paiera le prix haute saison la
première nuit puis le prix moyenne saison.

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
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
CREATE FUNCTION camping.check_location() RETURNS trigger AS $check_location$
		DECLARE
		/*  La variable error permet de tester tout les éléments et doit être égal à zero pour permettre  l'insertion  */
		error							integer;
 
		/* ces variables permettent le calcul automatique du prix  */
		inc								integer;
		date_ecart						integer;
		date_en_cours					date;
		auto_arrhes						integer;
		prix_total						integer;
		annee_en_cours					integer;
 
		/*  */
		type_emplacement				integer;
		row_emplacement					camping.emplacement%rowtype;
		row_tarification_emplacement 	camping.tarification_emplacement%rowtype;
 
		row_loc			camping.location%rowtype;
		row_annee		camping.annee%rowtype;
		row_facture		camping.facture%rowtype;
		row_option_loc	camping.option_location%rowtype;
		row_saison		camping.saison%rowtype;
	BEGIN
 
		error = 0;
 
		/* La premiere procédure vérifie en cas d'update, si les dates ont changés et si elle ne chevauchent pas une reservation pour le même emplacement*/
		IF (TG_OP = "UPDATE") THEN
			IF ((new.debut_loc!=old.debut_loc) OR (new.fin_loc!=old.fin_loc)) THEN
				FOR row_loc IN SELECT * FROM location WHERE num_emplacement=new.num_emplcament LOOP
					IF ((SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc)) AND (error=0)) THEN
						error = 1;
						RAISE EXCEPTION 'Une location est déjà présente pour ces dates et cet emplacement';
					END IF;
					IF ((row_loc.id_client==new.idclient) AND (ERROR=0)) THEN
						IF ((SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc))) THEN
							error = 1;
							RAISE EXCEPTION 'Le client à déjà une reservation pendant ces dates';
						END IF;
					END IF;
				END LOOP;
			END IF;
		END IF;
 
		/* La seconde procédure vérifie en cas d'insert si les dates envoyés  ne chevauchent pas une reservation pour cet emplacement*/
		IF (TG_OP = "INSERT") THEN
			FOR row_loc IN SELECT * FROM location WHERE num_emplacement=new.num_emplcament LOOP
				IF ((SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc)) AND (error=0)) THEN
					error = 1;
					RAISE EXCEPTION 'Une location est déjà présente pour ces dates et cet emplacement';
				END IF;
 
				IF ((row_loc.id_client==new.idclient) AND (error=0)) THEN
					IF ((SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc))) THEN
						error = 1;
						RAISE EXCEPTION 'Le client à déjà une reservation pendant ces dates';
					END IF;
				END IF;
			END LOOP;
		END IF;
 
		/* La troisieme procédure vérifie si les dates envoyés ne sont pas hors des dates d'ouverture et de fermeture du camping*/
		FOR row_annee IN (SELECT * FROM annee) LOOP
			IF ((SELECT (new.debut_loc, new.fin_loc) OVERLAPS (row_annee.fermeture,row_annee.ouverture)) AND (error=0)) THEN
				ERROR = 1;
				RAISE EXCEPTION 'Impossible de louer hors des periodes d ouverture du camping';
			END IF;
		END LOOP;
 
		/* La quatrieme procédure vérifie si la facture existe, et dans ce cas empeche l'ajout des données dans la tables*/
		FOR row_facture IN (SELECT * FROM facture) LOOP
			IF (row_facture.id_location==new.id_location) THEN
				error = 1;
				RAISE EXCEPTION 'La facture existe, impossible de modifier les informations';
			END IF;
		END LOOP;
 
		/* La cinquieme procédure permet le calcul automatique des arrhes en fonction du prix de l'emplacement pour chaque jour de la reservation en fonction de la saison, pleine ou creuse */
		FOR row_emplacement IN (SELECT id_type_emplacement FROM camping.location LEFT JOIN camping.emplacement ON location.num_emplacement=emplacement.id_type_emplacement WHERE num_emplacement=new.num_emplacement) LOOP
			type_emplacement = row_emplacement.id_type_emplacement;
		END LOOP;
		date_ecart = new.fin_loc-new.debut_loc;
		inc = 0;
		prix_total = 0;
		annee_en_cours = date_part('year',new.debut_loc);
		WHILE inc!=date_ecart LOOP
			FOR row_saison IN (SELECT * FROM saison WHERE annee=annee_en_cours ORDER BY debut_saison DESC) LOOP
				date_en_cours = new.debut_loc + inc;
				IF ((date_en_cours<fin_saison) AND (date_en_cours>debut_saison)) THEN
					FOR row_tarification_emplacement IN (SELECT prix_journee FROM tarification_emplacement WHERE id_saison=row_saison.id_saison AND id_type_emplacement=type_emplacement) LOOP
						prix_total=prix_total+row_tarification_emplacement.prix_journee;
					END LOOP;
				END IF;
			inc=inc+1;
			END LOOP;
		END LOOP;
		auto_arrhes = (prix_total/100)*30;
 
		/* La sixieme procédure permet l'ajout de données dans la table en fonction de si on fait un INSERT ou un UPDATE */
		IF (error=0 AND TG_OP = "UPDATE") THEN
			UPDATE location SET debut_loc=new.debut_loc, fin_loc=new.fin_loc,num_emplacement=new.num_emplacement,arrhes=auto_arrhes,annule=new.annule WHERE id_location=old.id_location;
		END IF;
		IF (error=0 AND TG_OP = "INSERT") THEN
			INSERT INTO location VALUES (new.id_location,new.id_client,new.debut_loc,new.fin_loc,new.num_emplacement,auto_arrhes,new.annule);
		END IF;
 
		/* La septieme procédure permet d'ajuster les dates des options au cas ou on reduirais le délai de reservation */
		IF (error=0 AND TG_OP = "UPDATE") THEN
			FOR row_option_loc IN (SELECT * FROM option_location WHERE id_location=old.id_location) LOOP
				IF (new.fin_loc < row_option_loc.fin_option) THEN
					UPDATE option_location SET fin_option=new.fin_loc WHERE id_location=old.id_location;
				END IF;
				IF (new.debut_loc > row_option_loc.debut_option) THEN
					UPDATE option_location SET debut_option=new.debut_loc WHERE id_location=old.id_location;
				END IF;
			END LOOP;
		END IF;
	END;
$check_location$ LANGUAGE plpgsql;
 
CREATE TRIGGER CHECK_LOCATION BEFORE INSERT OR UPDATE ON camping.location
FOR EACH ROW EXECUTE PROCEDURE camping.check_location();

Mais je sèche complétement, le trigger s’insère bien dans la base mais les données s’insèrent comme si il n'y avait pas de trigger, ya un truc que j'ai pas du piger, mais je suis sur que vous allez pouvoir m'aider :ccool:

Merci à vous

PS : J'ajoute le nouveau script sql de création de la base avec certaines données pré remplis, il ne reste plus qu'a créé des réservations et des factures pour tester le fonctionnement :

Le fichier est ici : http://www.toofiles.com/fr/rawfilesd...sql-base2.html

Encore une fois ce serait beaucoup plus simple et plus performant en utilisant une contrainte check associée à une UDF.

Exemple : Un emplacement ne peut pas être loué 2 fois en même temps

Code:

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION F_CHECK_LOCATION_OVERLAPS (num_emplacement ?, debut ?, fin ?) 
RETURNS BOOLEAN AS 
$$ 
SELECT CASE  
          WHEN overlaps(debut_loc, fin_loc, $2, $3) THEN false 
          ELSE true 
        END  
FROM location WHERE num_emplacement = $1; 
$$ LANGUAGE 'sql';

Code:

1
2
ALTER TABLE location
   ADD CONSTRAINT CK_LOC_EMP_DEB_FIN CHECK (F_CHECK_LOCATION_OVERLAPS(num_emplacement, debut_loc, fin_loc));

A +

19/04/2011, 15h08
aspiman

oui oui j'avais bien compris , mais mon prof veux un trigger...
19/04/2011, 15h16
vmolines

Citation:

Envoyé par SQLpro

Ce que vous dites est faux !!!!
...

Mea culpa :fessee:. Merci pour le tuyau des UDF !
19/04/2011, 16h04
SQLpro

Citation:

Envoyé par aspiman

oui oui j'avais bien compris , mais mon prof veux un trigger...

Dite à votre prof que son exercice est stupide. En effet réaliser des triggers là ou ce n'est pas nécessaire et là ou c'est contre performant est parfaitement stupide. Apprenez lui à faire avec des contraintes CHECK et dites lui que c'est de la part d'un autre prof...
vous pouvez me citer !

A +
19/04/2011, 16h05
SQLpro

Citation:

Envoyé par vmolines

Mea culpa :fessee:. Merci pour le tuyau des UDF !

Du coup j'en ais fait un post :
http://blog.developpez.com/sqlpro/p9...s-externe-ave/

A +

ok heu vous savez, cela ne m'avancera à rien car le but étant d'apprendre les triggers et celui ci ne fonctionnant pas, je ne vais pas pouvoir rendre un devoir concernant une autre méthode, même si celle ci me parait meilleur, de plus le trigger comme vous avez pus le constater est beaucoup plus évoluer qu'une simple vérification, il y a à aussi des remplissages automatiques etc...

Je viens de le bosser de nouveau celui ci et j'ai ajouter les commentaires:

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
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
CREATE FUNCTION camping.check_location() RETURNS trigger AS $check_location$
		DECLARE
		/*  La variable error permet de tester tout les éléments et doit être égal à zero pour permettre  l'insertion  */
		error							integer;
 
		/* ces variables permettent le calcul automatique du prix  */
		inc								integer;
		date_ecart						integer;
		date_en_cours					date;
		auto_arrhes						integer;
		prix_total						integer;
		annee_en_cours					integer;
 
		type_emplacement				integer;
		row_emplacement					camping.emplacement%rowtype;
		row_tarification_emplacement 	camping.tarification_emplacement%rowtype;
 
		row_loc			camping.location%rowtype;
		row_annee		camping.annee%rowtype;
		row_facture		camping.facture%rowtype;
		row_option_loc	camping.option_location%rowtype;
		row_saison		camping.saison%rowtype;
	BEGIN
 
		error = 0;
 
		/* La premiere procédure vérifie en cas d'update, si les dates ont changés et si elle ne chevauchent pas une reservation pour le même emplacement ou  le même client*/
		IF (TG_OP = "UPDATE") THEN
			/* Si une des dates envoyés est différente de celle déja dans la base */
			IF ((new.debut_loc!=old.debut_loc) OR (new.fin_loc!=old.fin_loc)) THEN
				/* Cet ligne retourne toute les lignes de la table location et réalise le traitement pour chacune d'entre elles */
				FOR row_loc IN SELECT * FROM location LOOP
					/* 
					Si les dates de la ligne en cours chevauche celle envoyé et que le numero d'emplacement envoyé correspond a celui de la ligne en cours et qu'il n'y a pas eu d'erreur
					Alors on envoi une exception et on initialise une erreur
					*/
					IF ((SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc)) AND (row_loc.num_emplacement = new.num_emplacement) AND (error=0)) THEN
						error = 1;
						RAISE EXCEPTION 'Une location est déjà présente pour ces dates et cet emplacement';
					END IF;
					/* 
					Si l'id client de la ligne en cours correspond à l'id_client envoyé et qu'il n'y a pas d'erreur et que les dates se chevauchent
					Alors on envoi une exception et on initialise une erreur
					*/
					IF ((row_loc.id_client=new.id_client)  AND (ERROR=0) AND (SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc))) THEN
							error = 1;
							RAISE EXCEPTION 'Le client à déjà une reservation pendant ces dates';
						END IF;
					END IF;
				END LOOP;
			END IF;
		END IF;
 
		/* La seconde procédure vérifie en cas d'insert si les dates envoyés  ne chevauchent pas une reservation pour cet emplacement*/
		IF (TG_OP = "INSERT") THEN
			/* Cet ligne retourne toute les lignes de la table location et réalise le traitement pour chacune d'entre elles */
			FOR row_loc IN SELECT * FROM location LOOP
				/* 
					Si les dates de la ligne en cours chevauche celle envoyé et que le numero d'emplacement envoyé correspond a celui de la ligne en cours et qu'il n'y a pas eu d'erreur
					Alors on envoi une exception et on initialise une erreur
				*/
				IF ((SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc)) AND ( row_loc.num_emplacement=new.num_emplcament ) AND (error=0)) THEN
					error = 1;
					RAISE EXCEPTION 'Une location est déjà présente pour ces dates et cet emplacement';
				END IF;
 
				/* 
				Si l'id client de la ligne en cours correspond à l'id_client envoyé et qu'il n'y a pas d'erreur et que les dates se chevauchent
				Alors on envoi une exception et on initialise une erreur
				*/
				IF ((row_loc.id_client=new.id_client)  AND (ERROR=0) AND (SELECT (new.debut_loc,new.fin_loc) OVERLAPS (row_loc.debut_loc,row_loc.fin_loc))) THEN
					error = 1;
					RAISE EXCEPTION 'Le client à déjà une reservation pendant ces dates';
				END IF;
			END LOOP;
		END IF;
 
		/* La troisieme procédure vérifie si les dates envoyés ne sont pas hors des dates d'ouverture et de fermeture du camping*/
		FOR row_annee IN (SELECT * FROM annee) LOOP
			IF ((SELECT (new.debut_loc, new.fin_loc) OVERLAPS (row_annee.fermeture,row_annee.ouverture)) AND (error=0)) THEN
				ERROR = 1;
				RAISE EXCEPTION 'Impossible de louer hors des periodes d ouverture du camping';
			END IF;
		END LOOP;
 
		/* La quatrieme procédure vérifie si la facture existe, et dans ce cas empeche l'ajout des données dans la tables*/
		FOR row_facture IN (SELECT * FROM facture) LOOP
			IF (row_facture.id_location==new.id_location) THEN
				error = 1;
				RAISE EXCEPTION 'La facture existe, impossible de modifier les informations';
			END IF;
		END LOOP;
 
		/* La cinquieme procédure permet le calcul automatique des arrhes en fonction du prix de l'emplacement pour chaque jour de la reservation en fonction de la saison, pleine ou creuse */
		type_emplacement = 0;
		FOR row_emplacement IN (SELECT id_type_emplacement FROM camping.location LEFT JOIN camping.emplacement ON location.num_emplacement=emplacement.num_emplacement WHERE num_emplacement=new.num_emplacement) LOOP
			type_emplacement = row_emplacement.id_type_emplacement;
		END LOOP;
		/* Si type_emplacement est différent de zero, donc qu'on a trouvé le type de l'emplcement voulus */
		IF (id_type_emplacement != 0) THEN
			/* date ecart calcul le nombre de jour du sejour */
			date_ecart = new.fin_loc-new.debut_loc;
			/* inc est un incrément */
			inc = 0;
			prix_total = 0;
			/* annee en cours est l'année l'extraction de l'anné dans la date envoyé  */
			annee_en_cours = date_part('year',new.debut_loc);
			/* Tant qu l'increment est différent du nombre de jour du sejour */
			WHILE inc!=date_ecart LOOP
				/* 
				On recherche les dates de debut et de fin de saison comprise  dans annee_en_cours  et on les ordonnances par ordre decroissant
				*/
				FOR row_saison IN (SELECT * FROM saison WHERE annee=annee_en_cours ORDER BY debut_saison DESC) LOOP
					/*Pour chaque  tour de boucle on ajoute inc à date_en_cours affin de traiter chaque jour un par un*/
					date_en_cours = new.debut_loc + inc;
					/* 
					Si la date en cours de traitement est inferieur à la date de fin_saison et superieur à la date de debut saison en cours de lecture 
					Alors c'est qu'on a détecté la saison correspondate à la date
					*/
					IF ((date_en_cours<row_saison.fin_saison) AND (date_en_cours>row_saison.debut_saison)) THEN
						/* 
						On rechere dans la table tarification emplcement le prix de la journé pour l'id_saison en cours et le type d'emplacement choisis
						Puis on ajoute le prix au jours au prix total
						*/
						FOR row_tarification_emplacement IN (SELECT prix_journee FROM tarification_emplacement WHERE id_saison=row_saison.id_saison AND id_type_emplacement=type_emplacement) LOOP
							prix_total=prix_total+row_tarification_emplacement.prix_journee;
						END LOOP;
					END IF;
				/* On incremement inc de 1 */
				inc=inc+1;
				END LOOP;
			END LOOP;
			/* On calcul les arrhes par rapport à 30% du prix total */
			auto_arrhes = (prix_total/100)*30;
		END IF;
 
		/* La sixieme procédure permet l'ajout de données dans la table en fonction de si on fait un INSERT ou un UPDATE */
		IF (error=0 AND TG_OP = "UPDATE") THEN
			UPDATE location SET debut_loc=new.debut_loc, fin_loc=new.fin_loc,num_emplacement=new.num_emplacement,arrhes=auto_arrhes,annule=new.annule WHERE id_location=old.id_location;
		END IF;
		IF (error=0 AND TG_OP = "INSERT") THEN
			INSERT INTO location VALUES (new.id_location,new.id_client,new.debut_loc,new.fin_loc,new.num_emplacement,auto_arrhes,new.annule);
		END IF;
 
		/* La septieme procédure permet d'ajuster les dates des options au cas ou on reduirais le délai de reservation */
		IF (error=0 AND TG_OP = "UPDATE") THEN
			FOR row_option_loc IN (SELECT * FROM option_location WHERE id_location=old.id_location) LOOP
				IF (new.fin_loc < row_option_loc.fin_option) THEN
					UPDATE option_location SET fin_option=new.fin_loc WHERE id_location=old.id_location;
				END IF;
				IF (new.debut_loc > row_option_loc.debut_option) THEN
					UPDATE option_location SET debut_option=new.debut_loc WHERE id_location=old.id_location;
				END IF;
			END LOOP;
		END IF;
	END;
$check_location$ LANGUAGE plpgsql;
 
CREATE TRIGGER CHECK_LOCATION BEFORE INSERT OR UPDATE ON camping.location
FOR EACH ROW EXECUTE PROCEDURE camping.check_location();

Le truc c'est que je suis persuadé que l'algo est juste mais que j'ai zappé un truc pour la déclaration.

Je veux juste savoir comment faire fonctionner celui ci et après promis j'utiliserais votre méthode.

Siouplait et désolé pour l'insistance

les chaines de caractères sont délimitées par des apostrophe. Pas par des guillemets !

testez pas à pas, d'abord cela :

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
CREATE FUNCTION camping.check_location() 
RETURNS TRIGGER 
AS
$check_location$
 
DECLARE errorMSG VARCHAR(256); --  La variable error permet de tester tout les éléments et doit être égal à zero pour permettre  l'insertion 
 
BEGIN
 
/* chevauchement pour reservation pour le même emplacement */
   IF (TG_OP = 'UPDATE') AND errorMSG IS NULL
   THEN
      IF EXISTS(SELECT *
                FROM   camping.location 
                WHERE  overlaps(debut_loc, fin_loc, new.debut_loc, new.fin_loc)
                  AND  num_emplacement = new.num_emplacement)
      THEN
         errorMSG = 'Chevauchement de période de réservation pour même emplacement';                  
      END IF;
   END IF;
 
 
/* traitement des erreurs */
   IF ( errorMSG IS NOT NULL) 
   THEN
      RAISE EXCEPTION '%', errorMSG;
   END IF;
 
END;
$check_location$ LANGUAGE plpgsql;

Code:

1
2
3
4
5
CREATE TRIGGER E_IU_location 
   BEFORE INSERT OR UPDATE 
   ON camping.location
   FOR EACH ROW 
   EXECUTE PROCEDURE camping.check_location();

A +

19/04/2011, 17h01
SQLpro

Citation:

Envoyé par aspiman

ok heu vous savez, cela ne m'avancera à rien car le but étant d'apprendre les triggers et celui ci ne fonctionnant pas, je ne vais pas pouvoir rendre un devoir concernant une autre méthode, même si celle ci me parait meilleur, de plus le trigger comme vous avez pus le constater est beaucoup plus évoluer qu'une simple vérification, il y a à aussi des remplissages automatiques etc...

Dans ce cas, faites les deux :
un trigger pour le remplissage auto + CHECK et UDF pour les contraintes !

Rien ne vous interdit de rendre un devoir plus intelligent que ne l'est l'énoncé !

A +

J'avais pas vu, mais à mon sens il y a des erreurs dans le script SQL de création de vos tables...

En effet le type SERIAL ou BIGSERIAL (pourquoi d'aileurs l'avoir utilisé, pensez vous que le serial permettant de numéroter 2 milliards de lignes sont insuffisant ?) doit être bréservé à la table de référence et pas aux tables filles.

Par exemple pour la table camping.location, la bonne formulation est :
Code:

1 2 3 4 5 6 7 8 9 10 CREATE TABLE camping.location ( id_location bigserial NOT NULL, id_client bigint NOT NULL, debut_loc date NOT NULL, fin_loc date NOT NULL, num_emplacement bigint NOT NULL, arrhes bigint NOT NULL, annule int2 NOT NULL, CONSTRAINT Pk_location PRIMARY KEY ( id_location ) ) ;
A +

19/04/2011, 17h35
SQLpro

Je voit des tartines d'autres erreur dans votre modèle :
arrhes en bigserial ??? comment mettre 21,15 euros ?
des montants ne doivent pas être en float, mais en décimal (16,2) par exemple.
les quantités doivent être en float (généralement)
annule dans location n'est-il pas un booléen ?
description_option bigserial ????
Enfin, pour la modélisation de la table des clients, je vous donne ZÉRO pointé !
1) des varchar(150) pour des noms et prenom
2) une adresse dans le nom
3) pas de lignes d'addresse
4) pas de pays
5) un seul téléphone
6) une seule adresse (comme ça s'il revient je créé un doublon ou bien je fais une fausse facture)...

A +
19/04/2011, 18h28
SQLpro

en tout cas, c'est un défi laid....

Bon, vu le nombre de post que j'aligne, je ne pouvais pas m'empêcher de la faire !!!!

A +
20/04/2011, 13h49
aspiman

j'avais bien vu les erreurs de typage, par contre je ne les connais pas tous sous postgre, je découvre actuellement le systeme donc je fait comme je peu avec les documents qu'on me donne.

Pour la table il ne fallait pas prendre la première, j'ai reposter la seconde recorrigé, mais ce n'est qu'une copie du model de l'énoncé, que voici :

http://www.toofiles.com/fr/rawfilesd..._partie_2.html

Je vais tenter de faire mieux, je ne suis pas expert mais j'ai tout de même envie de réussir cet exercice...
20/04/2011, 13h55
aspiman

et je viens de me rendre compte que c'est mon logiciel de conception qui convertis mes types de cette manière et effectivement qu'il y a énormément de chose à revoir.

Je vais regarder tout ça.
20/04/2011, 15h32
SQLpro

Citation:

Envoyé par aspiman

et je viens de me rendre compte que c'est mon logiciel de conception qui convertis mes types de cette manière et effectivement qu'il y a énormément de chose à revoir.

Je vais regarder tout ça.

Utilisez un outil qui marche comme Power AMC, ça ira plus vite !

A +