LES BASES DE DONNÉES

LE LANGAGE TSQL
LES DECLECNCHEURS

Exercice 1

Soit la base de données GComptesBancaire, avec la structure suivante:

Operation ( NumOP, TypeOp, MontantOp, #NumCpt, DateOp)
INSERT INTO Operation (TypeOp, MontantOp, #NumCpt, DateOp) VALUES
(‘R’, 2500, 1234558714878524, ‘23/05/2023’)

Compte(NumCompte, solde, TypeCompte, #NumCl)

Client(CIN, nom, prenom, adr, tel)

 Le numéro de l'opération est automatique

 La date d'opération prend par défaut la date du jour.
 Les opérations sont soit des dépôts ou des retraits, ajouter une contrainte à la table Opération sur le
champ TypeOp afin qu'il ne puisse avoir que deux valeurs : R ou D.
 Un compte ne peut étre que de type Compte Courant (CC) ou Compte d'Epargne (CE). Ajouter une
contrainte pour que le champ TypeCompte ne peut prendre que deux valeurs CC ou CE.
 Un client ne peut avoir qu'un seul compte courant mais plusieurs comptes d'épargnes.

Ecrire le code des déclencheurs suivants :

1. Le déclencheur TR_AJOUT_COMPTE qui, à la création d'un nouveau compte de type CC, vérifie si :
a. Le solde est >1500.00 DH,
b. Le client en question n'a pas d'autres comptes de type CC.

CREATE TRIGGER TR_AJOUT_COMPTE

BEFORE INSERT ON Compte

FOR EACH ROW

BEGIN

IF NEW.TypeCompte = 'CC' THEN

DECLARE compte_courant_count INT;

SELECT COUNT(*) INTO compte_courant_count

FROM Compte

WHERE #NumCl = NEW.#NumCl AND TypeCompte = 'CC';

IF NEW.solde <= 1500.00 OR compte_courant_count > 0 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'La création du compte de type CC est invalide.';

END IF;

END IF;

END;

CREATE TRIGGER TR_AJOUT_COMPTE

BEFORE INSERT ON Compte

FOR EACH ROW

BEGIN

IF NEW.TypeCompte = 'CC' THEN

IF (SELECT COUNT(*) FROM Compte WHERE #NumCl = NEW.#NumCl And TypeCompte = 'CC' > 0 )

OR

(NEW.solde <= 1500.00) THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'La création du compte de type CC est invalide.';

END IF;

END IF;

END;

2. Le déclencheur TR_SUPP_COMPTE qui interdit la suppression d'un compte dont le solde > 0.

CREATE TRIGGER TR_SUPP_COMPTE

BEFORE DELETE ON Compte

FOR EACH ROW

BEGIN

IF OLD.solde > 0 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'La suppression du compte est interdite car le solde est supérieur à 0.';
 END IF;

END;

3. Le déclencheur TR_UPDATE_COMPTE qui interdit la modification du solde des comptes auxquels sont
associées des opérations, il interdit également de changer un CE en CC (le contraire étant possible).

CREATE TRIGGER TR_UPDATE_COMPTE

BEFORE UPDATE ON Compte

FOR EACH ROW

BEGIN

DECLARE operation_count INT;

SELECT COUNT(*) INTO operation_count

FROM Operation

WHERE NumCpt = NEW.NumCompte;

IF operation_count > 0 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'La modification du solde du compte associé à des opérations est interdite.';

ELSEIF OLD.TypeCompte = 'CE' AND NEW.TypeCompte = 'CC' THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'La modification du type de compte de CE à CC est invalide.';

END IF;

END;

4. Le déclencheur TR_UPDATE_CLIENT qui, à la modification de numéro de telephone des clients, vérifie si

le nouveau numéro, n'est pas attribué à un autre client.

CREATE TRIGGER TR_UPDATE_CLIENT

BEFORE UPDATE ON Client

FOR EACH ROW

 BEGIN

DECLARE phone_count INT;

SELECT COUNT(*) INTO phone_count

FROM Client

WHERE tel = NEW.tel AND CIN != NEW.CIN;

IF phone_count > 0 THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'Le nouveau numéro de téléphone est déjà attribué à un autre client.';

END IF;

END;

5. Le déclencheur TR_insertion_operation QUI MET MIS A JOUR LA TABLE COMPTE POUR CHAQUE
OPERATION (RETRAIT LE SOLDE DOIT ETRE REDUIT DU MONTANT DE L’OPERATION ET VICE VERSA)

CREATE TRIGGER TR_insertion_operation AFTER INSERT ON OPERATION

FOR EACH ROW
BEGIN
IF NEW. TypeOp = 'D' THEN
UPDATE COMPTE SET SOLDE = SOLDE + New. MontantOp where #NumCpt = new. #NumCpt

End if

IF NEW. TypeOp = 'R' THEN

UPDATE COMPTE SET SOLDE = SOLDE - New. MontantOp where #NumCpt = new. #NumCpt
End if

END

Exercice 2
Soit La base de données dont la structure est suivante :
Styliste(NumStyliste, NomStyliste, AdrStyliste)
Costume(numCostume, DesignationCostume,# NumStyliste)
MembreJury (numMembreJury, nom, #fonction)
NotesJury(#NumCostume, #NumMembre, note)
 Fonction(Fonction)
Ecrire le code MYSQL des déclencheurs suivants :
1. Le déclencheur TR_SUPPRESSION qui, à la suppression de costumes de la table Costume, vérifie si des notes
leur ont été attribuées. Si c'est le cas empêcher la suppression.

CREATE TRIGGER TR_SUPPRESSION_costume BEFORE delete ON COSTUME

FOR EACH ROW
BEGIN
IF (select count(*) from NotesJury where #NumCostume = OLD. #NumCostume)>0

Then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Le nouveau numéro de téléphone est déjà attribué à un autre client.';
END IF;
END

CREATE TRIGGER TR_SUPPRESSION_costume BEFORE delete ON COSTUME

FOR EACH ROW
BEGIN
Declare NBRE int;
select count(*) into NBRE from NotesJury where #NumCostume = OLD. #NumCostume;

IF NBRE >0 Then

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Le nouveau numéro de téléphone est déjà attribué à un autre client.';
END IF;
END

2. Le déclencheur TR_AFFECTATION_NOTE qui, à l'affectation de notes à des costumes dans la table NotesJury,
vérifie si les notes attribuées sont comprises entre 0 et 20

CREATE TRIGGER TR_AFFECTATION_NOTE BEFORE insert ON NotesJury

FOR EACH ROW
BEGIN
IF (New.note not between 0 and 20)

Then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Le nouveau numéro de téléphone est déjà attribué à un autre client.';
END IF;
END
 CREATE TRIGGER TR_AFFECTATION_NOTE BEFORE insert ON NotesJury
FOR EACH ROW
BEGIN
IF (New.note < 0 or New.note > 20)

Then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Le nouveau numéro de téléphone est déjà attribué à un autre client.';
END IF;
END

3. Le déclencheur TR_AJOUT_MEMBRE qui, à l'ajout de membres jury, cherche si leurs fonctions existent dans
la table Fonction si ce n'est pas le cas il rajoute ses fonctions à la table.

CREATE TRIGGER TR_AJOUT_MEMBRE BEFORE insert ON MembreJury

FOR EACH ROW
BEGIN

IF (select count(*) from Fonction where Function = New. #fonction)=0

Then
Insert into Fonction(Function) values(New. #fonction);
END IF;
END

Exercice 3
Soit La base de données qui a la structure suivante :
Mouvement( NumMvt, Type, quantite, #codePdtFini)
ProduitFini(codePdtFini, nomPdt, qtiteStock)
Composition(#codePdtFini, #codePdtBrut,QtiteUtilise)
Fournisseur(RaisonSociale, AdrFournisseur, nbrProduitsFournis)
ProduitBrut(CodePdtBrut, NomPdtBrut, PAchat, #RaisonSociale)
Ajouter les declencheurs suivants:

Ecrire le code MYSQL des déclencheurs suivants :

1. TR_AJOUT_PDT_BRUT qui à l'ajout de produits bruts dans la table 'Produit Brut' met à jour le
champ NbrProduitsfournis pour les fournisseurs concernés
2. TR_SUPP_PDT_BRUT qui à la suppression de produits bruts dans la table 'Produit Brut' met à jour
le champ NbrProduitsfournis pour les fournisseurs concernés
3. TR_AJOUT_MVT qui à l'ajout de mouvements dans la table mouvement met à jour le stock
4. TR_SUPP_MVT qui à la suppression de mouvements dans la table mouvement met à jour le stock
5. TR_UPDATE_MVT qui à la modification de mouvements dans la table mouvement met à jour le
stock