Filière 3ème ANNÉE Ingénierie Informatique et
Année
Réseaux
universitaire :
TP3 : Transact-SQL 2022/2023
Objectifs :
• T-SQL – procédures stockées / les déclencheurs
Exercice 1 :
Soit la base de données SQL server GestionCom (la même base de données de l’exercice d’initiation
de T-SQL)
o Article (NumArt, DesArt, Prix, QteEnStock, SeuilMin, SeuilMax)
o Commande (NumCom, DatCom)
o LigneCommande (NumCom, NumArt, QteCommande)
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
1-
Declare @a int, @b DateTime, @c decimal
Declare C1 Cursor for Select [Link],DatCom, Sum(PUArt*QteCommandee) From Commande
C, Article A, LigneCommande LC Where [Link]=[Link] and [Link]=[Link] group
by [Link],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- Ecrire un programme qui pour chaque commande vérifie si cette commande a au moins un
article. Si c'est le cas affiche son numéro et la liste de ses articles sinon affiche un message
d'erreur :
Aucun article pour la commande …. Elle sera supprimée et supprime cette commande
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 [Link], DesArt, PUArt, QteCommandee From Article A,
Lignecommande LC Where [Link]=[Link] and NumCom=@a
End
Fetch Next from Cur_Com into @a
End
Close Cur_Com
Deallocate Cur_Com
Exercice 2 : procédures stockées
En utilisant la base de données créer dans l’exercice 1,
1. Créer une procédure stockée qui calcule le nombre d'articles par commande.
Create Procedure SP_NbrArtparCommandes
as
SELECT NumCom, COUNT(NumArt) AS NombreArticles
FROM LigneCommande
GROUP BY NumCom
-- Exécuter cette procédure
Exec SP_NbrCommandes
2. 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
3. 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
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
4. 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 :
a. Si l'article n'existe pas ou si la quantité demandée n'est pas disponible afficher un
message d'erreur
b. Si la commande introduite en paramètre n'existe pas, la créer
c. Ajoute ensuite la ligne de commande et met le stock à jour
Create procedure SP_EnregistrerLigneCom @numCom int,@numArt int, @QTEcmd int
as
if not exists(select numArt from Article where NumArt=@numArt)
or (select [Link] from Article where NumArt=@numArt)<@QTEcmd
begin
raiserror('c est un erreur ',15,120)
end
if not exists(select * from Commande where NumCom=@numCom)
begin
set identity_insert Commande ON
insert into Commande(NumCom,DatCom) values(@numCom,GETDATE())
set identity_insert Commande OFF
insert into LigneCommande(NumCom,NumArt,QteCommandee)
values (@numCom,@numArt,@QTEcmd)
update Article set QTeEnStock=QTeEnStock-@QTEcmd where NumArt=@numArt
end
exec SP_EnregistrerLigneCom 8,3,10
5. Créer une procédure stockée nommée SP_NbrCommandes qui retourne le nombre de
commandes
Create Procedure SP_NbrCommandes @Nbr int output
as
Set @Nbr = (Select count(NumCom) from Commande)
-- Exécuter cette procédure pour afficher le nombre de commandes :
Declare @n int Exec SP_NbrCommandes @n Output
Print 'Le nombre de commandes : ' + convert(varchar,@n)
6. Créer une procédure stockée nommée SP_NbrArtCom qui retourne le nombre d'articles d'une
commande dont le numéro est donné en paramètre.
Create Procedure SP_NbrArtCom @NumCom int, @Nbr int output
as
Set @Nbr = (Select count(NumArt) from LigneCommande where NumCom=@NumCom)
--Exécuter cette procédure pour afficher le nombre d'articles de la commande numéro 1
:
Declare @n int
Exec SP_NbrArtCom 1, @n Output
Print 'Le nombre d articles de la commande numéro 1 est : ' + convert(varchar,@n)
/*Ou
Declare @nc int, @n int
Set @nc=1
Exec SP_NbrArtCom @nc, @n Output
Print 'Le nombre d'articles de la commande numéro ' + convert(varchar,@nc) + ' est :
' + convert(varchar,@n)'
*/
7. Créer une procédure stockée nommée SP_TypePeriode qui retourne le type de la période en
fonction du nombre de commande. Si le nombre de commandes est supérieur à 100, le type sera
'Période rouge'. Si le nombre de commandes est entre 50 et 100 le type sera 'Période jaune' sinon
le type sera 'Période blanche' (exploiter la procédure SP_NbrCommandes) :
Create Procedure SP_TypePeriode @TypePer varchar(50) output
as
Declare @NbrCom int
Exec SP_NbrCommandes @NbrCom output
If @NbrCom >100
Set @TypePer='Période Rouge'
Else
Begin
If @NbrCom <50
Set @TypePer= 'Période blanche'
Else
Set @TypePer= 'Période Jaune'
End
8. 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.
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
Exercice 3 : (Triggers)
En utilisant la base de données créer dans l’exercice 1,
1- Créer des triggers INSTEAD OF pour Empêcher l'insertion des commandes avec une date de
commande dans le futur.
CREATE TRIGGER T_Q1
ON Commande
INSTEAD OF INSERT
AS
BEGIN
-- Vérifier chaque ligne insérée
IF EXISTS (SELECT * FROM inserted WHERE DatCom > GETDATE())
BEGIN
-- Annuler l'insertion des commandes avec une date future
RAISERROR ('L''insertion de commandes avec une date future est interdite.',
16, 1)
RETURN
END
-- Insérer les lignes avec une date valide
INSERT INTO Commande (NumCom, DatCom)
SELECT NumCom, DatCom
FROM inserted
END
2- Créer un trigger suivant empêche la suppression des commandes ayant des articles associés
CREATE TRIGGER T_Q2
ON Commande
INSTEAD OF DELETE
AS
BEGIN
-- Vérifier chaque ligne supprimée
IF EXISTS (SELECT * FROM deleted d JOIN LigneCommande lc ON [Link] = [Link])
BEGIN
-- Annuler la suppression des commandes ayant des articles associés
RAISERROR ('La suppression des commandes ayant des articles associés est
interdite.', 16, 1)
RETURN
END
-- Supprimer les lignes sans articles associés
DELETE FROM Commande
WHERE NumCom IN (SELECT NumCom FROM deleted)
END
3- Créer un trigger suivant à la suppression d'une ligne de commande, remet à jour le stock et
vérifie s'il s'agit de la dernière ligne pour cette commande. Si c'est le cas la commande est
supprimée
CREATE TRIGGER T_Q3
ON LigneCommande
AFTER DELETE
AS
BEGIN
-- Mettre à jour le stock pour chaque ligne supprimée
UPDATE Article
SET QteEnStock = [Link] + [Link]
FROM Article
INNER JOIN deleted ON [Link] = [Link]
-- Vérifier si la ligne supprimée est la dernière pour chaque commande
DELETE FROM Commande c
where numcom not in (select numcom from lignecommande)
END
4- Créer un trigger suivant à l'ajout d'une ligne de commande vérifie si les quantités sont
disponibles et met le stock à jour
CREATE TRIGGER T_Q4
ON LigneCommande
AFTER INSERT
AS
BEGIN
-- Vérifier les quantités disponibles pour chaque ligne insérée
IF EXISTS (
SELECT [Link]
from inserted i, article a where [Link] = [Link]
and [Link] > [Link]
)
BEGIN
-- Annuler l'insertion si les quantités ne sont pas disponibles
RAISERROR ('Les quantités demandées ne sont pas disponibles en stock.', 16,
1)
ROLLBACK TRANSACTION
RETURN
END
-- Mettre à jour le stock pour chaque ligne insérée
UPDATE a
SET QteEnStock = [Link] - [Link]
FROM Article a
INNER JOIN inserted i ON [Link] = [Link]
END
5- Créer un trigger suivant à la modification d'une ligne de commande vérifie si les quantités sont
disponibles et met le stock à jour
CREATE TRIGGER T_Q5
ON LigneCommande
AFTER UPDATE
AS
BEGIN
-- Vérifier les quantités disponibles pour chaque ligne modifiée
IF EXISTS (
SELECT [Link]
FROM LigneCommande lc
INNER JOIN Article a ON [Link] = [Link]
INNER JOIN inserted i ON [Link] = [Link] AND [Link] = [Link]
WHERE [Link] < [Link]
)
BEGIN
-- Annuler la modification si les quantités ne sont pas disponibles
RAISERROR ('Les quantités demandées ne sont pas disponibles en stock.', 16,
1)
ROLLBACK TRANSACTION
RETURN
END
-- Mettre à jour le stock pour chaque ligne modifiée
UPDATE a
SET QteEnStock = [Link] + [Link] - [Link]
FROM Article a
INNER JOIN deleted d ON [Link] = [Link]
INNER JOIN inserted i ON [Link] = [Link]
END