TRIGGERS et PROCEDURES STOCKEES en Transact SQL

BD GESTCMD

Modèle relationnel :
Clients (nucli, nomcli, precli, adcli, cpcli, vilcli, telcli)
Categ (codcat, libcat)
Articles (refart,desart, phart, codcat#)
Stock (refart, coddépot, qtesto, stomini, stomaxi)
Commandes (nucmd, datecmd, txremise, nucli#, etatcmd)
Contraintes : pour nucmd, les n° doivent être affectés dans l'ordre de
création, etatcmd ne peut prendre que les valeurs : EC(encours), LP(livrée
partiellement), LI(livrée) ;SO(soldée)
Ligcmd (nucmd, nulig, refart#, qtecmd)
Histofac ( nufac, datefac, nucmd#, totht, étatfac)
Contraintes : étatfac prend les valeurs NR(non réglée) ; RP 'réglée
partiellement), RT (réglée totalement)
Principe des mises à jour :
Les mises à jour sont enregistrées en mémoires dans des tables logiques :
inserted, deleted ou updated selon l'instruction sql utilisée INSERT,
DELETE ou UPDATE.
Insert : les nouvelles lignes ajoutées le sont dans la table d'origine
et dans la table inserted de la table origine.
Insert into client (nucli, nomcli,precli) values (4, 'Martin','Marc')
Table clients origine
nucli Nomcli precli
1 Albert charles
2 Dupontleon
3 DurandJean
4 Martin Marc
Table inserted :
nucli Nomcli precli
4 Martin Marc

Delete : suppression des lignes dans la table origine et ajout des

lignes supprimées dans la table deleted
Delete from client where nomcli = 'Dupont'
Table clients origine
nucli Nomcli precli
1 Albert charles
3 Durand Jean
4 Martin Marc

Table Deleted
nucli Nomcli precli
2 Dupont leon

Update : les lignes d'origine (sans modif) sont dans la table

Deleted, les lignes modifiées sont dans Inserted et dans la table
d'origine.
Update clients set precli = 'zoe' where nomcli = 'Durand'
Table clients origine
nucli Nomcli precli
1 Albert charles
3 Durand Zoe
4 Martin Marc

Table Deleted
nucli Nomcli precli
3 Durand Jean

Table Inserted :
nucli Nomcli precli
3 Durand Zoe
EXERCICE " COMTES BANCAIRES " : Procédures stockées
Contexte de travail
Vous participez à la réalisation d'un logiciel permettant de gérer
les comptes bancaires d'un particulier.
Le fonctionnement prévu est le suivant :
Dans un premier temps, l'utilisateur définit les différents comptes
à gérer en déclarant pour chacun le numéro de compte, le nom de
la banque, ...
L'utilisateur saisit ensuite chacune des opérations réalisées sur
l'un de ces comptes. Une opération correspond à un débit
(paiement par chèque de la facture de téléphone, retrait d'espèces
à l'aide d'une carte bancaire, ...) ou à un crédit (dépôt à la banque
de chèques reçus par exemple).
Chaque fin de mois, l'utilisateur reçoit un "relevé de compte" (un
par compte bancaire). Il effectue alors le rapprochement entre les
opérations qu'il a saisies et celles constatées par la banque :
- Pointage des opérations saisies apparaissant sur le relevé. Ce
pointage consiste à mémoriser le fait qu'une opération a été
constatée par la banque. L'opération est dite "pointée" et le
numéro du relevé est mémorisé.
- Ajout des opérations non saisies apparaissant sur le relevé
(réception d'un virement correspondant au salaire mensuel par
exemple). Ces opérations sont automatiquement pointées
puisqu'elles ont été constatées par la banque.
Solde des comptes
Les opérations permettent de calculer le solde d'un compte, différence entre la
somme des
opérations de crédit et la somme des opérations de débit.
En fait, chaque compte possède deux soldes :
- Le solde réel qui prend en considération toutes les opérations saisies par
l'utilisateur, qu'elles aient été constatées ou non par la banque.
- Le solde en banque qui ne prend en considération que les opérations constatées
par la banque, c'est-à-dire ayant fait l'objet d'un "pointage" à partir d'un relevé.
L'application est développée autour d'un SGBDR. Au niveau physique, on trouve
notamment deux tables :
- COMPTE, table mémorisant les informations concernant les comptes courants.
- OPERATION, table mémorisant chaque opération concernant un compte courant.
Le calcul des soldes des comptes conduit à additionner un grand nombre
d'opérations, et ce depuis la mise en service de l'application. Pour éviter ces
calculs, il a été décidé de mémoriser les soldes réel et "en banque" dans la table
COMPTE.
Dès lors se pose le problème de la cohérence entre les opérations et le solde des
comptes. La solution adoptée est le recours à un ensemble de procédures stockées
et de déclencheurs (triggers) destinés à maintenir à jour les soldes dans la table
COMPTE.
Schéma de la base de données
BANQUE (Bid, Bnom)
Bid : clé primaire
COMPTE (Cid, Cnumero, Clibelle, CsoldeReel, CsoldeBanque, Cbanque)
Cid : clé primaire
Cbanque : clé étrangère en référence à Bid de BANQUE
OPERATION (Oid, Odate, Omontant, Osens, Oreleve, Ocompte)
Oid : clé primaire
Oreleve : clé étrangère en référence à Rid de RELEVE
Ocompte : clé étrangère en référence à Cid de COMPTE
RELEVE(Rid, Rdate, Rcompte)
Rid : clé primaire
Rcompte : clé étrangère en référence à Cid de COMPTE
Remarques :
- CsoldeReel et CsoldeBanque représentent respectivement le "solde réel" et le
"solde en banque" du compte.
- Osens est un caractère mémorisant la nature de l'opération ('d' pour débit,
opération diminuant le solde du compte ; 'c' pour crédit, opération augmentant le
solde du compte).
- Oreleve contient le numéro du relevé (Rid) ayant permis de pointer l'opération.
Cet attribut contient la valeur NULL si l'opération n'a pas encore été pointée.
Extrait de la documentation du SGBD utilisé
> Création d'une procédure stockée
create procedure <nom_procédure>
parameters
<nom_paramètre> : <type_paramètre> // n fois
variables
<nom_variable> : <type_variable> // n fois
 begin
// liste d'instructions
end
> Création d'un déclencheur
create trigger <nom_trigger> on (insert | update | delete ) for
<nom_table>
variables
<nom_variable> : <type_variable> // n fois
begin
// liste d'instructions
end
> Syntaxe du langage de définition des procédures stockées
Le langage permet l'utilisation de tous les types de données courants pour la
déclaration des paramètres et des variables. Les instructions "classiques" sont
présentes : affectation, conditionnelle, boucle, appel de procédure, ...
> Intégration de SQL
Le langage de définition des procédures stockées supporte l'utilisation de requêtes
SQL selon la norme d'intégration de SQL dans un langage hôte (variables hôtes
notamment).
> Accès au n-uplet concerné par un trigger
Lors de l'écriture d'un trigger, il est possible d'accéder aux valeurs des attributs du
n-uplet concerné par l'opération. Le tableau ci-dessous résume les possibilités.
Opération Syntaxe Signification
INSERT NEW.<attribut> Valeur de l'attribut indiquée dans
l'instruction INSERT
UPDATE OLD.<attribut> Valeur de l'attribut avant l'instruction UPDATE
UPDATE NEW.<attribut> Nouvelle valeur (indiquée dans l'instruction
UPDATE)
DELETE OLD.<attribut> Valeur de l'attribut avant la suppression du n-uplet
Travail à Réaliser
1.Présentez le schéma entité-association correspondant au schéma de la base de
données.
2. Rédigez l'ensemble des procédures stockées et des déclencheurs nécessaires à
la gestion des informations calculées CsoldeReel et CsoldeBanque.
3. Discutez de l'intérêt de cette solution.
NB : Old est ce que l'on appelle Deleted en SQLSERVER, New
correspond à Inserted
Correction
Question 1 :
Remarque : la présence des propriétés calculées CsoldeReel et
CsoldeBanque sur le schéma est discutable, mais elles permettent une
meilleure appréhension des réalités manipulées, pour le développeur
comme pour l'utilisateur.

Correction exercice " comptes bancaires : procédures

stockées "Question 2 :
CREATE PROCEDURE MAJSOLDEBANQUE
PARAMETERS
@idCompte : entier
@sens : caractère
@montant : réel
begin
if (sens='c') then
update compte set CsoldeBanque=CsoldeBanque+@montant
where Cid=@idCompte
else
 update compte set CsoldeBanque=CsoldeBanque-
@montant
where Cid=@idCompte
endif
end
CREATE PROCEDURE MAJSOLDEREEL
PARAMETERS
@idCompte entier
@sens : caractère
@montant : réel
begin
if (@sens='c') then
update compte set CsoldeReel=CsoldeReel+@montant
where Cid=@idCompte
else
update compte set CsoldeReel=CsoldeReel-@montant
where Cid=@idCompte
endif
end
CREATE TRIGGER trigInsOperation ON INSERT FOR
OPERATION
VARIABLES
@sens : caractère
begin
majSoldeReel(new.Ocompte, new.Osens, new.Omontant)
if (new.Oreleve is not null) then
majSoldeBanque(new.Ocompte, new.Osens,
new.Omontant)
endif
end
CREATE TRIGGER trigUpdateOperation ON UPDATE FOR
OPERATION
VARIABLES
@sens : caractère
begin
// Annulation du montant originel de l'opération en affectant
// ce même montant en sens inverse
if (old.Osens='c') then @sens='d' else @sens='c' endif
majSoldeReel(old.Ocompte, @sens, old.Omontant)
if (old.Oreleve is not null) then
majSoldeBanque(old.Ocompte, @sens, old.Omontant)
endif
// MAJ du compte par le nouveau montant
majSoldeReel(new.Ocompte, new.Osens, new.Omontant)
if (new.Oreleve is not null) then
majSoldeBanque(new.Ocompte, new.Osens,
new.Omontant)
 endif
end
CREATE TRIGGER trigDelOperation ON DELETE FOR
OPERATION
VARIABLES
@sens : caractère
begin
if (old.Osens='c') then @sens='d' else @sens='c' endif
majSoldeReel(old.Ocompte, @sens, old.Omontant)
if (old.Oreleve is not null) then
majSoldeBanque(old.Ocompte, @sens, old.Omontant)
endif
end
Question 3 :
Le fait de maintenir ces informations calculées permettra
d'obtenir de meilleurs temps de réponse lors de la
consultation des comptes.
La centralisation du calcul au niveau du SGBD permet
d'éviter de nombreuses erreurs de codage dans les
différents écrans ou états utilisant ces informations. De
la même manière, la maintenabilité de l'application se
trouve améliorée.
Dans le cas d'une utilisation de la base de données dans
une architecture client/serveur (ce qui est assez
improbable dans le contexte décrit), la solution des
procédures stockées permet également de minimiser le
trafic sur le réseau et d'optimiser les temps d'exécution.