IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PostgreSQL Discussion :

Un petit défi ?


Sujet :

PostgreSQL

  1. #1
    Futur Membre du Club
    Profil pro
    Inscrit en
    Février 2009
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 14
    Points : 9
    Points
    9
    Par défaut 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 : 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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 :


  2. #2
    Membre émérite

    Profil pro
    Inscrit en
    Mars 2005
    Messages
    1 683
    Détails du profil
    Informations personnelles :
    Âge : 41
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations forums :
    Inscription : Mars 2005
    Messages : 1 683
    Points : 2 576
    Points
    2 576
    Par défaut
    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.

  3. #3
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par vmolines Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    INSERT INTO T_CLIENT_CLI VALUES (1, 'DUPONT', 15);
    Insertion d'une "bonne" facture :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    INSERT INTO T_FACTURE_FCT VALUES (101, 1, '2011-04-15', 12.0)
    Insertion d'une "mauvaise" facture :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    INSERT INTO T_FACTURE_FCT VALUES (102, 1, '2011-04-15', 35.0)
    Message d'erreur :
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Membre à l'essai
    Inscrit en
    Mai 2006
    Messages
    16
    Détails du profil
    Informations forums :
    Inscription : Mai 2006
    Messages : 16
    Points : 15
    Points
    15
    Par défaut Malin ?
    Est-ce qu'il est suffisamment malin pour rendre la main directement sur une requête du type :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT * FROM T_FACTURE_FCT WHERE CLI_ID=1 AND FCT_REMISE=35.0
    Si non, est-il possible de le rendre intelligent ?

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  6. #6
    Futur Membre du Club
    Profil pro
    Inscrit en
    Février 2009
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 14
    Points : 9
    Points
    9
    Par défaut
    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 : 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
    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

    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

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    ALTER TABLE location
       ADD CONSTRAINT CK_LOC_EMP_DEB_FIN CHECK (F_CHECK_LOCATION_OVERLAPS(num_emplacement, debut_loc, fin_loc));
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  8. #8
    Futur Membre du Club
    Profil pro
    Inscrit en
    Février 2009
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 14
    Points : 9
    Points
    9
    Par défaut
    oui oui j'avais bien compris , mais mon prof veux un trigger...

  9. #9
    Membre émérite

    Profil pro
    Inscrit en
    Mars 2005
    Messages
    1 683
    Détails du profil
    Informations personnelles :
    Âge : 41
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations forums :
    Inscription : Mars 2005
    Messages : 1 683
    Points : 2 576
    Points
    2 576
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Ce que vous dites est faux !!!!
    ...
    Mea culpa . Merci pour le tuyau des UDF !

  10. #10
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par aspiman Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  11. #11
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par vmolines Voir le message
    Mea culpa . Merci pour le tuyau des UDF !
    Du coup j'en ais fait un post :
    http://blog.developpez.com/sqlpro/p9...s-externe-ave/

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  12. #12
    Futur Membre du Club
    Profil pro
    Inscrit en
    Février 2009
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 14
    Points : 9
    Points
    9
    Par défaut
    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 : 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
    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

  13. #13
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    les chaines de caractères sont délimitées par des apostrophe. Pas par des guillemets !

    testez pas à pas, d'abord cela :

    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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  14. #14
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par aspiman Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  15. #15
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  16. #16
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  17. #17
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  18. #18
    Futur Membre du Club
    Profil pro
    Inscrit en
    Février 2009
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 14
    Points : 9
    Points
    9
    Par défaut
    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...

  19. #19
    Futur Membre du Club
    Profil pro
    Inscrit en
    Février 2009
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 14
    Points : 9
    Points
    9
    Par défaut
    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. #20
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par aspiman Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. Un petit défi : tester si une chaîne est un nombre romain
    Par rambc dans le forum Général Python
    Réponses: 1
    Dernier message: 09/04/2009, 13h43
  2. Petit défi SQL (Update avec condition)
    Par Angeldu74 dans le forum Langage SQL
    Réponses: 10
    Dernier message: 03/03/2009, 13h55
  3. Petit défi en PHP
    Par davidedj dans le forum EDI, CMS, Outils, Scripts et API
    Réponses: 11
    Dernier message: 28/08/2006, 19h10
  4. Petit défi de lettres
    Par tiboel dans le forum Algorithmes et structures de données
    Réponses: 7
    Dernier message: 09/03/2006, 01h29
  5. [W3C] Petit défi : Alignement et compatibilité IE et FF
    Par StreM dans le forum Balisage (X)HTML et validation W3C
    Réponses: 4
    Dernier message: 09/09/2005, 16h33

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo