Discussion :

[Invité]

Coucou, je ne parviens pas à terminer 1 trigger pour le n° de telephone qui doit faire appel à 1 procedure stockee pour le n° de telephone. Je travaille dans les tables que j'ai trouvées sur la base de données https://sqlpro.developpez.com/cours/...s/mpdHotel.gif qui vient en fait des publications de SQL PRO https://sqlpro.developpez.com/cours/bddexemple et j'utilise Wamp (Version de MySQL :
8.0.27) Merci à celles et ceux qui m'aideront <3

Voici mon objectif :

-créer un trigger qui vérifiera avant ajout, en faisant appel à une fonction/procédure stockée qui elle fera uniquement la vérification de l’existence de numéro de téléphone et de l’adresse e-mail un message d’erreur “l’e-mail et/ou le numéro de téléphone existe déjà”, si c’est le cas.
-Faire deux SQL de test exemple : ajouter un client avec ses informations complètes (Nom, Prénom, société, téléphone, localisation tel, e-mail, localisation e-mail)

Je vais d'abord réfléchir pour le numéro de téléphone pour ce post, ensuite je réfléchirai pour l'adresse mail dans un autre post. Donc pour ce post je parlerai de 1 trigger et 1 procedure concernant le numéro de telephone, et dans un autre post je parlerai de 1 trigger et 1 procedure concernant l'adresse mail car je ne vois pas comment faire autrement...

Je vous présente mon trigger :

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
 
//pout le numero de telephone
delimiter;
 
create TRIGGER trg_ajout_num before insert on T_CLIENT
 
FOR EACH ROW
 
	BEGIN
 
	insert into T_TELEPHONE (TEL_ID) values (new.TEL_ID);
 
	END;
 
delimiter ;|

Je vous présente ma procédure stockée :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
CREATE EXCEPTION EXC_EXISTE_DEJA 'Le numéro de téléphone @1 existe déjà';
 
set term^;
CREATE PROCEDURE Procedure_Ajouter_Numero(
   numero integer)
 
   RETURNS
   (numeroTelephone integer)
 
 as
 BEGIN
  //vérifier si le numéro n existerait pas déjà
  if ((SELECT tel_ID from T_Telephone where Tel_ID=:numero)
      )THEN
       EXCEPTION EXC_EXISTE_DEJA USING (:numero);
 
  //recherche le numéro de tel si pas précisé
  if (numero is null)
  then
   select TEL_ID from T_TELEPHONE where TEL_ID=:numero 
   into :numeroTelephone;
 
  //insère la ligne de commande
  insert into T_TELEPHONE (TEL_ID)
  values(:numero )
  returning TEL_ID into :numeroTelephone;
 
 
 END^
 
/* comment je vais tester ensuite */
select * from T_TELEPHONE
EXECUTE PROCEDURE Procedure_Ajouter_Numero(0488448844,57,'test@email.com','localisation')

Je vous présente la manière dont j'ai créé les tables en question (avec les référencements) :

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
 
/* Table : T_CLIENT*/
create table T_CLIENT(
CLI_ID INTEGER primary key references T_TITRE,
TIT_CODE CHAR(8),
CLI_NOM CHAR(32) not null,
CLI_PRENOM VARCHAR(25),
CLI_ENSEIGNE VARCHAR(100)
);
/* Index*/
create unique index IDX_T_CLIENT_PK on T_CLIENT (CLI_ID);
create index IDX_L_CLI_TIT_FK on T_CLIENT (TIT_CODE);
 
/* Table : T_TELEPHONE*/
create table T_TELEPHONE (
TEL_ID INTEGER primary key,
CLI_ID INTEGER references T_CLIENT,
TYP_CODE CHAR(8) references T_TYPE,
TEL_NUMERO CHAR(20) not null,
TEL_LOCALISATION VARCHAR(64)
);
 
/* Index*/
create unique index IDX_T_TELEPHONE_PK on T_TELEPHONE (TEL_ID);
create index IDX_L_TEL_CLI_FK on T_TELEPHONE (CLI_ID);
create index IDX_L_TEL_TYP_FK on T_TELEPHONE (TYP_CODE);
 
/* Table : T_EMAIL */
create table T_EMAIL (
EML_ID INTEGER primary key references T_CLIENT,
CLI_ID INTEGER not null,
EML_ADRESSE VARCHAR(100) not null,
EML_LOCALISATION VARCHAR(64)
);
 
/* Index*/
create unique index IDX_T_EMAIL_PK on T_EMAIL (EML_ID);
create index IDX_L_EML_CLI_FK on T_EMAIL (CLI_ID);

[escartefigue]

Bonjour,

Avant de se pencher sur les TRIGGER, il faut d'abord revoir la définition des tables qui ici est incorrecte :
une PK dans une table qui fait référence à une autre table, qui plus est sans nommer la colonne référencée, c'est doublement incorrect !

Accessoirement, pourquoi choisir du char(32) fixe pour le nom et du varchar(25) pour le prénom, pas très cohérent.
Plus encore, la notion d'enseigne concerne les clients personnes morales (entreprises) alors que le nom et le prénom ne concernent que les personnes physiques, avoir ces 3 colonnes dans une même table implique qu'on aura systématiquement des colonnes marquées "null".
Il aurait été préférable d'utiliser l'héritage et la spécialisation pour éviter cette situation.

Ensuite, pour vérifier l'existence du n° de téléphone dans la table, il ne faut pas comparer le n° de téléphone et l'identifiant du téléphone !
L'identifiant (TEL_ID) est une valeur technique, sans aucun rapport avec le numéro (TEL_NUMERO)
Et enfin lors de l'insertion dans la table des téléphones, il faut alimenter la foreign key identifiant le client, sans quoi le téléphone inséré ne sera lié à personne...

[Invité]

Accessoirement, pourquoi choisir du char(32) fixe pour le nom et du varchar(25) pour le prénom, pas très cohérent.
Plus encore, la notion d'enseigne concerne les clients personnes morales (entreprises) alors que le nom et le prénom ne concernent que les personnes physiques, avoir ces 3 colonnes dans une même table implique qu'on aura systématiquement des colonnes marquées "null".
Il aurait été préférable d'utiliser l'héritage et la spécialisation pour éviter cette situation.

Bonjour, j'ai oublié de préciser qu'il s'agit d'un travail demandé par ma formatrice car je suis une formation en base de données, et que la formatrice nous a obligé de ne pas toucher aux types (donc ne pas les modifier) car c'est plus tard durant notre formation qu'elle nous fera ce type de remarque que vous venez de m'en faire part et que je vous en remercie car c'est enrichissant.

Avant de se pencher sur les TRIGGER, il faut d'abord revoir la définition des tables qui ici est incorrecte :
une PK dans une table qui fait référence à une autre table, qui plus est sans nommer la colonne référencée, c'est doublement incorrect !

Concernant la création des tables (la partie DDL), je comprends mieux mon erreur de départ à présent. Sauf que j'ignore toujours la manière de créer les liens entre les tables car ma formatrice m'a dit qu'on ne doit pas s'occuper des contraintes pour le moment. Voici par ailleurs ce que j'ai essayé maintenant pour les tables et j'aimerais un retour svp car je me sens perdue et j'obtiens un message d'erreur parlant de la PK :

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
 
/* Table : T_CLIENT*/
create table T_CLIENT(
CLI_ID INTEGER primary key,
TIT_CODE CHAR(8) references T_TITRE (CLI_ID), //modification apportée
CLI_NOM CHAR(32) not null,
CLI_PRENOM VARCHAR(25),
CLI_ENSEIGNE VARCHAR(100)
);
/* Index*/
create unique index IDX_T_CLIENT_PK on T_CLIENT (CLI_ID);
create index IDX_L_CLI_TIT_FK on T_CLIENT (TIT_CODE);
 
/* Table : T_TELEPHONE*/
create table T_TELEPHONE (
TEL_ID INTEGER primary key,
CLI_ID INTEGER references T_CLIENT (TIT_CODE), //modification apportée
TYP_CODE CHAR(8) references T_TYPE,
TEL_NUMERO CHAR(20) not null,
TEL_LOCALISATION VARCHAR(64)
);
 
/* Index*/
create unique index IDX_T_TELEPHONE_PK on T_TELEPHONE (TEL_ID);
create index IDX_L_TEL_CLI_FK on T_TELEPHONE (CLI_ID);
create index IDX_L_TEL_TYP_FK on T_TELEPHONE (TYP_CODE);

[escartefigue]

Concernant la création des tables (la partie DDL), je comprends mieux mon erreur de départ à présent. Sauf que j'ignore toujours la manière de créer les liens entre les tables car ma formatrice m'a dit qu'on ne doit pas s'occuper des contraintes pour le moment. Voici par ailleurs ce que j'ai essayé maintenant pour les tables et j'aimerais un retour svp car je me sens perdue et j'obtiens un message d'erreur parlant de la PK :

Je comprends le souci de votre formatrice de ne pas aborder tous les sujets à la fois, mais, la bonne méthode pour modéliser une base de données est de ne pas se préoccuper dans un premier temps des tables et donc encore moins des contraintes.
Il faut seulement se préoccuper des règles de gestion, puis établir le modèle conceptuel, dans lequel il n'y a pas de tables, seulement des types d'entité et des associations

Voici deux séries de règles de gestion différentes (notez la règle RG01a qui diffère et du coup les cardinalités coté CLI_client_1) et les modèles conceptuels (MCD) correspondants :



N'importe quel logiciel de modélisation permet de dériver un modèle tabulaire à partir d'un MCD, c'est ce que j'ai fait ici avec Looping (excellent et gratuit), qui me donne ces deux modèles logiques :



On voit que les tables sont très différentes :

• deux tables seulement dans le premier MLD avec la présence d'une clef étrangère (FK) TIT_code dans la table CLI_client
• trois tables dans le deuxième MLD, l'association n-aire étant devenue une table, dont la clef primaire est composée des identifiants respectifs des types d'entité CLI_client_1 et TIT_titre_1, par contre, il n'y a pas de FK dans la table CLI_client_1

Ce faisant, la problématique des clefs étrangères et des contraintes associées ne se posent pas, elles sont générées automatiquement comme il faut en fonction des règles de gestion au travers du MCD. Voici le script obtenu pour ces deux modèles tabulaires, on y voit que les définitions des foreign key et des contraintes sont bien là, inutile de s'en préoccuper

Code SQL :

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
CREATE TABLE TIT_TITRE(
   TIT_code CHAR(8),
   TIT_libelle VARCHAR(32) NOT NULL,
   PRIMARY KEY(TIT_code)
);
 
CREATE TABLE CLI_client(
   CLI_id INT AUTO_INCREMENT,
   CLI_nom VARCHAR(50) NOT NULL,
   CLI_prenom VARCHAR(50) NOT NULL,
   CLI_enseigne VARCHAR(100) NOT NULL,
   TIT_code CHAR(8) NOT NULL,
   PRIMARY KEY(CLI_id),
   FOREIGN KEY(TIT_code) REFERENCES TIT_TITRE(TIT_code)
);
CREATE TABLE CLI_client_1(
   CLI_id INT AUTO_INCREMENT,
   CLI_nom VARCHAR(50) NOT NULL,
   CLI_prenom VARCHAR(50) NOT NULL,
   CLI_enseigne VARCHAR(100) NOT NULL,
   PRIMARY KEY(CLI_id)
);
 
CREATE TABLE TIT_TITRE_1(
   TIT_code CHAR(8),
   TIT_libelle VARCHAR(32) NOT NULL,
   PRIMARY KEY(TIT_code)
);
 
CREATE TABLE CP_posseder_1(
   CLI_id INT,
   TIT_code CHAR(8),
   PRIMARY KEY(CLI_id, TIT_code),
   FOREIGN KEY(CLI_id) REFERENCES CLI_client_1(CLI_id),
   FOREIGN KEY(TIT_code) REFERENCES TIT_TITRE_1(TIT_code)
);

Le problème, c'est d'apprendre à modéliser une base de données en commençant par réfléchir aux tables, c'est une très mauvaise approche et la source de bien des erreurs de modélisation.

[Invité]

Ok merci pour toutes ces informations , je vais voir ce que je peux faire maintenant pour ajuster mes tables en réfléchissant d'abord au MCD.

Par ailleurs, est-ce que vous codez en ISO_88591 dans les exemples du post précédent svp ?

[escartefigue]

Je n'ai codé aucun charset au niveau table et colonne, ce faisant, c'est le jeu de caractères de la base de données ou à défaut du serveur qui s'appliquera