Cours Programmation Avec Exercices Corriges SGBD 2
Cours Programmation Avec Exercices Corriges SGBD 2
Cours Programmation Avec Exercices Corriges SGBD 2
Sommaire
Les procédures stockées sont des programmes créés et exécutés du côté serveur. Elles
sont destinées à être appelées par un ou plusieurs clients de la base de données et sont très
importantes pour plusieurs raisons :
Elles sont pré-compilées à la création et donc l'utilisation d'une procédure stockée
garantit un temps de réponse plus rapide et une meilleure performance système ;
Elles évitent de réécrire plusieurs fois les mêmes instructions ;
Elles soulagent les applications client en répartissant des traitements entre le client et
le serveur ;
Elles soulagent le réseau puisque seule l'instruction d'exécution de la procédure
stockée sera envoyée à travers le réseau ;
Il est possible de donner aux utilisateurs le droit d'exécuter une procédure stockée
sans qu'ils aient le droit sur les objets qu'elle manipule.
Pour les exemples de ce cours, la base de données SQL Server GestionCom sera utilisée :
Description : Cette base de données permet de stocker des commandes. Chaque
commande concerne un ensemble d'articles.
Structure de la base de données :
Syntaxe :
Naoual ABDALLAH Page : 2 Version Octobre 2007
Module N° 12 : Programmation des Procédures Stockées et des Triggers Filière : TSDI
Syntaxe :
Select @Nom_Variable = valeur
Select @Nom_Variable = (Select ...from...Where)
ou
Set @Nom_Variable =valeur
Set @Nom_Variable = (Select ...from...Where)
Exemples :
Select @a=1
-- Affecte la valeur 1 à la variable @a
Select @a=(Select count(NumArt) from Article)
-- Affecte le nombre d'articles enregistrés dans la table article à la
variable @a
Select @b='Table pour ordinateur'
-- Affecte la valeur 'Table pour ordinateur' à la variable @b
LL''aaffffiicchhaaggee dd''iinnffoorrm
maattiioonnss
Syntaxe :
Print Elément_A_Afficher
Exemples :
Soient @a et @b des variables de type Chaîne de caractères, @c et @d des
variables de type entier
Print 'Bonjour' -- Affiche le texte Bonjour
Print @a -- Affiche la valeur de @a
Print @c -- Affiche la valeur de @c
Print @c + @d -- Affiche la somme des variables @c et @d
Print convert(varchar, @c) + @b -- Affiche la valeur de @c concaténé
avec la valeur de @b mais puisque @c est de type numérique et qu'on
ne peut jamais concaténer une valeur numérique avec une valeur chaîne
de caractères, il faut passer par une fonction de conversion dont la
syntaxe est la suivante :
Convert (Type de conversion, Valeur à convertir)
LL''aarrrrêêtt dd''uunn pprrooggrraam
mmmee
L'instruction return arrête l'exécution d'un programme sans condition
Syntaxe :
Return
LL''uuttiilliissaattiioonn ddeess ssttrruuccttuurreess aalltteerrnnaattiivveess
If...Else :
Syntaxe :
If Condition
Begin
Instructions
End
Else
Begin
Instructions
End
Remarques :
Si une instruction Select apparaît dans la condition, il faut la mettre
entre parenthèses
Si dans la clause If ou Else il existe une seule instruction, on peut
omettre le Begin et le End
Exemple :
On souhaite vérifier si le stock de l'article portant le numéro 10 a
atteint son seuil minimum. Si c'est le cas afficher le message
'Rupture de stock' :
Declare @QS
Declare @SM int
Select @QS = (Select QteEnStock from article Where
NumArt =10)
Select @SM = (Select SeuilMinimum from article Where
NumArt =10)
If @QS<=@SM
Print 'Rupture de stock'
Else
Print 'Stock disponible'
Case : Permet d'affecter, selon une condition, une valeur à un champ dans une
requête Select
Syntaxe :
Case
When Condition1 Then Résultat 1
When Condition2 Then Résultat 2
...
Else Résultat N
End
Exemple :
Exercices
1. Ecrire un programme qui calcule le montant de la commande numéro 10 et affiche
un message 'Commande Normale' ou 'Commande Spéciale' selon que le montant
est inférieur ou supérieur à 100000 DH
3. Ecrire un programme qui affiche la liste des commandes et indique pour chaque
commande dans une colonne Type s'il s'agit d'une commande normale
(montant <=100000 DH) ou d'une commande spéciale (montant > 100000 DH)
4. A supposer que toutes les commandes ont des montants différents, écrire un
programme qui stocke dans une nouvelle table temporaire les 5 meilleures
commandes (ayant le montant le plus élevé) classées par montant décroissant (la
table à créer aura la structure suivante : NumCom, DatCom, MontantCom)
Solutions
1.
Declare @Montant decimal
Syntaxe :
While Condition
Begin
instructions
End
Remarques :
Le mot clé Break est utilisé dans une boucle While pour forcer l'arrêt de la
boucle
Le mot clé Continue est utilisé dans une boucle While pour annuler
l'itération en cours et passer aux itérations suivantes (renvoyer le
programme à la ligne du while)
Exemple :
Tant que la moyenne des prix des articles n'a pas encore atteint 20 DH et le
prix le plus élevé pour un article n'a pas encore atteint 30 DH, augmenter
les prix de 10% et afficher après chaque modification effectuée la liste des
articles. Une fois toutes les modifications effectuées, afficher la moyenne
des prix et le prix le plus élevé :
While ((Select avg(puart) from article)<20) and (select max(puart)
from article) <30)
Begin
Update article Set puart=puart+(puart*10)/100
Select * from article
End
Select avg(puart) as moyenne , max(puart) as [Prix élevé] from article
LLee tteesstt ddee m
mooddiiffiiccaattiioonn dd''uunnee ccoolloonnnnee
L'instruction If Update renvoie une valeur true ou false pour déterminer si une
colonne spécifique d'une table a été modifié par une instruction insert ou update
(cette instruction est utilisée spécialement dans les déclencheurs et ne s'applique
pas à une instruction Delete).
Syntaxe :
If Update (Nom_Colonne)
Begin
…
End
Exemple :
If update (numCom)
Print 'Numéro de commande modifié'
LLee B
Brraanncchheem
meenntt
L'instruction Goto renvoie l'exécution du programme vers un point spécifique
repéré par une étiquette
Syntaxe :
Goto Etiquette
Remarque : Pour créer une étiquette, il suffit d'indiquer son nom suivi de deux
points (:)
Exemple
L'exemple précédent peut être écrit ainsi en utilisant l'instruction goto :
Declare @a decimal,@b decimal
Etiquette_1:
Set @a= (Select avg(puart) from article)
Set @b= (Select Max(puart) from article)
If @a<20 and @b<30
Begin
Update article Set puart=puart+(puart*20)/100
Select * from article
Goto Etiquette_1
End
Select avg(puart) as moyenne , max(puart) as [Prix élevé] from article
LLaa ggeessttiioonn ddeess ttrraannssaaccttiioonnss
Une transaction permet d'exécuter un groupe d'instructions. Si pour une raison ou
une autre l'une de ces instructions n'a pas pu être exécutée, tous le groupe
d'instructions est annulé (le tout ou rien) :
Pour démarrer une transaction on utilise l'instruction Begin Tran
Pour valider la transaction et rendre les traitements qui lui sont associés
effectifs, on utilise l'instruction Commit Tran
Pour interrompre une transaction en cours qui n'a pas encore été validée,
on utilise l'instruction Rollback Tran
Si plusieurs transactions peuvent être en cours, on peut leur attribuer des
noms pour les distinguer
Syntaxe :
Begin Tran [Nom_Transaction]
…
If Condition
RollBack Tran [Nom_Transaction]
…
Commit Tran [Nom_Transaction]
Exemple :
Supposons qu'il n'existe pas de contrainte clé étrangère entre le champ
NumCom de la table LigneCommande et le champ NumCom de la
Commande.
On souhaite supprimer la commande numéro 5 ainsi que la liste de ces
articles. Le programme serait :
Delete from Commande where NumCom=5
Exercices
1. Ecrire un programme qui pour chaque commande :
Affiche le numéro et la date de commande sous la forme :
Commande N° : ……Effectuée le : ….
La liste des articles associés
Le montant de cette commande
Solutions
1.
Declare @a int, @b DateTime, @c decimal
Declare C1 Cursor for Select C.NumCom,DatCom, Sum(PUArt*QteCommandee)
From Commande C, Article A, LigneCommande LC
Where C.NumCom=LC.NumCom and
LC.NumArt=A.NumArt group by
C.NumCom,DatCom
Open C1
Fetch Next from C1 into @a,@b,@c
While @@fetch_status =0
Begin
Print 'Commande N° : ' + convert(varchar,@a) + ' effectuée le : ' +
convert(varchar,@b)
Select Numart from LigneCommande where numcom=@a
Print 'Son montant est : ' + convert(varchar,@c)
Fetch Next from C1 into @a,@b,@c
End
Close C1
Deallocate C1
2.
Declare @a int
Declare Cur_Com Cursor for select NumCom from Commande
open Cur_Com
Fetch Next from Cur_Com into @a
While @@fetch_status =0
Begin
if not exists (Select NumArt from LigneCommande
where NumCom=@a)
Begin
Print 'Aucun article pour la commande N° : ' +
convert(varchar, @a) + '. Elle sera supprimée'
Delete From Commande Where NumCom=@a
End
Else
Begin
Print 'Commande n° : ' + convert(varchar, @a)
Select A.NumArt, DesArt, PUArt, QteCommandee
From Article A, Lignecommande LC
Where A.NumArt=LC.NumArt and NumCom=@a
Naoual ABDALLAH Page : 12 Version Octobre 2007
Module N° 12 : Programmation des Procédures Stockées et des Triggers Filière : TSDI
End
Fetch Next from Cur_Com into @a
End
Close Cur_Com
Deallocate Cur_Com
C
Crrééaattiioonn ddeess pprrooccéédduurreess SSttoocckkééeess eett ddeess T
Trriiggggeerrss
Transact-SQL offre les instructions Create Procedure et Create Trigger pour la
création des procédures stockées et des Triggers. Ces deux instructions seront
traitées en détail dans les parties qui suivent.
C
Crrééaattiioonn eett eexxééccuuttiioonn dd''uunnee pprrooccéédduurree ssttoocckkééee
Avant de voir comment programmer une procédure stockée, Il a été jugé
intéressant de voir d'abord où créer et où exécuter cette procédure stockée ainsi
l'utilisateur pourra au fur et à mesure de ce cours tester en pratique l'ensemble des
notions acquises.
Création d'une procédure stockée :
Il existe plusieurs manières de créer une procédure stockée :
A partir d'entreprise Manager :
Accéder à la base de données concernée
Cliquer avec le bouton droit de la souris sur 'Procédures Stockées'
Choisir 'Nouvelle Procédure Stockée'
Exécution :
Exec Nom_Procedure
Exemples :
Créer une procédure stockée nommée SP_Articles qui affiche la liste
des articles avec pour chaque article le numéro et la désignation :
Create Procedure SP_Articles as
Select NumArt, DesArt from Article
Exécuter cette procédure :
Exec SP_Articles
Créer une procédure stockée qui calcule le nombre d'articles par
commande :
Create Procedure SP_NbrArticlesParCommande as
Select Commande.NumCom, DatCom, Count(NumArt)
From Commande, LigneCommande
Where Commande.NumCom=LigneCommande.NumCom
Group by Commande.NumCom, DatCom
Exécuter cette procédure :
Exec SP_NbrArticlesParCommande
Avec des paramètres en entrée : La procédure stockée en fonction de valeurs
provenant de l'extérieur va effectuer certains traitements et donc il n'est pas
normal qu'une procédure stockée reçoive des paramètres en entrée dont les
valeurs ne soient pas exploitées dans les instructions des procédures (dans des
tests, dans des conditions…)
Syntaxe :
Create Procedure Nom_Propriétaire.Nom_Procedure
Nom_Param1_Entrée Type_Donnée = Valeur_Par_Defaut,
Nom_Param2_Entrée Type_Donnée = Valeur_Par_Defaut…
as
Instructions
Exécution :
Exec Nom_Procedure Valeur_Param1, Valeur_Param2...
Ou
Exec Nom_Procedure Nom_Param1 = Valeur_Param1,
Nom_Param2 = Valeur_Param2...
Remarque:
Avec la deuxième syntaxe, l'utilisateur n'est pas obligé de passer les
paramètres dans l'ordre et en plus si des paramètres ont des valeurs par
défaut, il n'est pas obligé de les passer.
Exemples :
Créer une procédure stockée nommée SP_ListeArticles qui affiche la
liste des articles d'une commande dont le numéro est donné en
paramètre :
Create Procedure SP_ListeArticles @NumCom int as
Select A.NumArt, NomArt, PUArt, QteCommandee
From Article A, LigneCommande LC
Where LC.NumArt=A.NumArt and LC.NumCom=@NumCom
Exécuter cette procédure pour afficher la liste des articles de la
commande numéro 1 :
Exec SP_ListeArticles 1
Ou
Declare @nc int
Set @nc=1
Exec SP_ListeArticles @nc
Créer une procédure stockée nommée SP_ComPeriode qui affiche la
liste des commandes effectuées entre deux dates données en
paramètre :
Create Procedure SP_ComPeriode @DateD DateTime,
@DateF DateTime as
Select * from Commande Where datcom between @dateD
and @DateF
Exécuter cette procédure pour afficher la liste des commandes
effectuées entre le 10/10/2006 et le 14/12/2006 :
Exec SP_ComPeriode '10/10/2006', '14/12/2006'
Ou
Declare @dd DateTime, @df DateTime
Set @dd='10/10/2006'
Set @df='14/12/2006'
Exec SP_ComPeriode @dd, @df
Créer une procédure stockée nommée SP_TypeComPeriode qui
affiche la liste des commandes effectuées entre deux dates passées
en paramètres. En plus si le nombre de ces commandes est
supérieur à 100, afficher 'Période rouge'. Si le nombre de ces
commandes est entre 50 et 100 afficher 'Période jaune' sinon
afficher 'Période blanche' (exploiter la procédure précédente) :
Create Procedure SP_TypeComPeriode @DateD DateTime,
@DateF DateTime as
Exec SP_ComPeriode @DateD, @DateF
Declare @nbr int
Set @nbr=(Select count(NumCom) from Commande Where
datcom between @dateD and @DateF)
If @nbr >100
Print 'Période Rouge'
Else
Begin
If @nbr<50
Print 'Période blanche'
Else
Print 'Période Jaune'
End
Créer une procédure stockée nommée SP_EnregistrerLigneCom qui
reçoit un numéro de commande, un numéro d'article et la quantité
commandée :
Si l'article n'existe pas ou si la quantité demandée n'est
pas disponible afficher un message d'erreur
Si la commande introduite en paramètre n'existe pas, la
créer
Ajoute ensuite la ligne de commande et met le stock à
jour
Create Procedure SP_EnregistrerLigneCom @numCom int,
@numart int, @qte decimal AS
if not exists(select numart from article where numart=@numart)
or (select Qteenstock from article where numart=@numart)
< @qte
Begin
Print 'Cet article n''existe pas ou stock est insuffisant'
Return
End
Begin transaction
if not exists(select numcom from Commande where
numCom=@numcom)
insert into commande
values(@NumCom,getdate())
Naoual ABDALLAH Page : 17 Version Octobre 2007
Module N° 12 : Programmation des Procédures Stockées et des Triggers Filière : TSDI
...
Return Valeur_Sortie
Exécution :
Declare Var_Retour Type_Var_Retour
...
Exec Var_Retour=Nom_Procedure …
Exemples :
Créer une procédure stockée nommée SP_TypePeriode qui renvoie un
code de retour. Si le nombre de commandes est supérieur à 100, la
procédure renvoie 1. Si le nombre de commandes est entre 50 et 100, la
procédure renvoie 2. Si le nombre de commandes est inférieur à 50, la
procédure renvoie 3. Si une erreur système a lieu, la procédure
renvoie 4 :
Create Procedure SP_TypePeriode as
Declare @NbrCom int
Set @NbrCom = (Select count(NumCom) from Commande)
If @NbrCom >=100
Return 1
If @NbrCom >50
Return 2
If @NbrCom <=50
Return 3
If @@ERROR <>0
Return 4
PS 3. Qui reçoit un numéro de costume et qui affiche la liste des notes attribuées
avec pour chaque note le numéro du membre de jury qui l'a attribué, son nom,
sa fonction et la note.
PS 4. Qui retourne le nombre total de costumes
PS 5. Qui reçoit un numéro de costume et un numéro de membre de jury et qui
retourne la note que ce membre a attribué à ce costume
PS 6. Qui reçoit un numéro de costume et qui retourne sa moyenne.
Exercice 2
Une société achète à ses fournisseurs des produits bruts qu'elle utilise dans la fabrication
de produits finis. On souhaite gérer la composition et les mouvements de stock de chaque
produit fini.
Les Mouvements de stock sont les opérations d'entrée ou de sortie (type=S ou type=E)
de produits finis vers ou depuis le magasin.
La base de données a la structure suivante :
On suppose que les tables 'Mouvement', 'Produit Fini' et 'Fournisseur' sont créées. Créer
les procédures suivantes :
PS 1. Qui crée les tables ProduitBrut et Composition
PS 2. Qui affiche le nombre de produits bruts par produit Fini
PS 3. Qui retourne en sortie le prix d'achat le plus élevé
PS 4. Qui affiche la liste des produits finis utilisant plus de deux produits bruts
PS 5. Qui reçoit le nom d'un produit brut et retourne en sortie la raison sociale de
son fournisseur
PS 6. Qui reçoit le code d'un produit fini et qui affiche la liste des mouvements de
sortie pour ce produit
PS 7. Qui reçoit le code d'un produit fini et le type de mouvement et qui affiche la
liste des mouvements de ce type pour ce produit fini
PS 8. Qui pour chaque produit fini affiche :
La quantité en stock pour ce produit
La liste des mouvements concernant ce produit
La quantité totale en sortie et la quantité totale en entrée
La différence sera comparée à la quantité en stock. Si elle correspond afficher
'Stock Ok' sinon afficher 'Problème de Stock'
PS 9. Qui reçoit un code produit fini et retourne en sortie son prix de reviens
PS 10. Qui affiche pour chaque produit fini :
Le prix de reviens (utiliser la procédure précédente)
La liste des produits bruts le composant (nom, Mt, RSFour)
Le nombre de ces produits
Exercice 3
Soit la base de données suivante :
C
Crryyppttaaggee dd''uunnee pprrooccéédduurree ssttoocckkééee
Il est possible de rendre le code de la procédure stockée inaccessible, il suffit pour
cela de procéder à un cryptage. La procédure stockée pourra être exécutée par un
programme externe mais son contenu sera illisible que cela soit pour son
propriétaire ou pour d'autres utilisateurs.
Remarque :
Cette procédure est irréversible
Syntaxe :
Create Procedure ………. WITH ENCRYPTION as
Instructions
R
Reeccoom
mppiillaattiioonn dd''uunnee pprrooccéédduurree ssttoocckkééee
SQL Server peut mettre en cache le plan d'exécution d'une procédure stockée.
L'utilisateur peut demander la recompilation de la procédure stockée. Trois
possibilités sont offertes :
Si le concepteur souhaite que la procédure stockée soit recompilée à chaque
exécution, il doit indiquer cela au moment de la création de la procédure :
Syntaxe :
Create Procedure ………. WITH RECOMPILE as
Instructions
Si l'utilisateur souhaite recompiler une procédure stockée, il peut le faire au
moment de l'exécution de la procédure :
Syntaxe :
Exec ……... Procedure ………. WITH RECOMPILE
Syntaxe :
Drop Procedure Nom_Procédure
Exemple :
Drop Procedure NbrArticlesCommande
M
Mooddiiffiiccaattiioonn dd''uunnee pprrooccéédduurree ssttoocckkééee
Il existe plusieurs manières de créer une procédure stockée :
A partir d'entreprise Manager :
Accéder à la procédure stockée concernée, double cliquer dessus.
Apporter les modifications souhaitées et valider
A partir d'une application client : Une procédure stockée peut être
modifiée en utilisant l'instruction Transact-SQL Alter Procedure à partir
de n'importe quelle application client :
Syntaxe :
Alter Procedure Nom_Procédure as
Nouvelles instructions
Remarque :
Le modification du contenu d'une procédure stockée n'affecte pas les
permissions d'accès associées à cette procédure ce qui n'est pas le cas lors
d'une suppression.
@order = 'first|Last|None',
@stmttype = 'Insert|Update|Delete'
FFoonnccttiioonnnneem
meenntt ddeess ttaabblleess iinnsseerrtteedd eett ddeelleetteedd ::
Au cours des opérations d'ajout, de suppression et de modification, le
système utilise les tables temporaires inserted et deleted. Ces tables ne sont
accessibles qu'au niveau des triggers et leur contenu est perdu dès que les
triggers sont validés.
Action d'ajout : Les enregistrements ajoutés sont placés dans une table
temporaire nommée inserted ;
Action de suppression : Les enregistrements supprimés sont placés dans
une table temporaire nommée deleted.
Action de modification : L'opération de modification est interprétée
comme une opération de suppression des anciennes informations et
d'ajout des nouvelles informations. C'est pourquoi le système utilise dans
ce cas les deux tables temporaires deleted et inserted. En fait quand un
utilisateur demande à modifier des enregistrements, ceux ci sont d'abord
sauvegardés dans la table temporaire deleted et la copie modifiée est
enregistrée dans la table inserted.
FFoonnccttiioonnnneem
meenntt ddeess ttrriiggggeerrss IIN
NSST
TEEA
ADDO
OFF eett A
AFFT
TEER
R ::
Cas où seul un trigger INSTEAD OF est associé à l'action de mise à
jour (insert, delete ou update) : Dans le trigger INSTEAD OF, les
enregistrements ajoutés (respectivement modifiés ou supprimés)
apparaissent uniquement dans les tables temporaires mais pas dans la
table d'origine et si le code associé à ce trigger ne prend pas en charge
l'ajout (respectivement la modification ou la suppression) de ces
enregistrements, ils ne seront pas ajoutés (respectivement modifiés et
supprimés) même si aucune action n'annule le déclencheur.
Exemple :
Un utilisateur exécute l'action suivante :
Insert into commande values (100,'13/09/07')
Supposons qu'un trigger instead of est associé à l'action d'insertion sur la
table commande. Dans le corps de ce trigger, on affiche le contenu de la
table inserted et le contenu de la table commande.
Dans la table inserted, on remarquera la présence de la commande
numéro 100 mais dans la table commande cet enregistrement est absent
et ne sera pas ajouté à la table commande même après la fin de l'exécution
de l'action d'ajout. Ceci est dû au fait que l'exécution des triggers instead
of remplace l'action de déclenchement.
Cas où seul des triggers AFTER sont associés à l'action de mise à
jour (insert, delete ou update) : Les contraintes sont testées en premier. Si
une contrainte n'est pas vérifiée l'insertion est annulée sans que le trigger
soit exécuté. Si les contraintes sont vérifiées, le trigger est exécuté. Les
enregistrements ajoutés apparaissent et dans la table d'origine et dans les
Syntaxe :
Create Trigger Nom_Trigger
On Nom_Table
Instead Of | For Opération1, Opération2...
As
Instructions
Remarque :
Opération peut prendre Insert, Delete ou Update selon le type de trigger
à créer
Un même trigger peut être associé à une seule opération ou à plusieurs
opérations à la fois
A chaque table, peuvent être associées trois triggers au maximum : ajout,
modification et suppression (un trigger concernant deux opérations est
compté comme deux triggers)
Le corps du trigger créé peut inclure n'importe quelles instructions
excepté Create Database, Alter Database, Drop Database, Restore
Database, Restore Log et reconfigure ;
Exemples :
Le trigger suivant interdit la modification des commandes
Create Trigger Tr_Empêcher_Modif
On Commande
For Update
As
Rollback
Le trigger suivant interdit la modification du numéro de commande et
vérifie si la date saisie pour la date de commande est supérieure ou égale à
la date du jour
Create Trigger Tr_Empêcher_Modif_Numcom
On Commande
For Update
As
if update(NumCom)
Begin
Raiserror('le numéro de commande ne peut être
modifié',15,120)
Rollback
End
if update(DatCom)
Begin
if ((select count (DatCom) from inserted
Where datediff(day,datcom,getdate() )>0)<> 0)
Begin
Raiserror('La date de commande ne peut
pas être inférieur à la date en cours',15,120)
Rollback
End
End
On LigneCommande
For Delete
As
Update article set QteEnStock = QteEnStock + (select
Sum(QteCommandee) from deleted where
article.NumArt=deleted.NumArt) from article, deleted where
deleted.numart=article.numart
Delete from commande where numcom not in (select numcom
from lignecommande)
Le trigger suivant à l'ajout d'une ligne de commande vérifie si les
quantités sont disponibles et met le stock à jour
Create Trigger Tr_Ajouter_Ligne
On LigneCommande
For Insert
As
Declare @a int
set @a=(select count(numart) from inserted, article where
article.numart = inserted.numart and QteCommandee
>QteEnStock)
if (@a >0)
Begin
Raiserror('Ajout refusé. Quantités demandées non
disponibles en stock',15,120)
Rollback
End
Else
Update article set QteEnStock = QteEnStock –
(select Sum(QteCommandee) from inserted
where article.NumArt=inserted.NumArt)
From article, inserted where inserted.numart=article.numart
Le trigger suivant à la modification d'une ligne de commande vérifie si
les quantités sont disponibles et met le stock à jour
Create Trigger Tr_Modifier_Ligne
On LigneCommande
For Update
As
Declare @a int
set @a=(select count(numart) from inserted, deleted, article where
article.numart = inserted.numart and article.numart =
deleted.numart and inserted.QteCommandee >
QteEnStock+deleted.QteCommandee)
if (@a >0)
Begin
Raiserror(''Modification refusée. Quantités demandées
non disponibles en stock',15,120)
Rollback
End
Naoual ABDALLAH Page : 32 Version Octobre 2007
Module N° 12 : Programmation des Procédures Stockées et des Triggers Filière : TSDI
Else
update article set QteEnStock = QteEnStock
+ (select Sum(QteCommandee) from deleted where
deleted.NumArt=Article.NumArt)
– (select Sum(QteCommandee) from inserted where
inserted.NumArt=Article.NumArt)
From article, inserted, deleted where inserted.numart =
article.numart and article.numart = deleted.numart
Remarque :
Si le trigger déclenché effectue une opération sur une autre table, les triggers
associés à cette table sont alors déclenchés (principe de cascade)
SSuupppprreessssiioonn dd''uunn ttrriiggggeerr
Syntaxe :
Drop Trigger Nom_Trigger
M
Mooddiiffiiccaattiioonn dd''uunn ttrriiggggeerr
Syntaxe :
Alter Trigger Nom_Trigger
On Nom_Table
For Opération1, Opération2...
as
Nouvelles Instructions
Exercice 3
Soit la base de données stock citée dans la série d'exercices sur les procédures stockées.
Créer les triggers suivants :
TR 1. Qui à l'ajout de produits bruts dans la table 'Produit Brut' met à jour le champ
NbrProduitsfournis pour les fournisseurs concernés
TR 2. Qui à la suppression de produits bruts dans la table 'Produit Brut' met à jour le
champ NbrProduitsfournis pour les fournisseurs concernés
TR 3. Qui à l'ajout de mouvements dans la table mouvement met à jour le stock
TR 4. Qui à la suppression de mouvements dans la table mouvement met à jour le
stock
TR 5. Qui à la modification de mouvements dans la table mouvement met à jour le
stock
C
Crryyppttaaggee dd''uunn ttrriiggggeerr
Comme pour une procédure stockée, il est possible de rendre le code d'un trigger
inaccessible, il suffit pour cela de procéder à un cryptage. Le trigger pourra être
exécuté par un programme externe mais son contenu sera illisible que cela soit
pour son propriétaire ou pour d'autres utilisateurs.
Remarque :
Cette procédure est irréversible
Syntaxe :
Create Trigger ………. WITH ENCRYPTION as
Instructions
EXERCICES SUPPLEMENTAIRES
Exercice1
Soit la base de données suivante :
Recettes (NumRec, NomRec, MethodePreparation, TempsPreparation)
Ingrédients (NumIng, NomIng, PUIng, UniteMesureIng, NumFou)
Composition_Recette (NumRec, NumIng, QteUtilisee)
Fournisseur(NumFou, RSFou, AdrFou)
Créer les procédures stockées suivantes :
PS 1. Qui affiche la liste des ingrédients avec pour chaque ingrédient le numéro, le
nom et la raison sociale du fournisseur
PS 2. Qui affiche pour chaque recette le nombre d'ingrédients et le prix de reviens
PS 3. Qui affiche la liste des recettes qui se composent de plus de 10 ingrédients avec
pour chaque recette le numéro et le nom
PS 4. Qui reçoit un numéro de recette et qui retourne son nom
PS 5. Qui reçoit un numéro de recette. Si cette recette a au moins un ingrédient, la
procédure retourne son meilleur ingrédient (celui qui a le montant le plus bas)
sinon elle retourne "Aucun ingrédient associé"
PS 6. Qui reçoit un numéro de recette et qui affiche la liste des ingrédients
correspondant à cette recette avec pour chaque ingrédient le nom, la quantité
utilisée et le montant
PS 7. Qui reçoit un numéro de recette et qui affiche :
Son nom (Procédure PS_4)
La liste des ingrédients ( procédure PS_6)
Son meilleur ingrédient (PS_5)
PS 8. Qui reçoit un numéro de fournisseur vérifie si ce fournisseur existe. Si ce n'est
pas le cas afficher le message 'Aucun fournisseur ne porte ce numéro' Sinon
vérifier, s'il existe des ingrédients fournis par ce fournisseur si c'est le cas
afficher la liste des ingrédients associées (numéro et nom) Sinon afficher un
message 'Ce fournisseur n'a aucun ingrédient associé. Il sera supprimé' et
supprimer ce fournisseur
PS 9. Qui affiche pour chaque recette :
Un message sous la forme : "Recette : (Nom de la recette), temps de
préparation : (Temps)
La liste des ingrédients avec pour chaque ingrédient le nom et la quantité
Un message sous la forme : Sa méthode de préparation est : (Méthode)
Si le prix de reviens pour la recette est inférieur à 50 Dh afficher le message
'Prix intéressant'
Exercice2
Une clinique souhaite gérer les consultations médicales effectuées. Dans cette clinique :
Une consultation médicale est réalisée par un et un seul médecin pour un et un seul
patient
Lors d'une consultation, plusieurs médicaments peuvent être prescrits
Plusieurs médicaments peuvent avoir le même nom mais des codes différents selon
les formes pharmaceutiques disponibles (par exemple Doliprane peut exister sous
PS 7. Qui reçoit un titre de livre et qui retourne la valeur 1 s'il a déjà été emprunté et
la valeur 0 sinon
PS 8. Qui reçoit un numéro de livre et un numéro d'adhérent et qui :
Vérifie si ce numéro de livre existe. Si ce n'est pas le cas, il affiche le
message d'erreur "Ce livre n'existe pas" sous forme d'un message système ;
Vérifie si ce numéro d'adhérent existe. Si ce n'est pas le cas, il affiche le
message d'erreur "Cet adhérent n'existe pas" sous forme d'un message
système ;
Vérifie si au moins un exemplaire est disponible de ce livre. Si ce n'est pas
le cas il affiche le message d'erreur "Aucun exemplaire disponible" sous
forme d'un message système ;
Enregistre l'emprunt de ce livre pour cet adhérent (la date de l'emprunt
prend la date du jour)
PS 9. Qui pour chaque livre affiche :
Les informations sur le livre sous la forme :
Titre : ....
Nombre exemplaire : ...
Editeur : ....
La liste des emprunts effectués (Nom Adhérent et Date d'emprunt )
Le nombre d'exemplaires en cours d'emprunt
Créer les triggers suivants :
TR 1. A l'ajout de livres, ce trigger vérifie que le nombre d'exemplaires pour ces livres
est >0
TR 2. A l'ajout d'adhérents, ce trigger vérifie si ces adhérents ont plus de 7 ans d'âge
TR 3. Deux éditeurs ne peuvent pas porter le même nom et donc à l'ajout ou à la
modification d'éditeurs, ce trigger doit vérifier s'il n'existe pas déjà des éditeurs
portant les mêmes noms
TR 4. Ce trigger doit empêcher la modification des emprunts
TR 5. A l'enregistrement d'emprunts, le trigger met à jour le champ
Nbrexemplairesdisponibles pour les livres concernés
TR 6. A l'enregistrement de retours de livres, Ce trigger met à jour le champ
Nbrexemplairesdisponibles pour les livres concernés
TR 7. A la suppression d'emprunts, ce trigger doit vérifier si aucun retour
correspondant à ces emprunts n'a été enregistré. Si c'est le cas, la suppression
est validée et le champ Nbrexemplairesdisponibles pour les livres concernés
doit être mis à jour
TR 8. A la modification d'emprunts, ce trigger doit mettre à jour le champ
Nbrexemplairesdisponibles pour les livres concernés
Exercice 5
La société SAMIRAUTO est spécialisée dans la vente de véhicules automobiles. Cette
société a un ensemble de filiales réparties sur le royaume :
A chaque filiale est rattaché un ensemble de vendeurs
Les vendeurs doivent convaincre des clients de passer des commandes pour l'achat
de modèles de véhicules
Un modèle de véhicule est disponible avec un ensemble d'options
Documentation Microsoft :
- Aide Transact-SQL à partir de SQL Server
- Aide SQL Server 2000
Exercice 1
CREATE PROCEDURE PS1 AS
Select NumCostume, DesignationCostume, NomStyliste, AdrStyliste from Styliste,
Costume where Styliste.NumStyliste=Costume.NumStyliste
CREATE PROCEDURE PS2 @NumCos int AS
Select DesignationCostume, NomStyliste, AdrStyliste from Styliste, Costume where
Styliste.NumStyliste = Costume.NumStyliste and NumCostume=@NumCos
CREATE PROCEDURE PS3 @NumCos int AS
Select MembreJury.NumMembreJury, NomMembreJury, FonctionMembreJury,
NoteAttribuée from MembreJury, Notesjury where MembreJury.numMembreJury=
Notesjury.NumMembreJury and NumCostume =@NumCos
CREATE PROCEDURE PS4 @NbrCos int output AS
Set @NbrCos=(select count(NumCostume) from Costume)
CREATE PROCEDURE PS5 @NumCos int, @MJ int , @note decimal output AS
Set @note=(select Noteattribuée from Notesjury where NumCostume =@NumCos and
NumMembreJury=@MJ)
CREATE PROCEDURE PS6 @NumCos int , @M decimal output AS
Set @M=(select Avg(NoteAttribuée) from Notesjury where NumCostume =@NumCos)
Exercice 2
CREATE PROCEDURE SP1 AS
Create table ProduitBrut(CodProBrut int primary key,NomProBrut varchar(50),
PrixAchat decimal, NumFour int Foreign Key references Fournisseur)
Create table Composition (CodProFini int Foreign key references ProduitFini,
CodProBrut int Foreign Key references ProduitBrut, QteUtilisee decimal,
Constraint PK_Composition Primary Key(CodProFini, CodProBrut))
CREATE PROCEDURE SP2 AS
Select codProFini, count(CodProBrut) from composition Group by CodProFini
CREATE PROCEDURE SP3 @MaxPrix decimal output AS
Set @MaxPrix =(Select max(PrixAchat) from ProduitBrut)
CREATE PROCEDURE SP4 AS
Select CodProFini from Composition Group by CodProFini
Having Count(CodProBrut)>=2
CREATE PROCEDURE SP5 @ProBrut varchar(50), @RS varchar(50) output AS
Set @RS=(Select RSFour From Fournisseur, ProduitBrut
Where Fournisseur.NumFour= ProduitBrut.NumFour and NomProBrut=@ProBrut)
CREATE PROCEDURE SP6 @CodProFini int AS
Select * from Mouvement Where TypeMvt='S' and CodProFini=@CodProFini
CREATE PROCEDURE SP7 @CodProFini int, @TypeMvt char(1) AS
Select * from Mouvement Where TypeMvt=@TypeMvt and CodProFini=@CodProFini
CREATE PROCEDURE SP8 AS
Declare @CPF int, @QteStock decimal
Declare C1 Cursor for select CodProFini, QteEnStock from ProduitFini
open C1
Fetch next from C1 into @CPF, @QteStock
while @@fetch_status=0
Begin
Print 'La quantité en stock est :' + convert(varchar, @QteStock)
Select * from Mouvement where CodProFini=@CPF
Declare @SommeE decimal, @SommeS decimal
Set @SommeE=(Select Sum(Quantite) from Mouvement where
TypeMvt="E" and CodProFini=@CPF)
Set @SommeS=(Select Sum(Quantite) from Mouvement where
TypeMvt="S" and CodProFini=@CPF)
if @SommeE-@SommeE <>@QteStock
Print 'Stock OK'
Else
Print 'Problème de Stock'
Fetch Next from C1 into @CPF, @QteStock
end
Close C1
Deallocate C1
CREATE PROCEDURE SP9 @CPF int, @PrixReviens decimal output AS
set @PrixReviens=(select Sum(PrixAchat*Qteutilisee) from ProduitBrut PB,
Composition C where C.CodProBrut=PB.CodProBrut and CodProFini=@CPF)
CREATE PROCEDURE SP10 AS
Declare @CPF int, @NbrProduitsFinis int
Declare C1 Cursor for select CodProFini, count(CodproBrut) from Composition
group by CodProFini
Open C1
Fetch Next from C1 into @CPF, @NbrProduitsFinis
While @@fetch_status=0
Begin
Declare @PR decimal
Exec SP9 @CPF, @PR output
Print 'Le prix de reviens est : ' + convert(varchar, @PR)
Select NomProFini, PrixAchat*Qteutilisee, RSFour From ProduitBrut PB,
Composition C Where PB.CodProBrut=C.CodProBrut and CodProFini=@CPF
Print 'Le nombre de produits bruts est : ' + convert(varchar,@NbrProduitsFinis)
Fetch Next from C1 into @CPF, @NbrProduitsFinis
End
Close C1
Deallocate C1
Exercice 3
CREATE PROCEDURE SP1 AS
Select NumStagiaire, NomStagiaire From Stagiaire where NumStagiaire not in (Select
NumStagiaire from Notation, )
CREATE PROCEDURE SP2 AS
Select Filiere.NumFiliere, NomFiliere from filiere, programme
Where Filiere.numFiliere=Programme.Numfiliere
Group by Filiere.NumFiliere, NomFiliere
Having Count(NumModule)>=10
CREATE PROCEDURE SP3 @CodSecteur varchar(10) AS
Select NomModule from Module M,Programme P, Filiere F
Where M.Nummodule=P.numModule and P.NumFiliere=F.NumFiliere and
codSecteur=@CodSecteur
group by NomModule
Having count(F.numfiliere)=(select Count(numfiliere) from filiere where
CodSecteur=@CodSecteur)
CREATE PROCEDURE SP4 @NumStagiaire int AS
Select Module.NumModule, NomModule, Note,Coefficient from Module, Notation,
programme
Where Notation.NumModule=Module.NumModule and
Module.Nummodule=programme.Nummodule and numStagiaire=@NumStagiaire
CREATE PROCEDURE SP5 AS
Declare @NumSta int, @NomPreSta varchar(50), @NomFil varchar(50)
Declare C1 Cursor for Select NumStagiaire, NomStagiaire + ' ' + PrenomStagiaire,
NomFiliere from Stagiaire S, Filiere F where S.NumFiliere=F.NumFiliere
Open C1
Fetch Next from C1 into @NumSta, @NomPreSta, @NomFil
while @@fetch_status=0
Begin
Print 'Nom et Prénom : ' + @NomPreSta + ' Filière : ' + @NomFil
if exists (select NumModule from Programme P, Stagiaire S where
P.NumFiliere=S.NumFiliere and NumStagiaire=@NumSta and
NumModule not in (select NumModule from notation where
NumStagiaire=@NumSta))
Begin
Print 'En cours de traitement'
select NumModule from Programme P, Stagiaire S where
P.NumFiliere=S.NumFiliere and
NumStagiaire=@NumSta and NumModule
not in (select NumModule from notation
where NumStagiaire=@NumSta)
Fetch Next from C1 into @NumSta, @NomPreSta,
@NomFil
Continue
end
if (select count(NumModule) from notation where NumStagiaire =
@NumSta and note<3) >2
Begin
Print 'Notes Eliminatoires'
Select NomModule from Module M, Notation N where
M.NumModule=N.NumModule and
numStagiaire=@NumSta and note <3
Exercice 1
Rappel : Les triggers After ne se déclenchent pas si les contraintes d'intégrité ne
sont pas validées
Exercice 2
CREATE TRIGGER TR1 ON CLIENT FOR INSERT, UPDATE AS
if exists (select CINCli from client group by CINCli Having count(numcli)>1)
Rollback
Rollback
if exists(select inserted.numcli from inserted,compte where
inserted.numcli=compte.numcli and compte.numcpt<>inserted.numcpt and
compte.typecpt=inserted.typecpt )
Begin
Rollback
End
Exercice 3
CREATE TRIGGER TR1 ON PRODUITBRUT FOR INSERT AS
Update fournisseur set NbrProduitsfournis=NbrProduitsfournis+(select
count(Codprobrut) from inserted where
inserted.numfour= fournisseur.numfour)
from inserted,fournisseur where inserted.numfour=fournisseur.numfour