Tsql

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

B AS E S D E D ON N É ES AVA N C É ES I do not fear computers. I fear the lack of them. Isaac Asimov

Page : 1

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

A. LES BASES DU LANGAGE TRANSACT SQL : 1) INTRODUCTION

Le transact SQL ou TSql est un langage dédié à l’accés aux données. Il est une extension améliorée du langage SQL : • Les variables • Les structures conditionnelles (if /then/ switch…) • Les pointeurs sur les lignes (curseurs) • …

2) L E S V A R I A B L E S : a) D E C L A R A T I O N : DECLARE @nom_variable type Exemple : DECLARE @x int La variable @x est locale car elle est visible juste pour la requête qui suit.

b) T Y P E S D E S D O N N É E S : Types numériques : • Entiers : o Bigint entier sur 64 bits signé o int entière sur 32 bits signé o smallint entier sur 16 bits non signé (équivalent à un octet) o Bigint entier sur 64 bits signé • Binaire : o Bit : 0 ou 1 • Décimaux et numériques : o Decimal : nombre à précision fixe o Numeric : equivalent au type decimal • Monétaires o Money : 4 chiffres apres la virgule o Smallmoney • Float • Real • Date : o Datetime : date et heure allant du 1er janvier 1753 au 31 decembre 9999 avec une precision de 3.33 millisecondes o Smalldatetime : date et heure allant du 1er janvier 1900 au 6 juin 2079 • Chaînes de caractères : o Char : chaine de caracteres fixe o Varchar chaine de caractéres à longueur variable o Text : chaine de carcteres de taille pouvant aller jusqu’à 2^31 -1 caracteres o Nchar : chaine incluant des caracteres unicodes o ………. REMARQUE :

On peut créer nos propres types de données. Par exemple, pour représenter le code postal , on utilise couramment varchar(6) comme type de données .On peut créer un type nommé codePost par exemple pour mapper ce type . pour cela, on exécute la procédure sp_addtype qui prend comme paramètres le nom du type à créer et le nom du type de base : EXEC sp_addtype @typename=’codePost’ , @physType=’varchar(6)’

Page : 2

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

C. UTILISATION DES VARIABLES

Avant d’utiliser une variable il faut la déclarer : Exemples : Declare @x float Select @x = pa From article where numArticle = 5 Select @x Declare @moyenne_prix real Select @moyenne_prix = avg(pv) From article Select @moyenne_prix Declare @nb_clients int Set @nb_clients = (select count(cmdClient) from commande) Select @nb_clients La différence avec les deux exemples précédents est que cette fois on utilise SET au lieu de Select. On peut egalement utiliser plusieurs variables dans la meme requete : Declare @moyenne_prix real Declare @nb_articles real Select @nb_articles = cont(numeroArticle) , @moyenne_prix = avg(pa) From article 3) LES STRUCTURES CONDITIONNELLES : A) IF - ELSE

Exemples : 1) declare @nbArticle real select @nbArticle = count(numArticle) from article if (@nbArticle < 100 ) print 'moins de 100 references dans le stock' else print 'le stock contient plus de 100 references' La fonction PRINT permet d’afficher le message 2) declare @nbArticle real select @nbArticle = count(numArticle) from article if (@nbArticle < 100 ) begin print 'moins de 100 references dans le stock' print @nbArticle end else begin print 'le stock contient plus de 100 references' print @nbArticle end 3) declare @nbArticle real select @nbArticle = count(numArticle) from article if (@nbArticle < 100 )

Page : 3

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

begin print 'moins de 100 references dans le stock' print @nbArticle end else begin print 'le stock contient plus de 100 references' print @nbArticle declare @maxPrix real set @maxPrix = (select max(pv) from article) print @maxPrixend end B) CASE i) F O R M A T 1 : Il existe deux utilisation de CASE en TSQl : CASE simple et CASe de recherche : Le CASE simple est l’équivalent de select case en vb ou case en pascal ou même de switch en C. Exemple : declare @nbArticle real, @msg varchar(20) select @nbArticle = count(numArticle) from article select @msg = case (@nbArticle) when 2 then 'deux articles' when 3 then 'trois articles' else 'autres' end print 'le nombre : ' + @msg La structure Case retourne la valeur sp écifiée après THEN lorsque l’expression spécifiée est égale à la valeur donnée avec WHEN. Si aucune valeur n’est trouvée, on renvoie celle spécifiée par Else

ii) F O R M A T 2 : Exemple : declare @nbArticle real, @msg varchar(20) select @nbArticle = count(numArticle) from article select @msg = case when @nbArticle = 2 then 'deux articles' when @nbArticle= 3 then 'trois articles' else 'autres' end print 'le nombre : ' + @msg

Pour cette structure, on ne spécifie pas de variable dans CASE. WHEN introduit des booléens et on sélectionne le bloc correspondant au WHEN qui renvoie vraie ou bien le bloc ELSE. 4) LES STRUCTURES RÉPÉTITIVES : i) W H I L E :

Exemple : declare @nb int set @nb = 0 while @nb <10 begin print ' iteration numero :' + convert (varchar(3),@nb)

Page : 4

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

set @nb =@nb+1 end Remarque : On peut introduire Break ou/et continue pour influencer l’exécution de la boucle Exemple : declare @nb int set @nb = 0 while @nb <10 begin print ' iteration numero :' + convert (varchar(3),@nb) set @nb =@nb+1 if @nb = 5 begin print 'Un saut' Continue end else if @nb = 8 begin print 'On quitte la boucle' Break end end

5) L E S B R A N C H E M E N T S : G O T O - L A B E L Exemple :

déclare @nb int set @nb = 0 debut: print 'on a pas encore dépasser 10' set @nb = 1+@nb if @nb <3 goto debut

On commence par créer un label (debut) puis sauter vers lui

6) U T I L I S A T I O N D E S I N S T R U C T I O N S S Q L : On peut utiliser des blocs d’instructions sql afin de remlacer l’utilisation des variables locales dans certains cas de figures. Exemples : Select pv as 'prix de vente', case when pa< 100 then 'PAS CHER' when pa < 130 then 'MOINS CHER' else 'TROP CHER' end as 'commentaire' From article

Page : 5

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

B. LES PROCEDURES STOCKÉES: 1. INTRODUCTION Une procédure stockée ou procédure mémorisée ou stored procedure est un ensemble d’instructions SQL pré compilées et mémorisées dans le dictionnaire. Elle sera exécutée à chaque fois qu’on l’appelle. Elles peuvent s’apparenter à des fonctions du cote serveur. en effet, on va programmer des requets avec d’eventuels arguments, retours, etc. 2. DECLARATION Synatxe de creation d’une procedure stockée est : CREATE PROCEDURE NOM_PROCEDURE LES PARAMETRES AS INSTRUCTIONS SQL Syntaxe d’appel d’une procedure EXEC NOM_PROCEDURE VALEURS DES ARGUMENTS Exemple : Soit la table produit (numProduit, libelle, prixUnitaire) CREATE PROCEDURE lesProduits AS Select * from produit exec lesProduits 3. MODIFICATION D’UNE PROCEDURE STOCKÉE La Syntaxe de modification est : ALTER PROCEDURE NOM_PROCEDURE AS le nouveau bloc d’instructions sql Exemple : alter procedure lesProduits as select numArt from art 4. SUPPRESSION D’UNE PROCEDURE STOCKÉE La syntaxe de suppression est : DROP PROCEDURE NOM_PROCEDURE 5. PROCEDURES PARAMETREES Pour déclarer un paramètre, il suffit donc de le spécifier dans l’entête de la procédure suivant la syntaxe suivante : @nom_parametre type OUTPUT Le nom du paramètre précédé par @ et suivi par son type choisi parmi les types SQL, on peut ajouter une valeur par défaut qui est optionnelle, puis la direction OUTPUT qui permet d’indiquer que c’est un paramètre de sortie. Exemples : 1. CREATE PROCEDURE newProduit (@num INT, @lib varCHAR(35),@pu real) AS INSERT INTO produit (numProduit, libelle, prixUnitaire) VALUES(@num, @lib, @pu) Exec newProduit 100,’Ecran plat’, 4500 2. CREATE PROCEDURE listeProduits (@num Int =null) AS if @num is null begin select * from produit end

Page : 6

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

else begin select * from produit where numArt = @num end exec listeProduits 100 exec listeProduits 3 create procedure moyenne @moy real OUTPUT as set @moy=(select avg(prixunitaire) from produitt). declare @moye real, @a real exec moyenne @moy =@a output select @a Exercices : 1. écrire une procédure stockée qui affiche les libelles des produits dans le stock est < 10 2. écrire une procédure stockée qui admet en paramètre un numéro d’article est retourne : a. le libelle équivalent, si l’utilisateur passe une valeur lors de l’exécution de la procédure b. retourne tous les libelles des produits de la base de données sinon 3. Ecrire une procédure qui permet de supprimer un article de la commande d’un client dont les numéros du client et de l’article sont passés en paramètres. 4. écrire une procédure stockée qui calcule la valeur totale du stock de l’entreprise, le coût total du stock et la valeur du gain éventuel (la valeur du stock est la somme des quantités multipliées par les prix de vente, le coût total du stock est la somme des quantités multipliées par les prix d’achats, la valeur du gain est la valeur en stock – le coût) 5. écrire une procédure qui permet de mettre à jour le stock après une opération de vente de produits, la procédure admet en paramètre le numéro d’article à vendre et la quantité à vendre puis retourne un message suivant les cas : (b) « Opération impossible », si la quantité est > stock de l’article (c) « Besoin en réapprovisionnement », si la quantité - stock < 10 (d) « Opération effectuée avec succès, la nouvelle valeur du stock est : « puis affiche la nouvelle valeur du stock

Page : 7

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

C. LES FONCTIONS: 1. INTRODUCTION Une fonction est une procédure qui renvoie une valeur. La syntaxe est assez simple : CREATE FUNCTION nom_fonction RETURNS type_résultant [ AS ] BEGIN code RETURN valeur_résultante END

( [ { @parametre1 type [ = valeur_défaut ] } [ , @parametre2 ... ] ] )

2. EXPLICATIONS DE LA SYNTAXE · name : le nom de la fonction · @parameterX : un paramètre de la fonction · typeX : le type du paramètre · type : le paramètre de retour de la fonction · code : les instructions SQL que l'on va effectuer · valeur_résultante : la valeur que va retourner la fonction

3. E X E M P L E S : 1. exemple de fonction qui prenne deux nombres en paramètres, qui les additionne et qui renvoie le résultat : create function return_addition (@nbre1 int, @nbre2 int) returns int begin return @nbre1 + @nbre2 end Appel : Pour appeler une fonction, il faut utiliser son nom à deux composantes (owner.name), il suffit de l'utiliser tel quel : PRINT dbo.return_addition(1,3) 2. exemple de function qui prenne en parameters le code d’un article et renvoit la quantité en stock create function st (@y int) returns int as begin declare @u int set @u = (select stock from article where numarticle = @y) return @u end Appel : Le stock de l’article numero 1 : SELECT dbo.st (1) AS ‘stock article' Le stock de l’article numero 4 : SELECT dbo.st (4) AS ‘stock article'

Page : 8

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

D. LES CURSEURS: 1. INTRODUCTION Le curseur est un mécanisme de mise en mémoire en tampon permettant de parcourir les lignes d'enregistrements du résultat renvoyé par une requête. Les curseurs sont envoyés par MS-SQL Server tout le temps, mais on ne voit pas le mécanisme se passer, ainsi lors d'une requête SELECT, SQL Server va employer des curseurs. 2. DECLARATION ET UTILISATION Pour utiliser un curseur, il faut commencer par le déclarer. le syntaxe de déclaration est : DECLARE nom CURSOR FOR SELECT Code Ensuite, il faut ouvrir ce curseur avec OPEN nom et ne pas oublier de le fermer à la fin avec CLOSE nom. Il faut aussi utiliser DEALLOCATE pour libérer la mémoire du curseur. Pour récupérer les valeurs actuelles contenues dans le curseur, il faut employer : FETCH name INTO @value1, @value2 # Cela va stocker les valeurs actuelles de l'enregistrement courant dans les variables @valueX, qu'il ne faut surtout pas oublier de déclarer. Exemple Declare cur cursor local For Select * from deplacement open cur fetch next from cur while @@fetch_status=0 begin fetch next from cur end close cur deallocate cur Remarques: On peut néanmoins utiliser FETCH pour d'autres choses : · Aller à la première ligne : FETCH FIRST FROM curseur_nom · Aller à la dernière ligne : FETCH LAST FROM curseur_nom · Aller à la ligne suivante : FETCH NEXT FROM curseur_nom · Aller à la ligne précédente : FETCH PRIOR FROM curseur_nom · Aller à la ligne X : FETCH ABSOLUTE ligne FROM curseur_nom · Aller à X lignes plus loin que l'actuelle : FETCH RELATIVE ligne FROM curseur_nom Pour parcourir un curseur, on peut employer une boucle WHILE qui teste la valeur de la fonction @@FETCH_STATUS qui renvoie 0 tant que l'on n'est pas à la fin. Exemple : declare @lib varchar(50) declare curseur_article cursor for select libelle from article open curseur_auteurs fetch curseur_auteur into @lib while @@fetch_status = 0 begin print @lib fetch curseur_auteurs into @lib end CLOSE curseur_auteurs DEALLOCATE curseur_auteurs

Page : 9

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

On peut bien entendu imbriquer plusieurs curseurs les uns dans les autres pour des choses plus compliquées. Concrètement, maintenant que nous avons vu comment fonctionnait un curseur et comment l'employer, que fait-il de plus qu'une simple requête ? Il permet surtout d'intervenir sur le résultat de la requête. On peut intervenir sur chaque valeur retournée, on peut modifier ces valeurs ou supprimer des lignes. On peut aussi réaliser des opérations avec ces données avant qu'elles arrivent au programme qui les utilise, c'est à dire des calculs de somme, des maximums, des modifications de date, des formatages de chaînes de caractères. Un exemple intéressant est le parcours avec rupture, c'est à dire parcourir et si on a déjà eu une fois cet objet on ne le réaffiche pas. Dans l'exemple que je vais vous présenter, on affiche tous les genres, les acteurs par genre et pour chaque acteur les livres qu'ils ont écrits. On emploie des ruptures pour vérifier que l'on n'a pas déjà affiché une fois cet élément :

3. F O N C T I O N S D E S C U R S E U R S Ill y a trois fonctions intéressantes concernant les curseurs : 

@  @FETCH_STATUS : Renvoie l'état de la dernière instruction FETCH effectuée sur un curseur. Elle renvoie 0 si tout s'est bien passé, -1 s'il n'y a plus de lignes et -2 si la ligne est manquante.



@  @CURSOR_ROWS : Renvoie le nombre de lignes se trouvant actuellement dans le dernier curseur ouvert. Renvoie 0 s'il n'y a pas de curseurs ouverts ou plus de ligne dans le dernier curseur. Renvoie un nombre négatif si le curseur a été ouvert de manière asynchrone (voir config de SQL Server)



CURSOR_STATUS : Nous permet de vérifier qu'une procédure à bien renvoyé un curseur avec un jeu de données. Je ne vais pas m'étendre sur cette fonction, vu sa complexité, référez-vous à la doc si vous en avez besoin.

Page : 10

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

C. LES TRIGGERS: 1. INTRODUCTION Un trigger est une procédure stockée qui se lance automatiquement lorsqu'un événement se produit. Par événement, on entend toute modification des données se trouvant dans les tables. On s'en sert pour contrôler ou à appliquer des contraintes qu'il est impossible de formuler de façon déclarative. 2. DECLARATION La syntaxe de création d’un trigger est : CREATE TRIGGER nom_trigger ON nom_table {FOR {INSERT , UPDATE , DELETE} AS instructions_SQL Explications:  CREATE TRIGGER indique que l’on créé un trigger.  trigger_name : nom que l’on souhaite donné au trigger.  ON {table | view} : précise sur quelle table ou quelle vue s’applique le trigger.  {FOR | AFTER | INSTEAD OF} : précise le comportement du trigger.  { [ INSERT ] [ , ] [ UPDATE ] [ , ] DELETE ] } Précise le ou les évènements déclancheurs.  AS : introduit le code SQL du trigger Lors de la déclaration d’un trigger on associe un code SQL avec une table (ou une vue), un évènement déclencheur et un comportement 3. LES ÉVÈNEMENTS DÉCLENCHEURS DES TRIGGERS Il y a trois évènements susceptibles de déclancher un trigger, ils correspondent aux trois actions possible sur une ligne d’une table INSERT, DELETE et UPDATE. Un trigger doit spécifier au moins l’un de ces comportements mais peut tout à fait être déclanché par DEUX ou TROIS évènements, il suffit pour cela de les séparer par une virgule dans la déclaration : CREATE TRIGGER trigger_name ON table AFTER INSERT, UPDATE, DELETE AS […] L’évènement INSERT : L’évènement insert est déclanché lors de l’ajout d’un enregistrement. L’évènement UPDATE : L’évènement update est déclanché lors de la modification d’un enregistrement. L’évènement DELETE : L’évènement DELETE est déclanché lors de la suppression d’un enregistrement. 4. LES MODES DE COMPORTEMENT DES TRIGGERS Les triggers possèdent deux comportements différents, soit ils effectuent des opérations à la suite de l’action déclenchante, soit ils effectuent des opérations à la place de l’action déclenchante AFTER : Le comportement AFTER indique que le trigger est déclanché après l’action déclenchante : Lors d’une action de suppression : d’abord les enregistrements sont supprimés de la table, ensuite les contraintes sont validées, enfin le trigger est déclenché. Il peut y avoir plusieurs triggers AFTER sur chaque événement. FOR : FOR est considéré comme équivalent à AFTER. INSTEAD OF : Le comportement INSTEAD OF indique que le trigger est déclanché à la place de l’action déclenchante. Deux triggers INSTEAD OF d’une même table ne peuvent se déclancher par le même évènement.

Page : 11

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

5. LES PSEUDO TABLES Un problème se pose maintenant comment récupérer des informations sur l’opération déclenchante ? Pour cela on utilise les pseudo tables INSERTED et DELETED. Les pseudo tables possèdent la même définition que la table sur laquelle le trigger est appliqué. Elles ne permettent que des opérations de sélection (Pas de DELETE, INSERT, UPDATE). 5.1. La pseudo table Inserted

La pseudo table Inserted possède la même définition que la table sur laquelle le trigger est appliqué. Elle représente soit les nouveaux enregistrements dans le cas de l’évènement INSERT soit les nouvelles valeurs des enregistrements dans le cas de l’évènement UPDATE. 5.2. La pseudo table Deleted

Elle représente soit les enregistrements supprimés dans le cas de l’évènement DELETE, soit les anciennes valeurs des enregistrements dans le cas de l’évènement UPDATE. Exemples : soit la table client (numClient, nom, prenom,CA) Table origine numClient Nom

prenom CA

1

Albert

charles 12.000

2

Dupont

leon

21000

3

Durand

Jean

32000

1. Soit la requête d’insertion suivante : Insert into client values (4, 'Martin','Marc,7899') . Les nouvelles lignes ajoutées le sont dans la table d'origine et dans la table inserted de la table origine. Table inserted : numClient

Nom

prenom

CA

4

Martin

Marc

7899

Table d’origine numClient Nom

prenom CA

1

Albert

charles

12.000

2

Dupont

leon

21000

3

Durand

Jean

32000

4

Martin

Marc

7899

2. Soit la requête de suppression Delete from client where nomcli = 'Dupont'. La suppression des lignes dans la table origine et ajout des lignes supprimées dans la table deleted Table d’origine numClient Nom

prenom CA

1

Albert

charles 12.000

3

Durand Jean

Page : 12

32000

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

4

Martin Marc

7899

Table Deleted numClient Nom 2

prenom CA

Dupont leon

21000

3. Soit la requete Update suivante : Update clients set precli = 'zoe' where nomcli = 'Durand' . Les lignes d'origine (sans modif) sont dans la table Deleted, les lignes modifiées sont dans Inserted et dans la table d'origine. Table clients origine numClient Nom

prenom CA

1

Albert charles 12.000

3

Durand zoe

32000

4

Martin Marc

7899

Table Deleted numClient Nom 3

prenom CA

Durand Jean

32000

Table Inserted : nucli Nomcli precli CA 3

Durand Zoe 32000 6. ANNULATION DES EFFETS D’UN TRIGGER

Pour empêcher un trigger de produire son effet on peut utiliser le ROLLBACK qui dans ce cas peut porter sur la transaction (ROLLBACK TRANSACTION celle qui a déclenchée le trigger par exemple) ou uniquement le trigger (ROLLBACK TRIGGER) c'est à dire sur les seuls effets de ce dernier. Exemples : Soit la base de données suivante : Magasin (numeroMag, LocationMag, gerantMag) Client (numeroClient, nomClient, prenomClient, paysClient, villeClient, caClient, typeClient) Article (numeroArticle, nomArticle, stockArticle, prixAchat, prixVente , #artFournissuer) Fournisseurs (numeroFournisseur, nomFournisseur) Commande (numeroCmd, dateCmd, #cmdClient,# cmdMag) LigneCommande (lignCmd, lignArticle, lignquantite) Livraison (numeroLivraison, dateLivraison,# livClient, #livMag) ligneLivraison(numeroLivraison, #LIVClient,quantite,# livCmd)

1.

imaginons que nous vérifions que la quantité livrée dans la table LigneLivraison est cohérente à la quantité commandée dans la table ligneCommande. En d’autres termes, il faut que la quantité livrée soit égale à la quantité commandée pour la même commande,si le client ajoute une ligne dans sa commande,la quantité livrée doit être automatiquement mise à jour : CREATE trigger t1 ON lignCommande FOR UPDATE AS update ligneLiv set quantite = (select qtite

Page : 13

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

from lignCommande Where livcmd= numlign and livart = lgarticle) 2.

3.

4.

Ecrire un trigger qui générera le message ci-dessous quand un client sera inséré dans la table Client : Un nouveau client Alaoui Ahmed a ete ajouté à la base de donnée create trigger t6 on client for insert as declare @x int, @y varchar(30) select @x=numclient, @y=nom from inserted print 'nouveau client numero ' +convert(varchar,@x)+ ' nom : ' + @y Créer une table clientSauv dans laquelle on insère automatiquement le nom, le prénom , la ville et le chiffre d’affaire de tout client supprimé. create trigger t2 ON client for delete as declare @n varchar(30), @v varchar(30) select @n = nom, @v = ville from deleted insert into clientSauv values (@n,@v) ECRIRE UN TRIGGER QUI PERMET DE SUPPRIMER AUTOMATIQUEMENT TOUS LES ARTICLES FOURNIS PAR UN FOURNISSEUR AVANT DE LA SUPPRIMER alter trigger t3 on fournisseur instead of delete as declare @x int set @x = (select numfourn from deleted) delete from article where numfr = @x delete from fournisseur where numfourn = @x

5.

Ecrire un trigger qui permet de supprimer toutes les lignes de commandes concernant une commande avant de supprimer celle-ci

6.

Ecrire un trigger qui permet de supprimer toutes les lignes de commandes ainsi que toutes les commandes concernant un client avant de supprimer celui-ci

7.

Ecrire un trigger qui affiche l'ancien nom ainsi que le nouveau lorsqu'un update de la colonne <nom> est effectué sur un tuple de Client : create trigger t7 on client for update as declare @x varchar(30), @y varchar(30) select @x=nom from inserted select @y= nom from deleted print 'ancien nom ' + @y + ' nouveau nom : ' + @x.

D. LES EXCEPTION 1. INTRODUCTION Différents niveaux d'erreurs existent et différents moyens de les gérer. Soit par exemple une procédure qui aurait pour effet de supprimer une ligne de la table commande en s’assurant de supprimer toutes les lignes de toutes les lignes de commande concernés. L'entête d'une telle procédure pourrait s'écrire : CREATE PROCEDURE supprimeCommande @cmd INT Si la ligne considérée n'est pas retrouvée dans la table commande, comme si la valeur du paramètre est NULL, cette procédure échouera sans indiquer d'anomalie. Il faut donc procéder à des tests préalables (nullité, existence…). SQL Server fournit la variable globale @@error que l'on peut tester à tout instant. Cette variable est mise à jour à chaque instruction.

Page : 14

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

Le code continue de s'exécuter même après une erreur. Pour gérer les erreurs, SQL Server dispose de la levée des erreurs à l'aide de l'instruction RAISERROR et l'utilisation du GOTO combiné à une étiquette de branchement pour la reprise sur erreur, permet de centraliser la gestion des erreurs à un seul et même endroit du programme. 1. LES MESSAGES D’ERREURS PREDEFINIS : On peut visualiser tous les messages d’erreurs définis par le système dans le catalogue sysmessages SELECT error, severity, description FROM sysmessages ORDER BY severity DESC

2. L E S M E S S A G E S D ’ E R R E U R S P E R S O N N A L I S É S : a. Créer un message L’utilisateur peut créer ces propres message d’erreurs via la procédure stockée système définit dans le catalogue de la base de données MASTER: sp_addmessage La procédure doit prendre en paramètres : • Un numéro de message unique (ID) qui peut être comprit entre 50,001 et 214783647. • Le second paramètre indique le niveau de sévérité • Le troisième paramètre indique le message de l’erreur Exemple: On peut créer un message qui sera affiché une fois qu’une opération d’insertion dans la table client ne soit possible: USE master EXEC sp_addmessage 100001, 14, N'l’’opération d’’insertion dans la table client a échouée!' Pour demander l’affichage du message d’erreur : raiserror (100001,14) Maintenant, on va créer un nouveau message qui sera affiché une fois que l’operation d’insertion echoue dans n’importe quelle table : USE master EXEC sp_addmessage 100002, 14, N'l’’opération d’’insertion dans la table courante a échouée!' raiserror (100002,14,1) Maintenant, on va créer un troisième message qui sera affiché une fois que l’opération d’insertion échoue dans n’importe quelle table en affichant le nom de la table en question : USE master GO EXEC sp_addmessage 100003, 14, N'l’’opération d’’insertion dans la table %s a échouée!' raiserror (100003, 14, 1, Article)

b. supprimer un message Pour supprimer un message utilisateur, il suffit d’executer la procedure systeme sp_dropmessage avec comme parametre le numero du message à supprimer : exec sp_dropmessage 100001 c. afficher un message sans le créer La commande Raiserror permet d’invoquer un message déjà existant dont le numéro est passé en paramètres, mais elle peut également afficher un message dont le texte est passé en paramètres :

Page : 15

ANNEE DE FORMATION 2008/2009

W.JAKJOUD

OFFICE DE LA FORMATION PROFESSIONNELLE & DE LA PROMOTION DU TRAVAIL INSTITUT SUPERIEUR DES TECHNOLOGIES DE L’INFORMATION ET DE LA COMMUNICATION DE SAFI

Soit la procédure suivante : CREATE PROCEDURE insererClient @Nom varchar(50), @id int AS IF @id <>0 BEGIN INSERT into Client (numClient, nom) VALUES (@id, @Nom) END ELSE BEGIN RAISERROR('%s est non autorisé comme identifiant d''un client’,16,1,@Nom,23) END Tester : exec insererClient 4, ‘KAMALI’ Et : exec insererClient 0, ‘ESSLAOUI’ On créer un message contenant le texte passé à la commande Raiserror : USE MASTER EXEC sp_addmessage 100005, 14, N’%s est non autorisé comme identifiant d’un client’ On modéfie la procedure: CREATE PROCEDURE insererClient @Nom varchar(50), @id int AS IF @id <>0 BEGIN INSERT into Client (numClient, nom) VALUES (@id, @Nom) END ELSE BEGIN RAISERROR(100005,16,1,@Nom,23) END Re-tester : exec insererClient 4, ‘KAMALI’ Et : exec insererClient 0, ‘ESSLAOUI’

Page : 16

ANNEE DE FORMATION 2008/2009

W.JAKJOUD