"procedures stockees et declencheures"

Partie 1 - Procédures stockées
Lorsque vous créez une application avec SQL Server , le langage de programmation Transact-SQL est la principale interface de programmation entre vos applications et la base de données SQL Server. Lorsque vous utilisez des programmes Transact-SQL, vous disposez de deux méthodes pour enregistrer et exécuter les programmes. Vous pouvez enregistrer les programmes localement et créer des applications qui envoient les commandes à SQL Server et traitent les résultats, ou vous pouvez enregistrer les programmes comme des procédures stockées dans SQL Server et créer des applications qui exécutent ces procédures stockées et traitent les résultats.
Les procédures stockées de SQL Server ressemblent aux procédures d'autres langages de programmation car elles peuvent :
· accepter des paramètres d'entrée et renvoyer plusieurs valeurs sous forme de paramètres de sortie à la procédure ou au lot d'instructions appelant ;
· contenir des instructions de programmation qui exécutent des opérations dans la base de données, y compris l'appel d'autres procédures ;
· renvoyer une valeur d'état à une procédure ou à un lot d'instructions appelants pour indiquer une réussite ou un échec (et la raison de l'échec).
Vous pouvez utiliser l'instruction EXECUTE de Transact-SQL pour exécuter une procédure stockée. Les procédures stockées diffèrent des fonctions en ce sens qu'elles ne renvoient pas de valeurs à la place de leurs noms et qu'elles ne peuvent pas être employées directement dans une expression.
Les avantages d'utiliser des procédures stockées dans SQL Server au lieu de programmes Transact-SQL enregistrés localement sur des ordinateurs clients sont les suivants :
· Possibilité d'utiliser la programmation modulaire.
Vous pouvez créer la procédure une fois, la stocker dans la base de données et l'appeler autant de fois que vous le voulez dans votre programme. Les procédures stockées peuvent être créées par une personne spécialisée dans la programmation des bases de données ; elles peuvent ensuite être modifiées indépendamment du code source du programme.
· Leur exécution est plus rapide.
Si une opération nécessite un volume important de code Transact-SQL ou si elle est exécutée à de nombreuses reprises, les procédures stockées peuvent s'avérer plus rapides que les lots de code Transact-SQL. Elles sont analysées et optimisées au moment de leur création et, après leur première exécution, il est possible d'utiliser une version en mémoire des procédures. Les instructions Transact-SQL envoyées à plusieurs reprises depuis le client lors de chaque exécution, sont compilées et optimisées chaque fois qu'elles sont exécutées par SQL Server.
· Réduction du trafic du réseau.
Une opération qui demande des centaines de lignes de code Transact-SQL peut être effectuée au moyen d'une seule instruction qui exécute le code dans une procédure, au lieu d'envoyer toutes ces lignes sur le réseau.
· Utilisation comme mécanisme de sécurité.
Les utilisateurs peuvent obtenir l'autorisation d'exécuter une procédure stockée même s'il ne leur est pas permis d'exécuter directement les instructions de la procédure.
L'instruction Transact-SQL CREATE PROCEDURE permet de créer les procédures stockées de SQL Server et l'instruction ALTER PROCEDURE, de les modifier. La définition d'une procédure stockée contient deux composants principaux : la spécification du nom de la procédure et de ses paramètres, et le corps de la procédure, qui contient les instructions Transact-SQL permettant d'exécuter les opérations de la procédure.
Création d'une procédure stockée
Vous pouvez créer une procédure stockée en utilisant l'instruction Transact-SQL CREATE PROCEDURE.
· L'instruction CREATE PROCEDURE ne peut pas s'utiliser conjointement avec d'autres instructions SQL dans un même lot d'instructions.
· L'autorisation de créer des procédures stockées revient par défaut au propriétaire de la base de données, qui peut la transmettre à d'autres utilisateurs.
· Les procédures stockées sont des objets de base de données et leur nom doit respecter les règles gouvernant les identificateurs.
· Vous ne pouvez créer une procédure stockée que dans la base de données en cours.
Pour créer une procédure stockée, vous devez préciser :
· les paramètres d'entrée et de sortie de la procédure ou du lot appelant ;
· les instructions de programmation qui exécutent les opérations dans la base de données, y compris l'appel à d'autres procédures ;
· la valeur d'état renvoyée à la procédure ou au lot appelant pour indiquer la réussite ou l'échec et, dans ce cas, la raison de l'échec.

Procédures stockées système
Nombre d'activités administratives dans SQL Server s'exécutent à l'aide d'un type spécial de procédure connu sous le nom de procédure stockée système. Les procédures stockées système sont créées et enregistrées dans la base de données master et ont le préfixe sp_. Les procédures stockées du système peuvent s'exécuter depuis n'importe quelle base de données, sans avoir à qualifier complètement le nom de la procédure stockée, en utilisant le nom de base de données master.
Il est fortement recommandé de ne pas créer de procédures stockées avec le préfixe sp_. SQL Server recherche toujours une procédure stockée en commençant par sp_ dans l'ordre suivant :
1. elle existe dans la base de données master ;
2. ensuite, en fonction des éventuels identificateurs fournis (nom de base de données ou propriétaire) ;
3. enfin, avec dbo comme propriétaire si aucun propriétaire n'est spécifié.
Par conséquent, bien qu'il puisse exister dans la base de données en cours une procédure stockée créée par l'utilisateur ayant le préfixe sp_, la base de données master est toujours analysée la première, même si la procédure stockée est qualifiée avec le nom de la base de données.

Important Si une procédure stockée créée par un utilisateur porte le même nom qu'une procédure stockée système, celle de l'utilisateur ne s'exécutera jamais.
Regroupement
Une procédure peut être créée avec le même nom qu'une procédure stockée existante, mais avec un numéro d'identification différent, ce qui permet de grouper les procédures logiquement. En regroupant les procédures de même nom, vous pouvez les supprimer toutes en une seule fois. Les procédures utilisées dans la même application sont souvent regroupées de cette manière. Par exemple, les procédures utilisées avec l'application my_app pourraient s'appeler my_proc;1, my_proc;2, etc. En supprimant my_proc, vous supprimez tout le groupe. Une fois les procédures groupées, les procédures individuelles à l'intérieur du groupe ne peuvent pas être supprimées.
Procédures stockées temporaires
Les procédures stockées temporaires privées et globales, comme les tables temporaires, peuvent être créées en ajoutant les préfixes # et ## à leur nom. # désigne une procédure stockée temporaire locale, et ##, une procédure stockée temporaire globale. Ces procédures n'existent plus après l'arrêt de SQL Server.
Les procédures stockées temporaires sont utiles lorsque vous vous connectez à des versions antérieures de SQL Server qui ne prennent pas en charge la réutilisation des plans d'exécution des instructions ou lots d'instructions Transact-SQL. Les applications se connectant à SQL Server version 2000 doivent utiliser la procédure stockée système sp_executesql au lieu de procédures stockées temporaires. Pour plus d'informations, voir Mise en mémoire cache et réutilisation du plan d'exécution.
Seule la connexion qui a créé une procédure temporaire locale peut exécuter la procédure, laquelle est automatiquement supprimée au moment de la déconnexion (lorsque l'utilisateur se déconnecte de SQL Server ).
Toute connexion peut exécuter une procédure stockée temporaire globale. Une procédure stockée temporaire globale existe jusqu'à ce que l'utilisateur qui l'a créée se déconnecte ; les versions de cette procédure en cours d'exécution par d'autres utilisateurs s'achèvent. Lorsque la connexion utilisée pour créer la procédure est fermée, aucune nouvelle exécution de cette procédure temporaire globale n'est possible. Seules les connexions ayant déjà lancé l'exécution de la procédure stockée peuvent la terminer.
Si une procédure stockée est créée directement dans la base de données tempdb sans le préfixe # ou ##, elle sera automatiquement supprimée à l'arrêt de SQL Server , parce que la base de données tempdb est recréée à chaque démarrage de SQL Server. Les procédures créées directement dans la base de données tempdb continuent d'exister même au terme de la connexion qui l'a créée. Comme pour n'importe quel autre objet, l'autorisation d'exécuter une procédure stockée temporaire peut être accordée à d'autres utilisateurs, leur être refusée ou révoquée.

Création d'une procédure stockée (Enterprise Manager)
Pour créer une procédure stockée
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, puis développez la base de données dans laquelle créer la procédure.
3. Cliquez avec le bouton droit sur Procédures stockées, puis cliquez sur Nouvelle procédure stockée.
4. Entrez le texte de la procédure stockée. Pour indenter le texte de la procédure stockée, appuyez sur TAB. Pour quitter la zone de texte, appuyez sur CTRL+TAB ou cliquez sur le bouton approprié.
5. Pour vérifier la syntaxe, cliquez sur Vérifier la syntaxe.
6. Pour définir des autorisations, cliquez sur Autorisations.
Création d'une procédure stockée à l'aide de l'Assistant Création de procédures stockées (Enterprise Manager)
Pour créer une procédure stockée à l'aide de l'Assistant Création de procédures stockées
1. Développez un groupe de serveurs, puis développez le serveur sur lequel créer la vue.
2. Dans le menu Outils, cliquez sur Assistants.
3. Développez le dossier Bases de données.
4. Cliquez deux fois sur l'Assistant Création de procédures stockées.
5. Exécutez les étapes de l'Assistant.
Définition des paramètres
Une procédure stockée communique avec le programme appelant par le biais de ses paramètres. Lorsqu'un programme exécute une procédure stockée, il peut lui transmettre des valeurs à travers ses paramètres. Ces valeurs peuvent être utilisées comme des variables standard dans le langage de programmation Transact-SQL. Une procédure stockée peut aussi renvoyer des valeurs au programme appelant, par l'intermédiaire de paramètres OUTPUT. Elle peut compter jusqu'à 2100 paramètres, chacun doté d'un nom, d'un type de données, d'une direction et d'une valeur par défaut.
Attribution d'un nom
Chaque paramètre d'une procédure stockée doit être défini avec un nom unique. Les noms des procédures stockées doivent être précédés du caractère @, comme les variables standard de Transact-SQL, et doivent respecter les règles se rapportant aux identificateurs d'objets. Le nom du paramètre peut être utilisé dans la procédure stockée pour obtenir et modifier la valeur du paramètre.
Des valeurs peuvent être passées aux procédures stockées soit de façon explicite en nommant les paramètres et en leur donnant la valeur appropriée, soit en fournissant des valeurs aux paramètres dans l'instruction CREATE PROCEDURE sans les nommer. Par exemple, si la procédure stockée my_proc attend trois paramètres nommés @first, @second et @third, les valeurs qui lui sont transmises peuvent être assignées aux noms de paramètres de la façon suivante :
EXECUTE my_proc @second = 2, @first = 1, @third = 3
ou selon leur position sans les nommer :
EXECUTE my_proc 1, 2, 3
Vous pouvez spécifier les paramètres dans n'importe quel ordre si, pour exécuter une procédure stockée, vous les désignez par leur nom. Par contre, si vous ne les nommez pas, vous devez spécifier les paramètres dans le même ordre (de gauche à droite) qu'ils sont définis dans la procédure stockée. Vous devez, en outre, spécifier tous les paramètres, même s'ils sont facultatifs ou s'ils ont des valeurs par défaut. Par exemple, si les paramètres de my_proc sont tous facultatifs, vous pouvez exécuter my_proc en indiquant uniquement les valeurs des deux premiers paramètres, mais pas en vous contentant de spécifier seulement celles des deuxième et troisième paramètres. Cette règle est nécessaire pour que SQL Server soit en mesure d'identifier les paramètres spécifiés.
Spécification d'un type de données
Les paramètres d'une procédure stockée sont définis avec un type de données, comme dans le cas des colonnes d'une table. Ils peuvent être définis avec n'importe quel type de données Microsoft® SQL Server ™ 2000, y compris text et image. Et ils peuvent également utiliser des types de données définis par l'utilisateur.

Remarque Le type de données cursor est exclusivement réservé aux paramètres OUTPUT des procédures stockées. (Pour plus d'informations sur l'utilisation des variables de curseur, voir Portée des noms de curseur Transact-SQL).
Le type de données d'un paramètre détermine le type et la plage de valeurs admis pour le paramètre. Par exemple, si vous définissez un paramètre avec le type de données tinyint, seules les valeurs numériques comprises entre 0 et 255 seront acceptées. Une erreur est renvoyée lorsqu'une procédure stockée est exécutée avec une valeur incompatible avec le type de données.

Spécification de la direction d'un paramètre
Tous les paramètres d'une procédure stockée peuvent recevoir des valeurs d'entrée lorsque la procédure est exécutée par le programme qui l'appelle.
Exemple
La procédure stockée ci-dessous, get_sales_for_title, utilise un paramètre d'entrée. Le paramètre @title reçoit, comme valeur d'entrée, le titre d'un livre défini par le programme appelant. L'instruction SELECT utilise @title pour obtenir la valeur correcte ytd_sales et l'afficher.
CREATE PROCEDURE get_sales_for_title
@title varchar(80) -- This is the input parameter.
AS

-- Get the sales for the specified title.
SELECT "YTD_SALES" = ytd_sales
FROM titles
WHERE title = @title

RETURN
GO
Si vous spécifiez le mot clé OUTPUT pour un paramètre dans la définition de la procédure stockée, cette dernière peut renvoyer la valeur actuelle du paramètre au programme appelant lors de sa sortie. Le programme appelant doit également utiliser le mot clé OUTPUT lorsqu'il exécute la procédure pour enregistrer la valeur du paramètre dans une variable, qu'il pourra ensuite utiliser. Pour plus d'informations, voir Renvoi de données au moyen de paramètres OUTPUT.
Attribution d'une valeur par défaut
Vous pouvez créer une procédure stockée contenant des paramètres facultatifs en attribuant à ces derniers une valeur par défaut. Lorsque la procédure est exécutée, la valeur par défaut est prise en compte si aucune autre valeur n'a été spécifiée.
L'attribution de valeurs par défaut est nécessaire parce qu'une erreur système est renvoyée lorsqu'un paramètre d'une procédure stockée n'a pas de valeur par défaut et que le programme appelant ne fournit pas de valeur pour ce paramètre lors de l'exécution de la procédure.
S'il n'est pas possible d'affecter une valeur par défaut appropriée à un paramètre donné, vous pouvez spécifier à cet effet la valeur NULL, et faire en sorte que la procédure stockée affiche un message personnalisé lorsqu'elle est exécutée sans qu'une valeur ait été spécifiée pour ce paramètre.

Remarque Si la valeur par défaut est une chaîne de caractères contenant des espaces ou des signes de ponctuation, ou si elle débute par un nombre (par exemple, 6xxx), elle doit figurer entre guillemets simples.
Exemple
L'exemple suivant illustre le comportement particulier adopté par la procédure get_sales_for_title lorsqu'elle est exécutée sans qu'une valeur ne soit affectée au paramètre @title.
CREATE PROCEDURE get_sales_for_title
@title varchar(80) = NULL, -- NULL default value
@ytd_sales int OUTPUT
AS

-- Validate the @title parameter.
IF @title IS NULL
BEGIN
PRINT 'ERROR: You must specify a title value.'
RETURN
END

-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title

RETURN
GO
L'exemple suivant illustre la procédure my_proc avec des valeurs par défaut pour chacun des trois paramètres @first, @second et @third, et les valeurs affichées lorsque la procédure est exécutée avec des valeurs différentes :
CREATE PROCEDURE my_proc
@first int = NULL, -- NULL default value
@second int = 2, -- Default value of 2
@third int = 3 -- Default value of 3
AS

-- Display values.
SELECT @first, @second, @third
GO

EXECUTE my_proc -- No parameters supplied
GO
Affiche :
NULL 2 3

EXECUTE my_proc 10, 20, 30 -- All parameters supplied
GO
Affiche :
10 20 30

EXECUTE my_proc @second = 500 -- Only second parameter supplied by name
GO
Affiche :
NULL 500 3

EXECUTE my_proc 40, @third = 50 -- Only first and third parameters
GO -- are supplied.
Affiche :
40 2 50


Programmation de procédures stockées
Lorsque vous créez une procédure stockée, vous pouvez y insérer pratiquement n'importe quel code Transact-SQL pouvant faire l'objet d'un lot d'instructions.
Règles de programmation des procédures stockées
Les règles à suivre pour la programmation de procédures stockées sont les suivantes :
· La définition de CREATE PROCEDURE peut inclure n'importe quel nombre et type d'instructions SQL, excepté les instructions CREATE suivantes qui ne peuvent être employées dans aucune procédure stockée :
CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE
·
· Une procédure stockée peut créer des objets de base de données. Vous pouvez référencer un objet dans la procédure stockée où vous l'avez créé, à condition que sa création précède sa référence.
· Vous pouvez référencer des tables temporaires dans une procédure stockée.
· Si vous créez une table temporaire locale dans une procédure stockée, cette table n'existe que dans le cadre de cette procédure et disparaît au terme de la procédure.
· Si vous exécutez une procédure stockée qui en appelle une autre, la procédure appelée peut accéder à tous les objets créés par la première procédure, y compris aux tables temporaires.

· Si vous exécutez une procédure stockée distante qui apporte des modifications à une instance distante de SQL Server , ces modifications ne pourront pas être annulées (rollback). Les procédures stockées distantes ne font pas partie des transactions.
· Une procédure stockée peut contenir au maximum 2100 paramètres.
· Le nombre maximal de variables locales dans une procédure stockée n'est limité que par la mémoire disponible.
· Selon la mémoire disponible, la taille maximale d'une procédure stockée est de 128 Mo.
Pour plus d'informations sur les règles de création des procédures stockées, voir CREATE PROCEDURE.
Qualification des noms à l'intérieur d'une procédure stockée
Dans une procédure stockée, les noms des objets non qualifiés par l'utilisateur et utilisés dans des instructions (par exemple, SELECT ou INSERT) reviennent par défaut au propriétaire de la procédure stockée. Si un utilisateur qui crée une procédure stockée n'indique pas le nom des tables référencées dans les instructions SELECT, INSERT, UPDATE ou DELETE de la procédure stockée, l'accès à ces tables par le biais de la procédure stockée est limité par défaut au créateur de la procédure.
Les noms d'objets utilisés avec les instructions ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, UPDATE STATISTICS et DBCC doivent être qualifiés avec le nom du propriétaire de l'objet si d'autres utilisateurs doivent employer la procédure stockée. Par exemple, l'utilisatrice Mary, qui possède la table marytab, doit qualifier le nom de sa table quand celle-ci est utilisée avec l'une de ces instructions, si elle souhaite que d'autres utilisateurs puissent exécuter la procédure stockée dans laquelle la table est utilisée.
Cette qualification est nécessaire parce que les noms d'objets sont résolus à chaque exécution de la procédure. Si marytab n'était pas qualifiée, lorsqu'un utilisateur nommé John tenterait d'exécuter la procédure, SQL Server rechercherait en vain une table appelée marytab appartenant à John.
Cryptage des définitions de procédure
Si, lors de la création d'une procédure stockée, vous voulez être sûr qu'aucun autre utilisateur ne puisse visualiser sa définition, utilisez la clause WITH ENCRYPTION. La définition de la procédure est alors stockée dans un format illisible.
Une fois cryptée, la définition de la procédure stockée ne peut être décryptée et visualisée par personne, que ce soit son propriétaire ou l'administrateur système.
Exemple
A. Création d'une procédure utilisant des paramètres
Voici un exemple de création d'une procédure stockée qui est utile dans la base de données pubs. À partir du nom et du prénom d'un auteur, la procédure stockée affiche le titre et l'éditeur de chacun des livres de cet auteur :
CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
Vous obtenez un message indiquant que la commande n'a renvoyé aucune donnée ni aucune ligne, ce qui signifie que la procédure a été créée.
Exécutez maintenant la procédure stockée au_info :
EXECUTE au_info Ringer, Anne
GO
Voici le jeu de résultats obtenu :
au_lname au_fname title pub_name
--------- --------- --------------------- ----------------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Moon Books

(2 row(s) affected)
B. Création d'une procédure stockée dont les paramètres utilisent des valeurs par défaut
La procédure stockée suivante, pub_info2, affiche le nom de tous les auteurs qui ont écrit un livre publié par l'éditeur spécifié au moyen d'un paramètre. Si aucun nom d'éditeur n'est précisé, la procédure renvoie les noms des auteurs publiés par Algodata Infosystems.
CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'
AS
SELECT au_lname, au_fname, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
JOIN titles t ON ta.title_id = t.title_id
JOIN publishers p ON t.pub_id = p.pub_id
WHERE @pubname = p.pub_name
Exécutez pub_info2 sans fournir de paramètre :
EXECUTE pub_info2
GO

Voici le jeu de résultats obtenu :
au_lname au_fname pub_name
---------------- ---------------- --------------------
Green Marjorie Algodata Infosystems
Bennet Abraham Algodata Infosystems
O'Leary Michael Algodata Infosystems
MacFeather Stearns Algodata Infosystems
Straight Dean Algodata Infosystems
Carson Cheryl Algodata Infosystems
Dull Ann Algodata Infosystems
Hunter Sheryl Algodata Infosystems
Locksley Charlene Algodata Infosystems

(9 row(s) affected)
C. Exécution d'une procédure stockée qui remplace la valeur par défaut d'un paramètre par une valeur explicite
Dans la procédure stockée suivante, showind2, titles est définie comme valeur par défaut pour le paramètre @table :
CREATE PROC showind2 @table varchar(30) = 'titles'
AS
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table
Les titres de colonne (par exemple, TABLE_NAME) facilitent l'interprétation des résultats. Voici les résultats de la procédure stockée pour la table authors :
EXECUTE showind2 authors
GO
TABLE_NAME INDEX_NAME INDEX_ID
---------- ---------- ----------
authors UPKCL_auidind 1
authors aunmind 2

(2 row(s) affected)
Si l'utilisateur ne spécifie pas de valeur, SQL Server emploie la table par défaut titles :
EXECUTE showind2
GO
Voici le jeu de résultats obtenu :
TABLE_NAME INDEX_NAME INDEX_ID
---------- ---------- ----------
titles UPKCL_titleidind 1
titles titleind 2

(2 row(s) affected)
D. Création d'une procédure stockée utilisant un paramètre de valeur NULL par défaut
La valeur par défaut d'un paramètre peut être NULL. Dans ce cas, si l'utilisateur ne spécifie pas de paramètre, SQL Server exécute la procédure stockée en fonction de ses autres instructions. Aucun message d'erreur ne s'affiche.
La définition de la procédure peut également indiquer l'action (ou les actions) à accomplir si l'utilisateur ne fournit pas de paramètre. Exemple :
CREATE PROC showind3 @table varchar(30) = NULL
AS IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table
E. Création d'une procédure stockée utilisant un paramètre par défaut contenant des caractères génériques
La valeur par défaut peut contenir des caractères génériques (%, _, [] et [^]) si la procédure stockée utilise le paramètre avec le mot clé LIKE. Par exemple, showind peut être modifiée pour afficher des informations sur les tables système si l'utilisateur ne fournit pas de paramètre :
CREATE PROC showind4 @table varchar(30) = 'sys%'
AS SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name LIKE @table
La variante suivante de la procédure stockée au_info a des valeurs par défaut contenant des caractères génériques pour les deux paramètres :
CREATE PROC au_info2 @lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
Si vous exécutez au_info2 sans spécifier de paramètre, la procédure affiche tous les auteurs dont le nom commence par la lettre D :
EXECUTE au_info2
GO
Voici le jeu de résultats obtenu :
au_lname au_fname title pub_name
-------- -------- --------------------- -------------------
Dull Ann Secrets of Silicon Val Algodata Infosystems
del Castillo Innes Silicon Val Gastrono Binnet & Hardley
DeFrance Michel The Gourmet Microwave Binnet & Hardley

(3 row(s) affected)
L'exemple suivant omet le deuxième paramètre lorsque des valeurs par défaut ont été définies pour deux paramètres. Vous pouvez ainsi afficher les livres et les éditeurs de tous les auteurs dont le nom est Ringer :
EXECUTE au_info2 Ringer
GO
au_lname au_fname title pub_name
--------- --------- ---------------------- ----------------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Moon Books
Ringer Albert Is Anger the Enemy? New Moon Books
Ringer Albert Life Without Fear New Moon Books

(4 row(s) affected)
Imbrication de procédures stockées
On dit que les procédures stockées sont imbriquées lorsqu'une procédure stockée en appelle une autre. Vous pouvez ainsi aller jusqu'à 32 niveaux d'imbrication. L'imbrication augmente d'un niveau lorsque la procédure stockée appelée commence à s'exécuter, et diminue d'un niveau lorsque son exécution est terminée. Au-delà de 32 niveaux d'imbrication, la totalité de la chaîne de procédures échoue. Le niveau d'imbrication actuel des procédures stockées en exécution est enregistré dans la fonction @@NESTLEVEL.
Renvoi de données à partir d'une procédure stockée
Les procédures stockées de SQL Server renvoient les données sous quatre formes :
· des paramètres de sortie, qui renvoient soit des données (entiers ou caractères) ou une variable de curseur, les curseurs étant des ensembles de résultats pouvant être extraits ligne par ligne (pour plus d'informations sur l'utilisation des variables de curseur, voir Portée des noms de curseur Transact-SQL) ;
· des codes de retour, qui sont toujours un entier ;
· un ensemble de résultats pour chaque instruction SELECT contenue dans la procédure stockée ou toute autre procédure stockée appelée par cette dernière (pour plus d'informations sur l'utilisation de l'instruction SELECT, voir Principes de base des requêtes) ;
· un curseur global qui peut être référencé en dehors de la procédure stockée
Renvoi de données au moyen de paramètres OUTPUT
Si vous spécifiez le mot clé OUTPUT pour un paramètre dans la définition de procédure, la procédure stockée peut renvoyer la valeur actuelle du paramètre au programme appelant lors de la sortie de la procédure. Pour enregistrer la valeur du paramètre dans une variable afin que le programme appelant puisse l'utiliser, ce dernier doit inclure le mot clé OUTPUT lorsqu'il exécute la procédure stockée.
Exemple
L'exemple ci-dessous illustre une procédure stockée avec un paramètre d'entrée et un paramètre de sortie. Le premier paramètre de la procédure, @title, recevra la valeur d'entrée spécifiée par le programme appelant tandis que le second paramètre, @ytd_sales, sera utilisé pour renvoyer la valeur au programme appelant. L'instruction SELECT utilise le paramètre @title pour obtenir la valeur ytd_sales correcte et assigne cette valeur au paramètre de sortie @ytd_sales.
CREATE PROCEDURE get_sales_for_title
@title varchar(80), -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS

-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title

RETURN
GO
Le programme ci-dessous exécute la procédure stockée avec une valeur pour le paramètre d'entrée, et enregistre la valeur de sortie de la procédure dans la variable locale @ytd_sales_for_title du programme appelant.
-- Declare the variable to receive the output value of the procedure.
DECLARE @ytd_sales_for_title int

-- Execute the procedure with a title_id value
-- and save the output value in a variable.

EXECUTE get_sales_for_title
"Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT

-- Display the value returned by the procedure.
PRINT 'Sales for "Sushi, Anyone?": ' + convert(varchar(6),@ytd_sales_for_title)
GO

Sales for "Sushi, Anyone?": 4095

Des valeurs d'entrée peuvent également être définies pour les paramètres OUTPUT lorsque la procédure stockée est exécutée. Ceci permet à la procédure de recevoir une valeur du programme appelant, de la modifier ou de l'utiliser pour exécuter des opérations, puis de renvoyer la nouvelle valeur au programme appelant. Dans l'exemple précédent, la variable @ytd_sales_for_title peut recevoir une valeur avant l'exécution de la procédure stockée. La variable @ytd_sales contient la valeur du paramètre dans le corps de la procédure stockée, et la valeur de la variable @ytd_sales est renvoyée au programme appelant lorsque la procédure est terminée. On appelle habituellement ce mécanisme « capacité de passage par référence ».
Si vous spécifiez OUTPUT pour un paramètre pendant l'exécution d'une procédure stockée alors que le paramètre n'est pas défini avec OUTPUT dans la procédure, vous obtiendrez un message d'erreur. Il est néanmoins possible d'exécuter une procédure stockée avec des paramètres OUTPUT et de ne pas spécifier OUTPUT lors de l'exécution de la procédure. Aucune erreur n'est renvoyée, mais vous ne pouvez pas utiliser la valeur de sortie dans le programme appelant.
Renvoi de données au moyen d'un code de retour
Une procédure stockée peut renvoyer une valeur entière appelé code de retour pour indiquer l'état d'exécution d'une procédure. Le code de retour d'une procédure se définit au moyen de l'instruction RETURN. Comme dans le cas des paramètres OUTPUT, vous devez enregistrer le code de retour dans une variable lors de l'exécution de la procédure afin de pouvoir utiliser sa valeur dans le programme appelant. Par exemple, la variable @result, de type de données int, sert à stocker le code de retour de la procédure stockée my_proc :
DECLARE @result int
EXECUTE @result = my_proc
Les codes de retour sont couramment utilisés dans les blocs de contrôle de flux à l'intérieur des procédures stockées afin de renseigner la valeur du code de retour pour chaque situation d'erreur possible. Vous pouvez utiliser la fonction @@ERROR après une instruction Transact-SQL pour détecter si une erreur a eu lieu pendant l'exécution de l'instruction.
Exemple
A. Envoi d'un code de retour variable selon le type d'erreur
L'exemple ci-dessous illustre comment la procédure get_sales_for_title adopte un comportement différent en définissant des valeurs de code de retour spéciales en fonction de l'erreur. Le tableau répertorie les valeurs entières que la procédure stockée affecte à chaque erreur possible.
Valeur Description
0 Exécution réussie
1 Valeur de paramètre obligatoire non spécifiée
2 Valeur de paramètre incorrecte
3 Erreur d'obtention de la valeur des ventes
4 Valeur de ventes NULL dans le titre

CREATE PROCEDURE get_sales_for_title
-- This is the input parameter, with a default.
@title varchar(80) = NULL,
-- This is the output parameter.
@ytd_sales int OUTPUT
AS

-- Validate the @title parameter.
IF @title IS NULL
BEGIN
PRINT "ERROR: You must specify a title value."
RETURN(1)
END
ELSE
BEGIN
-- Make sure the title is valid.
IF (SELECT COUNT(*) FROM titles
WHERE title = @title) = 0
RETURN(2)
END

-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title

-- Check for SQL Server errors.
IF @@ERROR <> 0
BEGIN
RETURN(3)
END
ELSE
BEGIN
-- Check to see if the ytd_sales value is NULL.
IF @ytd_sales IS NULL
RETURN(4)
ELSE
-- SUCCESS!!
RETURN(0)
END

GO
Une telle utilisation des codes de retour permet aux programmes appelants de détecter et de traiter les erreurs qui se produisent pendant l'exécution de la procédure stockée.
B. Traitement des différents codes de retour renvoyés par une procédure stockée
L'exemple suivant crée un programme qui traite les codes de retour renvoyés par la procédure get_sales_for_title.
-- Declare the variables to receive the output value and return code
-- of the procedure.
DECLARE @ytd_sales_for_title int, @ret_code INT

-- Execute the procedure with a title_id value
-- and save the output value and return code in variables.
EXECUTE @ret_code = get_sales_for_title
"Sushi, Anyone?",
@ytd_sales = @ytd_sales_for_title OUTPUT

-- Check the return codes.
IF @ret_code = 0
BEGIN
PRINT "Procedure executed successfully"
-- Display the value returned by the procedure.
PRINT 'Sales for "Sushi, Anyone?": ' + CONVERT(varchar(6),@ytd_sales_for_title)
END
ELSE IF @ret_code = 1
PRINT "ERROR: No title_id was specified."
ELSE IF @ret_code = 2
PRINT "ERROR: An invalid title_id was specified."
ELSE IF @ret_code = 3
PRINT "ERROR: An error occurred getting the ytd_sales."

GO

Exécution d'une procédure stockée
Utilisez l'instruction EXECUTE de Transact-SQL pour exécuter une procédure stockée. L'utilisation du mot clé EXECUTE n'est pas nécessaire à cette exécution si la procédure est la première instruction du lot.
Des valeurs de paramètres peuvent être fournies si une procédure stockée a été écrite pour les accepter.

Remarque Si vous entrez des paramètres sous la forme @parameter = value, leur ordre n'a pas d'importance. Vous pouvez aussi omettre les paramètres pour lesquels une valeur par défaut a été définie. Si vous spécifiez un paramètre donné sous la forme @parameter = value, vous devez tous les spécifier de cette façon. Sinon, ils doivent apparaître dans l'ordre indiqué par l'instruction CREATE PROCEDURE.
Lorsque le serveur exécute une procédure stockée, il refuse tous les paramètres qui n'étaient pas insérés dans la liste des paramètres au moment de la création de la procédure. Tout paramètre qui est passé par référence (en fournissant explicitement son nom) ne sera pas accepté si son nom ne concorde pas.
Bien que vous puissiez omettre des paramètres ayant des valeurs par défaut, seule la liste des paramètres peut être tronquée. Par exemple, si une procédure stockée a cinq paramètres, vous pouvez omettre les deux derniers paramètres, mais pas omettre le quatrième et inclure le cinquième, à moins d'utiliser le format @parameter = value.
La valeur par défaut d'un paramètre, si elle a été définie dans la procédure stockée, est utilisée dans les cas suivants :
· si aucune valeur n'est spécifiée pour le paramètre au moment de l'exécution de la procédure ;
· si le mot clé DEFAULT est spécifié comme valeur du paramètre.
Pour exécuter une procédure stockée qui est groupée avec d'autres procédures du même nom, indiquez le numéro d'identification de la procédure à l'intérieur du groupe. Par exemple, pour exécuter la seconde procédure stockée du groupe my_proc, spécifiez :
EXECUTE my_proc;2

Exécution automatique des procédures stockées
Les procédures stockées marquées pour être exécutées automatiquement le sont à chaque démarrage de Microsoft® SQL Server ™ 2000.
Cette exécution automatique est utile si vous voulez effectuer régulièrement certaines opérations ou dans le cas d'une procédure exécutée en arrière-plan, qui doit toujours être active. Elle revêt également de l'importance dans le cas de procédures stockées exécutant des tâches système ou de maintenance dans tempdb, par exemple la création d'une table temporaire globale. De cette façon, la table temporaire existe toujours dès que tempdb est recréée lors du démarrage de SQL Server.
Une procédure stockée exécutée automatiquement présente les mêmes autorisations que les membres du rôle de serveur sysadmin. Les éventuels messages d'erreur générés par une procédure stockée sont enregistrés dans le journal des erreurs de SQL Server. Une procédure stockée en exécution automatique ne renvoie pas d'ensembles de résultats. En effet, la procédure stockée est exécutée par SQL Server et non par un utilisateur, c'est pourquoi l'ensemble de résultats ne sortirait nulle part.
Bien que l'exécution automatique s'applique individuellement pour chaque procédure stockée, l'option de configuration scan for startup procs de SQL Server peut être définie de façon à éviter l'exécution automatique de toutes les procédures au démarrage de SQL Server.
Pour valider ou invalider l'exécution automatique d'une procédure stockée
Modification d'une procédure stockée et attribution d'un autre nom
Si vous devez modifier les instructions ou les paramètres d'une procédure stockée, vous pouvez soit supprimer et recréer la procédure, soit la modifier en une seule étape. Si vous supprimez et recréez une procédure stockée, toutes les autorisations qui lui sont associées seront perdues. Si vous la modifiez, sa définition (ou celle de ses paramètres) est modifiée, mais ses autorisations sont préservées.
Vous pouvez aussi renommer une procédure stockée. Le nouveau nom doit se conformer aux règles en vigueur pour les identificateurs. Seul le propriétaire d'une procédure stockée peut renommer celle-ci, mais le propriétaire de la base de données peut changer le nom des procédures stockées de n'importe quel utilisateur. Pour pouvoir renommer une procédure stockée, celle-ci doit se trouver dans la base de données en cours.
Une procédure stockée peut aussi être modifiée de façon à crypter sa définition ou à rendre obligatoire sa recompilation à chaque exécution.

Remarque La modification du nom ou de la définition d'une procédure stockée peut, lors de l'exécution, entraîner un échec au niveau de ses objets dépendants si ceux-ci n'ont pas été mis à jour pour tenir compte des modifications de la procédure.
Modification d'une procédure stockée (Enterprise Manager)
Pour modifier une procédure stockée
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la procédure stockée, puis cliquez sur Procédures stockées.
3. Dans le volet de détails, cliquez avec le bouton droit de la souris sur la procédure stockée puis cliquez sur Propriétés.
4. Dans la boîte Texte, modifiez le texte de la procédure stockée comme il convient. Utilisez les touches CTRL+TAB pour créer un alinéa dans le texte de la procédure stockée SQL Enterprise Manager.
5. Pour vérifier la syntaxe, cliquez sur Vérifier la syntaxe.
6. Pour modifier les autorisations, cliquez sur Autorisations.
Attribution d'un nouveau nom à une procédure stockée (Enterprise Manager)
Pour renommer une procédure stockée
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la procédure stockée, puis cliquez sur Procédures stockées.
3. Dans le volet de détails, cliquez avec le bouton droit de la souris sur la procédure stockée puis cliquez sur Renommer.
4. Tapez le nouveau nom de la procédure stockée.
5. Confirmez le nouveau mot de passe.

Remarque Si vous renommez une procédure stockée, le nom figurant dans le texte de la définition de la procédure n'est pas modifié pour autant. Pour changer ce nom, vous devez modifier directement la procédure stockée.

Attention Les procédures stockées étendues ne doivent pas être renommées, à moins que le code utilisé pour la définir ne soit lui aussi modifié. Sinon, la procédure ne s'exécutera pas.

Recompilation d'une procédure stockée
Comme une base de données est modifiée par des opérations telles que l'ajout d'index ou la modification de données dans les colonnes indexées, les plans de requête d'origine servant à accéder à ses tables doivent être à nouveau optimisés par leur recompilation. Cette optimisation s'effectue automatiquement lors de la première exécution d'une procédure stockée au redémarrage de Microsoft® SQL Server ™ 2000. Elle se produit également lorsqu'une table sous-jacente utilisée par la procédure est modifiée. Toutefois, si vous ajoutez un nouvel index dont la procédure pourrait bénéficier, l'optimisation n'est pas automatique (il faut attendre une nouvelle exécution de la procédure après le redémarrage de SQL Server ).
SQL Server offre trois moyens de recompiler une procédure stockée :
· La procédure système sp_recompile force la recompilation d'une procédure stockée lors de son exécution suivante.
· La création d'une procédure stockée incluant l'option WITH RECOMPILE dans sa définition indique que SQL Server ne met pas de plan en cache pour cette procédure et que celle-ci est recompilée à chaque fois qu'elle est exécutée. Utilisez l'option WITH RECOMPILE pour les procédures stockées dotées de paramètres dont les valeurs varient fortement d'une exécution à l'autre, ce qui entraîne à chaque fois la création de nouveaux plans d'exécution. L'utilisation de cette option n'est pas courante car elle ralentit l'exécution de la procédure stockée ; cette dernière doit en effet être recompilée à chaque exécution.
· Vous pouvez forcer la recompilation d'une procédure stockée en spécifiant l'option WITH RECOMPILE lorsque vous exécutez celle-ci. Utilisez cette option uniquement lorsque le paramètre fourni n'est pas courant ou si les données ont changé de façon significative depuis la création de la procédure.

Remarque Si un objet référencé par une procédure stockée est supprimé ou renommé, l'exécution de la procédure produit une erreur. Cependant, si un objet référencé dans une procédure stockée est remplacé par un objet du même nom, la procédure s'exécute sans qu'il soit nécessaire de la recompiler.
Visualisation d'une procédure stockée
Plusieurs procédures stockées système fournissent des informations sur les procédures stockées à partir des tables système. À l'aide de ces procédures stockées, vous pouvez :
· afficher les instructions Transact-SQL utilisées pour créer une procédure stockée, ce qui peut être utile si vous ne disposez pas des fichiers de script Transact-SQL utilisés pour créer la procédure stockée ;
· obtenir des informations sur une procédure stockée, comme son propriétaire, la date de sa création et ses paramètres ;
· afficher la liste des objets utilisés par la procédure stockée spécifiée et des procédures qui l'utilisent, cette liste permettant d'identifier les procédures affectées par la modification ou la suppression d'un objet de la base de données.
Affichage de la définition d'une procédure stockée (Enterprise Manager)
Pour visualiser la définition d'une procédure stockée
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la procédure stockée, puis cliquez sur Procédures stockées.
3. Dans le volet de détails, cliquez avec le bouton droit de la souris sur la procédure stockée puis cliquez sur Propriétés.
Affichage de la définition d'une procédure stockée (Enterprise Manager)
Pour visualiser la définition d'une procédure stockée
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la procédure stockée, puis cliquez sur Procédures stockées.
3. Dans le volet de détails, cliquez avec le bouton droit de la souris sur la procédure stockée puis cliquez sur Propriétés.
Affichage des dépendances d'une procédure stockée (Enterprise Manager)
Pour visualiser les dépendances d'une procédure stockée
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la procédure stockée, puis cliquez sur Procédures stockées.
3. Dans le volet de détails, cliquez avec le bouton droit sur la procédure stockée, pointez sur Toutes les tâches, puis cliquez sur Afficher les dépendances.
Affichage d'informations sur une procédure stockée étendue (Enterprise Manager)
Pour afficher des informations sur une procédure stockée étendue
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données master, puis cliquez sur Procédures stockées étendues.
3. Dans le volet de détails, cliquez avec le bouton droit de la souris sur la procédure stockée puis cliquez sur Propriétés.
4. Facultativement, cliquez sur (...) pour localiser la DLL contenant la procédure stockée étendue.
5. Facultativement, cliquez sur Autorisations pour voir ou définir les autorisations pour la procédure stockée étendue.
Suppression d'une procédure stockée
Vous pouvez supprimer une procédure stockée si elle ne vous est plus utile. Si une procédure stockée qui a été supprimée est appelée par une autre, SQL Server affiche un message d'erreur au moment de l'exécution de la procédure appelante. Cependant, si vous définissez une nouvelle procédure stockée ayant le même nom et les mêmes paramètres pour remplacer celle qui a été supprimée, les autres procédures qui la référencent pourront s'exécuter avec succès. Prenons par exemple une procédure stockée proc1 qui référence la procédure stockée proc2. Si vous supprimez proc2 et créez une procédure différente nommée proc2, proc1 référencera la nouvelle procédure stockée, et il n'est pas nécessaire de la recompiler.
Dès que des procédures stockées ont été groupées, il est impossible de supprimer des procédures individuelles du groupe. La suppression d'une procédure stockée entraîne celle de toutes les procédures stockées du même groupe.
Suppression d'un procédure stockée (Enterprise Manager)
Pour supprimer une procédure stockée
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la procédure stockée, puis cliquez sur Procédures stockées.
3. Dans le volet de détails, cliquez avec le bouton droit de la souris sur la procédure stockée puis cliquez sur Supprimer.
4. Pour connaître la manière dont la suppression de cette procédure stockée affectera la base de données, cliquez sur Afficher les dépendances.
5. Cliquez sur Supprimer tout.
Suppression d'une procédure stockée étendue (Enterprise Manager)
Pour supprimer une procédure stockée étendue
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données master, puis cliquez sur Procédures stockées étendues.
3. Dans le volet de détails, cliquez avec le bouton droit de la souris sur la procédure stockée étendue à supprimer puis cliquez sur Supprimer.
4. Pour connaître la manière dont la suppression de cette procédure stockée étendue affectera la base de données, cliquez sur Afficher les dépendances.
5. Cliquez sur Supprimer tout.

Partie 2 - Respect des règles d'entreprise à l'aide des déclencheurs
SQL Server fournit deux mécanismes principaux pour assurer l'application des règles d'entreprise et de l'intégrité des données : les contraintes et les déclencheurs. Un déclencheur est un type spécial de procédure stockée qui est automatiquement invoqué lorsque les données d'une table spécifique sont modifiées. Les déclencheurs sont invoqués en réponse aux instructions INSERT, UPDATE ou DELETE. Il peut interroger d'autres tables et peut inclure des instructions Transact-SQL complexes. Le déclencheur et l'instruction qui le déclenche, sont traités comme une unique transaction qui peut être annulée (par une opération de rollback) à partir du déclencheur. Si une erreur grave est détectée (par exemple un espace disque insuffisant), toute la transaction est automatiquement annulée.
Les déclencheurs sont utiles dans les cas suivants :
· Les déclencheurs peuvent effectuer des modifications en cascade dans des tables associées de la base de données, mais ces modifications peuvent être exécutées plus efficacement par le biais de contraintes d'intégrité référentielle en cascade.
· Pour assurer l'application de restrictions plus complexes que celles définies à l'aide de contraintes CHECK.
Contrairement aux contraintes CHECK, ils peuvent faire référence à des colonnes d'autres tables. Par exemple, un déclencheur peut utiliser une instruction SELECT à partir d'une autre table afin de comparer avec les données insérées ou mises à jour et d'effectuer des actions supplémentaires, comme modifier les données ou afficher un message d'erreur défini par l'utilisateur.
· Pour détecter la différence entre l'état d'une table avant et après une modification des données, et entreprendre une ou plusieurs actions en fonction de cette différence.
· Plusieurs déclencheurs du même type (INSERT, UPDATE ou DELETE) sur une table, permettent que de nombreuses actions différentes soient réalisées en réponse à la même instruction de modification.
Comparaison des déclencheurs et des contraintes
Les contraintes et les déclencheurs possèdent leurs avantages spécifiques, utiles dans des situations particulières. Le principal avantage des déclencheurs est qu'ils peuvent contenir une logique de traitement complexe utilisant du code Transact-SQL. Si les déclencheurs peuvent ainsi prendre en charge toutes les fonctionnalités des contraintes, ils ne constituent pas pour autant la meilleure méthode à utiliser dans tous les cas.
L'intégrité d'entité doit toujours être appliquée au niveau le plus bas par des index qui font partie de contraintes PRIMARY KEY et UNIQUE, ou qui sont créés indépendamment des contraintes. L'intégrité de domaine doit être appliquée au moyen de contraintes CHECK, et l'intégrité référentielle (RI) (RI, referential integrity), par des contraintes FOREIGN KEY, en admettant que les fonctionnalités qu'ils offrent correspondent aux besoins fonctionnels de l'application.
Les déclencheurs sont particulièrement utiles lorsque les fonctionnalités prises en charge par les contraintes ne peuvent pas répondre aux besoins fonctionnels de l'application. Exemple :
· Les contraintes FOREIGN KEY peuvent valider une valeur de colonne uniquement si celle-ci correspond exactement à une valeur d'une autre colonne, à moins que la clause REFERENCES définisse une action d'intégrité référentielle en cascade.
· Une contrainte CHECK ne peut valider une valeur de colonne que par rapport à une expression logique ou à une autre colonne de la même table. Vous devez utiliser un déclencheur si votre application requiert qu'une valeur de colonne soit validée par rapport à une colonne d'une autre table.
· Les contraintes ne peuvent donner d'informations sur les erreurs qu'à travers les messages d'erreur système standard. Si votre application requiert (ou peut tirer parti) des messages personnalisés et une gestion des erreurs plus complexe, vous devez utiliser un déclencheur.
Les déclencheurs peuvent effectuer des modifications en cascade dans des tables associées de la base de données, mais ces modifications peuvent être exécutées plus efficacement par le biais de contraintes d'intégrité référentielle en cascade.
· Les déclencheurs peuvent servir à interdire ou à annuler (roll back) des modifications qui enfreignent l'intégrité référentielle, annulant ainsi la tentative de modification des données. Un déclencheur de ce type peut entrer en action si vous modifiez une clé étrangère et que la nouvelle valeur ne correspond pas à sa clé primaire. Par exemple, vous pouvez créer, sur titleauthor.title_id, un déclencheur d'insertion qui annule toute insertion si la nouvelle valeur ne correspond pas à une valeur de titles.title_id. Les contraintes de clé étrangère sont généralement utilisées dans ce but.
· Si des contraintes existent sur la table du déclencheur, elles sont vérifiées après l'exécution du déclencheur INSTEAD OF mais avant celle du déclencheur AFTER. Si les contraintes sont violées, les actions du déclencheur INSTEAD OF sont annulées et le déclencheur AFTER n'est pas exécuté.
Conception de déclencheurs
SQL Server offre deux options permettant de concevoir des déclencheurs :
· Les déclencheurs INSTEAD OF sont exécutés à la place de l'action de déclenchement habituelle. Ils peuvent également être définis sur une vue avec une ou plusieurs tables de base, afin d'étendre les types de mises à jour pouvant être prises en charge par celle-ci.
· Les déclencheurs AFTER sont exécutés après l'action associée à une instruction INSERT, UPDATE ou DELETE. La définition du déclencheur AFTER s'effectue de la même façon que celle de FOR, unique option disponible dans les versions antérieures de SQL Server. Les déclencheurs AFTER peuvent être spécifiés uniquement sur des tables.
Le tableau suivant compare la fonctionnalité des déclencheurs AFTER et INSTEAD OF.
Fonction Déclencheur AFTER Déclencheur INSTEAD OF
Applicabilité Tables Tables et vues
Quantité par table ou vue Plusieurs par action de déclenchement (UPDATE, DELETE et INSERT) Un par action de déclenchement (UPDATE, DELETE et INSERT)
Références en cascade Aucune restriction Non autorisé sur des tables cible de contraintes d'intégrité référentielle en cascade
Exécution Après : Traitement des contraintesActions référentielles déclarativescréation de tables inserted et deletedL'action de déclenchement Avant : Traitement des contraintes Au lieu de : L'action de déclenchement Après : création de tables inserted et deleted
Ordre d'exécution La première et la dernière exécution peuvent être spécifiées Non applicable
Références de colonnes text, ntext et image dans des tables inserted et deleted Non autorisé Autorisé

Définition des circonstances d'activation d'un déclencheur
Vous avez le choix entre deux options pour définir le moment auquel un déclencheur est activé :
· Les déclencheurs AFTER sont activés après l'action de déclenchement (INSERT, UPDATE ou DELETE) et après le traitement des éventuelles contraintes. Vous pouvez définir des déclencheurs AFTER à l'aide du mot clé AFTER ou FOR. Étant donné que le mot clé FOR produit le même effet que le mot clé AFTER, les déclencheurs définis à l'aide du mot clé FOR sont également considérés comme des déclencheurs AFTER.
· Les déclencheurs INSTEAD OF sont activés à la place de l'action de déclenchement et avant le traitement des contraintes.
Chaque table ou vue peut disposer d'un seul déclencheur INSTEAD OF par action de déclenchement (UPDATE, DELETE et INSERT). Une table peut en revanche détenir plusieurs déclencheurs AFTER par action de déclenchement.
Exemple
A. Utilisation du déclencheur INSTEAD OF pour remplacer l'action de déclenchement standard
CREATE TRIGGER TableAInsertTrig ON TableA
INSTEAD OF INSERT
AS ...
B. Utilisation du déclencheur AFTER pour augmenter l'action de déclenchement standard
CREATE TRIGGER TableBDeleteTrig ON TableB
AFTER DELETE
AS ...
C. Utilisation du déclencheur FOR pour augmenter l'action de déclenchement standard
-- This statement uses the FOR keyword to generate an AFTER trigger.
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR UPDATE
AS ...

Exécution des déclencheurs
Les déclencheurs AFTER ne sont jamais exécutés en cas de violation de contrainte et ne peuvent donc pas être utilisés pour un traitement susceptible d'éviter les violations de contrainte.
Les déclencheurs INSTEAD OF sont exécutés à la place de l'action de déclenchement. Ils sont activés après la création des tables inserted et deleted reflétant les modifications apportées à la table de base, mais avant l'exécution de toute autre action. Ils sont exécutés avant toute contrainte, ce qui leur permet d'effectuer un prétraitement qui complète les actions des contraintes.
Si un déclencheur INSTEAD OF défini sur une table exécute une instruction portant sur cette table et susceptible de l'activer de nouveau, il n'est pas appelé de façon récurrente. L'instruction est traitée comme si la table ne détenait aucun déclencheur INSTEAD OF et démarre la chaîne des opérations de contrainte et des exécutions du déclencheur AFTER. Par exemple, si un déclencheur est défini sur une table comme déclencheur d'insertion INSTEAD OF (INSERT) et qu'il exécute une instruction INSERT sur cette table, cette instruction INSERT ne l'appelle pas une seconde fois. L'instruction INSERT exécutée par le déclencheur démarre le processus d'exécution des actions de contrainte et d'activation de tout déclencheur d'insertion AFTER (INSERT) défini pour la table.
Si un déclencheur INSTEAD OF défini sur une vue exécute une instruction portant sur cette vue et susceptible de l'activer de nouveau, il n'est pas appelé de façon récurrente. L'instruction est résolue sous forme de modifications apportées aux tables de base sous-jacentes de la vue. Dans ce cas, la définition de la vue doit respecter toutes les restrictions applicables à une vue pouvant être mise à jour. Pour obtenir une définition des vues pouvant être mises à jour, voir Modification de données par l'intermédiaire d'une vue. Par exemple, si un déclencheur est défini sur une vue comme déclencheur de mise à jour (UPDATE) INSTEAD OF et qu'il exécute une instruction UPDATE faisant référence à cette vue, cette instruction UPDATE ne l'appelle pas une seconde fois, mais elle est appliquée à la vue comme si celle-ci ne comportait pas de déclencheur INSTEAD OF. Les colonnes modifiées par l'instruction UPDATE doivent être résolues dans une seule table de base. Chaque modification d'une table de base sous-jacente démarre la chaîne d'application des contraintes et d'activation des déclencheurs AFTER définis sur la table.
En termes de performances, les déclencheurs consomment peu de ressources système. Le plus clair du temps passé à exécuter un déclencheur est consacré à des accès à d'autres tables, qui peuvent se trouver soit en mémoire, soit sur l'unité de base de données. Les tables deleted et inserted sont toujours en mémoire. L'emplacement des autres tables auxquelles fait référence le déclencheur détermine la durée requise pour l'opération.

Remarque L'utilisation de curseurs dans les déclencheurs n'est pas recommandée, car cela peut porter préjudice aux performances. Au lieu des curseurs, utilisez une logique basée sur un ensemble de lignes afin de concevoir un déclencheur portant sur plusieurs lignes.
Conception de déclencheurs INSTEAD OF
Le principal avantage des déclencheurs INSTEAD OF est qu'ils autorisent des vues qui autrement ne pourraient pas être mises à jour à l'être. Une vue comprenant plusieurs tables de base doit posséder un déclencheur INSTEAD OF pour prendre en charge les opérations d'insertion, de mise à jour et de suppression référençant des données dans les tables. Autre avantage : ils permettent de programmer une logique permettant de rejeter certaines parties d'un lot d'instructions et d'en mener d'autres à terme.
Un déclencheur INSTEAD OF peut effectuer des opérations comme :
· ignorer des parties d'un lot d'instructions ;
· ne pas traiter une partie d'un lot et consigner dans le journal les lignes problématiques ;
· appliquer une action de substitution si une erreur se produit.

Remarque Les déclencheurs INSTEAD OF DELETE et INSTEAD OF UPDATE ne peuvent pas être définis sur une table qui a une clé étrangère définie avec une action DELETE ou UPDATE.
Grâce à la programmation de cette logique dans le cadre d'un déclencheur INSTEAD OF, celle-ci ne doit pas être implémentée à nouveau par toutes les applications qui accèdent aux données.
Dans la séquence suivante d'instructions Transact-SQL, un déclencheur INSTEAD OF met à jour deux tables de base à partir d'une vue. De plus, deux approches de traitement des erreurs sont indiquées :
· Les insertions en double dans la table Person sont ignorées, et les informations issues des insertions sont consignées dans la table PersonDuplicates.
· Les insertions de valeurs en double dans EmployeeTable sont transformées en une instruction UPDATE qui extrait les informations actuelles et les place dans EmployeeTable sans générer de violation d'unicité de clé.
Les instructions Transact-SQL créent deux tables de base, une vue, une table d'enregistrement des erreurs et le déclencheur INSTEAD OF sur la vue. Ces tables séparent les données personnelles et d'entreprise et elles représentent les tables de base pour la vue :
CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)

CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
La vue suivante indique toutes les données pertinentes relatives à une personne issues des deux tables :
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
Vous pouvez enregistrer les tentatives d'insertion de lignes présentant des numéros de sécurité sociale dupliqués. La table PersonDuplicates consigne les valeurs insérées, l'ID de l'utilisateur à l'origine de la tentative d'insertion et l'heure de l'insertion :
CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)
Le déclencheur INSTEAD OF insère des lignes dans des tables de base multiples à partir d'une simple vue. Les tentatives d'insertion de lignes présentant des numéros de sécurité sociale dupliqués sont enregistrées dans la table PersonDuplicates. Les lignes dupliquées dans la table EmployeeTable sont modifiées en instructions de mise à jour.
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate,Comment
FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary,Comment
FROM inserted
ELSE
--If duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary,
Comment = I.Comment
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END
Création d'un déclencheur
Tenez compte des points suivants avant de créer un déclencheur :
· L'instruction CREATE TRIGGER doit être la première instruction du lot. Toutes les autres instructions qui suivent dans ce lot sont interprétées comme faisant partie de la définition de l'instruction CREATE TRIGGER.
· L'autorisation de créer des déclencheurs revient par défaut au propriétaire de la table qui ne peut pas la transmettre à d'autres utilisateurs.
· Les déclencheurs sont des objets de base de données et leur nom doit respecter les règles gouvernant les identificateurs.
· Vous pouvez créer un déclencheur uniquement dans la base de données en cours, mais un déclencheur peut référencer des objets situés hors de celle-ci.
· Bien que les déclencheurs puissent faire référence à des tables temporaires, ils ne peuvent pas être créés sur une table système ou temporaire. Les tables système ne doivent pas être référencées ; utilisez plutôt les vues de schéma d'information.
· Les déclencheurs INSTEAD OF DELETE et INSTEAD OF UPDATE ne peuvent pas être définis sur une table qui a une clé étrangère définie avec une action DELETE ou UPDATE.
· Bien qu'une instruction TRUNCATE TABLE ait les mêmes effets qu'une instruction DELETE dépourvue de clause WHERE (elle supprime toutes les lignes), elle ne peut pas activer les déclencheurs DELETE du fait que l'instruction TRUNCATE TABLE n'est pas inscrite au journal.
· L'instruction WRITETEXT ne provoque pas l'activation des déclencheurs INSERT ou UPDATE.
Pour créer un déclencheur, vous devez spécifier :
· son nom ;
· la table sur laquelle il est défini ;
· le moment auquel il est activé ;
· les instructions de modification des données qui l'activent, les options valides étant INSERT, UPDATE ou DELETE (un même déclencheur peut être activé par plusieurs instructions de modification des données, Ainsi, un déclencheur peut être activé simultanément par les instructions INSERT et UPDATE.
· les instructions de programmation qui réalisent des actions de déclencheur.
Déclencheurs multiples
Une table peut comporter plusieurs déclencheurs d'un type donné, à condition que ceux-ci portent des noms différents, et chaque déclencheur peut exécuter de nombreuses fonctions. Toutefois, un déclencheur donné ne peut porter que sur une seule table mais il peut s'appliquer à n'importe quel sous-ensemble de trois actions utilisateur (UPDATE, INSERT et DELETE).
Une table ne peut posséder qu'un seul déclencheur INSTEAD OF d'un type donné.
Permissions et propriété des déclencheurs
Les autorisations CREATE TRIGGER reviennent par défaut au propriétaire de la table sur laquelle est défini le déclencheur, au rôle de serveur fixe sysadmin et aux membres des rôles de base de données fixes db_owner et db_ddladmin. Ces autorisations ne sont pas transmissibles.
Si un déclencheur INSTEAD OF est créé sur une vue, la chaîne d'appartenance est rompue si le propriétaire de la vue ne possède pas également les tables de base référencées par la vue et le déclencheur. Si une table de base n'est pas détenue par le propriétaire de la vue, le propriétaire de la table doit accorder séparément les autorisations nécessaires à tout utilisateur effectuant des opérations de lecture ou de mise à jour sur la vue. Si un même utilisateur est le propriétaire de la vue mais aussi des tables de base sous-jacentes, les autorisations qu'il accorde aux autres utilisateurs ne peuvent porter que sur la vue, et non sur telle ou telle table de base.
Création d'un déclencheur (Enterprise Manager)
Pour créer un déclencheur
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la table qui doit contenir le déclencheur, puis cliquez sur Tables.
3. Dans le volet de détails, cliquez avec le bouton droit sur la table dans laquelle vous allez créer le déclencheur, pointez sur Toutes les tâches, puis cliquez sur Gérer les déclencheurs.
4. Dans la zone Nom, cliquez sur .
5. Dans la boîte Texte, entrez le texte du déclencheur. Utilisez les touches CTRL-TAB pour créer un alinéa dans le texte du déclencheur.
6. Pour vérifier la syntaxe, cliquez sur Vérifier la syntaxe.
Programmation des déclencheurs
Lors de la création d'un déclencheur, vous pouvez insérer pratiquement tout le code Transact-SQL susceptible d'être utilisé dans un lot d'instructions, à l'exception de ceux indiqués ci-dessous :
ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG


Important Les instructions DISK RESIZE, DISK INIT, LOAD DATABASE et LOAD LOG ont été préservées dans Microsoft® SQL Server 2000™ uniquement à des fins de compatibilité ascendante et ne figureront peut-être plus dans les versions futures.

Cryptage des définitions de déclencheurs
Si vous voulez vous assurer que les autres utilisateurs ne peuvent pas voir la définition d'un déclencheur, vous pouvez utiliser la clause WITH ENCRYPTION. La définition du déclencheur est alors enregistrée dans un format illisible.
Une fois cryptée, la définition du déclencheur ne peut être décryptée ni affichée par personne, pas même son propriétaire ni l'administrateur système.
Options de l'instruction SET
Lorsqu'une application ODBC se connecte à un serveur SQL Server , celui-ci valide automatiquement les options suivantes pour la session :
· SET QUOTED_IDENTIFIER ON ;
· SET TEXTSIZE 2147483647 ;
· SET ANSI_DEFAULTS ON ;
· SET CURSOR_CLOSE_ON_COMMIT OFF ;
· SET IMPLICIT_TRANSACTIONS OFF.
Ces paramètres améliorent la portabilité des applications ODBC. Étant donné que les applications DB-Library– ne définissent généralement pas ces options, les déclencheurs doivent être testés avec les options SET indiquées ci-dessus, à la fois activées (ON) et désactivées (OFF). Cette opération garantit que les déclencheurs fonctionnent correctement, quelles que soient les options activées par une connexion déterminée lors de l'appel du déclencheur. Un déclencheur qui requiert un paramétrage particulier pour une de ces options devra commencer par une instruction SET. Cette instruction SET n'est active que pour l'exécution du déclencheur ; à la fin de celle-ci, la configuration d'origine est restaurée.
Test des modifications de colonnes spécifiques
La clause IF UPDATE (column_name) peut être utilisée dans la définition d'un déclencheur pour déterminer si une instruction INSERT ou UPDATE a modifié une colonne particulière de la table. Cette clause prend la valeur TRUE si une valeur est attribuée à la colonne.

Remarque Étant donné qu'une valeur spécifique d'une colonne ne peut pas être supprimée par l'instruction DELETE, la clause IF UPDATE ne s'applique pas à l'instruction DELETE.
Vous pouvez aussi utiliser la clause IF COLUMNS_UPDATED() pour vérifier les colonnes de la table qui ont été mises à jour par une instruction INSERT ou UPDATE. Cette clause utilise un masque de bits (binaire) entier pour spécifier les colonnes à tester.
Exemple
A. Utilisation de la clause IF UPDATE pour tester les modifications de données
L'exemple ci-dessous crée le déclencheur d'insertion (INSERT) my_trig sur la table my_table et vérifie si la colonne b a été modifiée par des instructions INSERT.
CREATE TABLE my_table*
(a int NULL, b int NULL)
GO

CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
PRINT 'Column b Modified'
GO
B. Utilisation de la clause COLUMNS UPDATED() pour tester les modifications de données
Vous obtenez des résultats similaires avec la clause COLUMNS_UPDATED() :
CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
PRINT 'Column b Modified'
GO
Résolution de noms différée
SQL Server permet à un déclencheur de référencer une table qui n'existait pas au moment où le déclencheur a été créé. Cette fonction s'appelle la résolution de noms différée.
Remarque Si un objet référencé par un déclencheur est supprimé ou renommé, une erreur est renvoyée lors de l'exécution du déclencheur. Cependant, si un objet référencé dans un déclencheur est remplacé par un objet du même nom, le déclencheur s'exécute sans qu'il soit nécessaire de le recréer. Par exemple, si le déclencheur trig1 référence la table test1, alors que cette dernière a été supprimée et qu'une autre table appelée test1 a été créée, trig1 référence la nouvelle table.
Renvoi de résultats
Il est recommandé qu'un déclencheur ne renvoie aucun résultat. En effet, le traitement particulier des résultats qui seraient renvoyés doit être écrit dans chacune des applications permettant l'apport de modifications à la table du déclencheur. Pour éviter qu'un déclencheur renvoie des résultats, n'incluez dans sa définition ni instruction SELECT, ni assignations de variables. Si une assignation de variable doit intervenir dans un déclencheur, utilisez l'instruction SET NOCOUNT au début du déclencheur, pour empêcher tout renvoi d'un ensemble de résultats.

Utilisation de déclencheurs contenant des instructions ROLLBACK TRANSACTION
Lorsque des déclencheurs contenant des instructions ROLLBACK TRANSACTION sont exécutés à partir d'un lot d'instructions, ils annulent le lot tout entier. Dans l'exemple suivant, si l'instruction INSERT active un déclencheur contenant une instruction ROLLBACK TRANSACTION, l'instruction DELETE ne s'exécutera pas car la totalité du lot d'instructions est annulée :
/* Start of Batch */
INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95') -- Causes trigger to fire and ROLLBACK TRANSACTION.
DELETE employee WHERE emp_id = 'PMA42628M'
GO
Si des déclencheurs contenant des instructions ROLLBACK TRANSACTION sont activés à partir d'une transaction définie par l'utilisateur, l'instruction ROLLBACK TRANSACTION annule toute la transaction. Dans l'exemple suivant, si l'instruction INSERT active un déclencheur contenant une instruction ROLLBACK TRANSACTION, l'instruction UPDATE est également annulée :
/* Start of Transaction */
BEGIN TRANSACTION
UPDATE employee SET hire_date = '7/1/94' WHERE emp_id = 'VPA30890F'
INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95') -- Causes trigger to fire and ROLLBACK TRANSACTION
Utilisation des tables inserted et deleted
Deux tables spéciales sont utilisées dans les instructions de déclencheur : la table deleted et la table inserted. SQL Server crée et gère automatiquement ces tables. Ces tables temporaires servent à tester les effets de certaines modifications de données et à définir des conditions pour les actions de déclencheur ; toutefois vous ne pouvez pas modifier directement les données dans les tables.
Les tables inserted et deleted sont principalement utilisées dans les déclencheurs pour :
· étendre l'intégrité référentielle entre les tables ;
· insérer ou mettre à jour des données dans des tables de base sous-jacentes d'une vue ;
· rechercher les erreurs et prendre les actions nécessaires.
· Détecter la différence entre l'état d'une table avant et après une modification des données, et entreprendre une ou plusieurs actions en fonction de cette différence.
La table deleted stocke des copies des lignes affectées par les instructions DELETE et UPDATE. Pendant l'exécution d'une instruction DELETE ou UPDATE, certaines lignes sont supprimées de la table du déclencheur et déplacées vers la table deleted. La table deleted et la table du déclencheur ne possèdent habituellement pas de ligne en commun.
La table inserted stocke des copies des lignes affectées par les instructions INSERT et UPDATE. Durant une transaction INSERT ou UPDATE, de nouvelles lignes sont ajoutées simultanément dans la table inserted et dans la table du déclencheur. Les lignes de la table inserted sont des copies des lignes créées dans la table du déclencheur.
D'un point de vue théorique, une transaction UPDATE est une opération de suppression suivie d'une opération d'insertion ; les anciennes lignes sont d'abord copiées dans la table deleted, et les nouvelles lignes sont ensuite copiées dans la table du déclencheur et dans la table inserted.
Pour définir les conditions du déclencheur, utilisez les tables inserted et deleted de façon appropriée, en fonction de l'action qui a activé le déclencheur. Bien que vous puissiez, sans provoquer d'erreur, référencer la table deleted pendant le test d'une insertion (INSERT) ou la table inserted pendant le test d'une suppression (DELETE), ces tables de test du déclencheur ne contiendront alors aucune ligne.

Remarque Si des actions du déclencheur dépendent du nombre de lignes affectées par une modification de données, utilisez les tests (comme l'examen de @@ROWCOUNT) pour les modifications de données multilignes (une instruction INSERT, DELETE ou UPDATE basée sur une instruction SELECT), puis effectuez les opérations appropriées.
SQL Server 2000 ne permet pas le référencement aux colonnes text, ntext ou image dans les tables inserted et deleted pour les déclencheurs AFTER ; toutefois ces références de colonnes sont autorisées pour les déclencheurs INSTEAD OF. Pour plus d'informations, voir CREATE TRIGGER.
Utilisation des tables inserted et deleted dans les déclencheurs INSTEAD OF
Les tables inserted et deleted transmises aux déclencheurs INSTEAD OF définis sur des tables suivent les mêmes règles que les tables inserted et deleted transmises aux déclencheurs AFTER. Le format des tables inserted et deleted est le même que celui de la table sur laquelle est défini le déclencheur INSTEAD OF. Chaque colonne des tables inserted et deleted est directement mappée vers une colonne de la table de base.
Qu'une table possède ou non un déclencheur INSTEAD OF, les règles qui régissent la fourniture de valeurs pour les colonnes par une instruction INSERT ou UPDATE faisant référence à la table sont les mêmes :
· Aucune valeur ne peut être spécifiée pour une colonne calculée ou de type de données timestamp.
· Aucune valeur ne peut être spécifiée pour une colonne dotée d'une propriété IDENTITY, sauf si le paramètre IDENTITY_INSERT de cette colonne est activé (ON), auquel cas les instructions INSERT doivent fournir une valeur.
· Les instructions INSERT doivent fournir des valeurs pour toutes les colonnes NOT NULL pour lesquelles aucune contrainte DEFAULT n'est définie.
· Les valeurs sont facultatives pour toute colonne acceptant des valeurs NULL ou toute colonne NOT NULL avec valeur par défaut (DEFAULT), sous réserve qu'il ne s'agisse pas d'une colonne calculée, identité ou timestamp.
Lorsqu'une instruction INSERT, UPDATE ou DELETE fait référence à une vue possédant un déclencheur INSTEAD OF, le moteur de base de données appelle le déclencheur au lieu d'effectuer une opération directe sur une table. Le déclencheur doit utiliser les informations présentées dans les tables inserted et deleted pour élaborer toute instruction nécessaire à l'implémentation de l'action requise dans les tables de base, même si le format des informations contenues dans les tables inserted et deleted conçues pour la vue diffère de celui des données stockées dans les tables de base.
Le format des tables inserted et deleted transmises à un déclencheur INSTEAD OF défini sur une vue correspond à la liste de sélection de l'instruction SELECT définie pour la vue. Exemple :
CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT EmployeeID, LastName, FirstName
FROM Northwind.dbo.Employees
L'ensemble de résultats pour cette vue possède trois colonnes : une colonne int et deux colonnes nvarchar. Les tables inserted et deleted transmises à un déclencheur INSTEAD OF défini sur la vue possèdent également une colonne int nommée EmployeeID et deux colonnes nvarchar, l'une nommée LName et l'autre, FName.
La liste de sélection d'une vue peut également contenir des expressions qui n'établissent pas de mappage direct vers une colonne de table de base unique. Certaines expressions de vue, telles que l'invocation d'une constante ou d'une fonction, peuvent ne pas référencer de colonne et être ignorées. Les expressions complexes peuvent référencer plusieurs colonnes, mais les tables inserted et deleted ne détiennent qu'une seule valeur pour chaque ligne insérée. Les mêmes considérations s'appliquent aux expressions simples d'une vue si elles font référence à une colonne calculée à laquelle est associée une expression complexe. Un déclencheur INSTEAD OF défini sur la vue doit gérer ces types d'expressions.

Considérations multilignes
Un point important dont il faut tenir compte, lors de l'écriture du code d'un déclencheur, est que l'instruction qui active le déclencheur peut être une instruction unique affectant plusieurs lignes de données au lieu d'une seule. Il en est généralement ainsi pour les déclencheurs de mise à jour (UPDATE) et de suppression (DELETE) qui concernent souvent plusieurs lignes. Cela est moins fréquent dans le cas des déclencheurs d'insertion (INSERT) car l'instruction INSERT de base n'ajoute qu'une seule ligne. Toutefois, comme un déclencheur d'insertion peut être activé par une instruction SELECT INSERT INTO (table_name), l'insertion de nombreuses lignes peut aboutir à un appel de déclencheur unique.
Les facteurs à prendre en compte au sujet des lignes multiples sont particulièrement importants lorsque la fonction d'un déclencheur consiste à recalculer automatiquement les totaux d'une table et à enregistrer les résultats dans une autre table en vue de subir d'autres calculs.

Remarque L'utilisation de curseurs dans les déclencheurs n'est pas recommandée, car cela peut porter préjudice aux performances. Au lieu des curseurs, utilisez une logique basée sur un ensemble de lignes afin de concevoir un déclencheur portant sur plusieurs lignes.
Exemple
Les déclencheurs des exemples suivants ont pour mission de stocker le total cumulé d'une colonne dans une autre table.
A. Stockage d'un total cumulé pour l'insertion d'une seule ligne
La première version du déclencheur fonctionne correctement pour l'insertion d'une seule ligne, lorsqu'une ligne de données est chargée dans la table sales. Le déclencheur est activé par une instruction INSERT et la nouvelle ligne est chargée dans la table inserted pendant la durée d'exécution du déclencheur. L'instruction UPDATE lit la valeur de la colonne qty correspondant à la ligne et l'ajoute à la valeur existante dans la colonne ytd_sales de la table titles. La clause WHERE garantit que la ligne mise à jour dans la table sales correspond à la valeur title_id de la ligne dans la table inserted.
-- Trigger is valid for single-row inserts.
CREATE TRIGGER intrig
ON sales
AFTER INSERT AS

UPDATE titles
SET ytd_sales = ytd_sales + qty
FROM inserted
WHERE titles.title_id = inserted.title_id
B. Stockage d'un total cumulé pour l'insertion d'une ou de plusieurs lignes
Dans le cas d'une insertion (INSERT) multiligne, ce déclencheur peut ne pas fonctionner correctement ; l'expression à droite d'une expression d'affectation dans une instruction UPDATE (ytd_sales + qty) ne peut être qu'une unique valeur, et non une liste de valeurs. Le déclencheur obtient donc une valeur à partir d'une seule ligne de la table inserted et l'ajoute à la valeur ytd_sales existante de la table titles pour une valeur donnée de title_id. Le résultat risque de ne pas être correct si une valeur unique de title_id se trouve plus d'une fois dans la table inserted.
Pour mettre à jour correctement la table titles, le déclencheur doit tenir compte de l'existence possible de plusieurs lignes dans la table inserted. Vous pouvez pour cela recourir à la fonction SUM, qui calcule la valeur qty totale d'un groupe de lignes de la table inserted pour chaque valeur title_id. La fonction SUM est placée dans une sous-requête en corrélation (l'instruction SELECT entre parenthèses) qui renvoie une valeur unique pour chaque valeur title_id de la table inserted qui correspond ou est en corrélation avec une valeur title_id de la table titles.
-- Trigger is valid for multirow and single-row inserts.
CREATE TRIGGER intrig
ON sales
AFTER INSERT AS

UPDATE titles
SET ytd_sales = ytd_sales +
(SELECT SUM(qty) -- Correlated subquery.
FROM inserted
WHERE titles.title_id = inserted.title_id)
WHERE titles.title_id IN
(SELECT title_id FROM inserted)
Le fonctionnement de ce déclencheur est également correct dans le cas de l'insertion d'une seule ligne ; le total de la colonne qty est alors la somme d'une seule ligne. La sous-requête en corrélation et l'opérateur IN utilisé dans la clause WHERE demandent toutefois un traitement complémentaire de Microsoft® SQL Server ™ 2000, ce qui n'est pas nécessaire pour l'insertion d'une seule ligne.
C. Stockage d'un total cumulé basé sur le type d'insertion
Pour rectifier ce problème, vous pouvez modifier le déclencheur afin d'utiliser la méthode optimale en fonction du nombre de lignes. Par exemple, la fonction @@ROWCOUNT peut être utilisée dans la logique du déclencheur pour différencier une insertion d'une seule ligne d'une insertion de plusieurs lignes.
-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
CREATE TRIGGER intrig
ON sales
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
UPDATE titles
SET ytd_sales = ytd_sales + qty
FROM inserted
WHERE titles.title_id = inserted.title_id
END
ELSE
BEGIN
UPDATE titles
SET ytd_sales = ytd_sales +
(SELECT SUM(qty)
FROM inserted
WHERE titles.title_id = inserted.title_id)
WHERE titles.title_id IN
(SELECT title_id FROM inserted)
END
Déclencheur INSERT conditionnel
Il rejette ou accepte chaque transaction de modification de données en entier. Cependant, il n'est pas nécessaire d'annuler (rollback) toutes les modifications de données simplement parce que quelques-unes ne sont pas valables. En utilisant une sous-requête en corrélation dans un déclencheur, vous pouvez forcer le déclencheur à analyser les lignes modifiées une à une.
Exemple
A. Utilisation d'un déclencheur AFTER INSERT
L'exemple suivant suppose l'existence d'une table nommée newsale dans la base de données pubs. Voici l'instruction CREATE pour newsale :
CREATE TABLE newsale
(stor_id char(4),
ord_num varchar(20),
date datetime,
qty smallint,
payterms varchar(12),
title_id tid)
Si vous voulez examiner chacun des enregistrements que vous voulez insérer, le déclencheur conditionalinsert analyse l'insertion ligne par ligne, puis supprime les lignes qui n'ont pas de valeur title_id correspondante dans la table titles.
CREATE TRIGGER conditionalinsert
ON sales
AFTER INSERT AS
IF
(SELECT COUNT(*) FROM titles, inserted
WHERE titles.title_id = inserted.title_id) <> @@ROWCOUNT
BEGIN
DELETE sales FROM sales, inserted
WHERE sales.title_id = inserted.title_id AND
inserted.title_id NOT IN
(SELECT title_id
FROM titles)
PRINT 'Only sales records with matching title_ids added.'
END
La transaction n'est pas annulée dans sa totalité si des titres non valables ont été insérés ; dans ce cas, le déclencheur supprime les lignes incorrectes. Cette capacité de supprimer des lignes qui ont été insérées repose sur l'ordre dans lequel le traitement s'effectue lors de l'activation des déclencheurs. D'abord, les lignes sont insérées dans la table sales et dans la table inserted, puis le déclencheur s'exécute.
Pour tester le déclencheur, insérez quatre lignes dans la table newsale. Les title_ids de deux lignes de cette table ne correspondent pas aux valeurs spécifiées dans la table titles.
newsale
stor_id ord_num date qty payterms title_id
------- -------- ------------------- --- -------- --------
7066 QA7442.3 Jul 25 1995 8:35AM 75 Net 30 PS1372
7066 QA7442.3 Jul 24 1995 8:35AM 75 Net 60 BU7832
7067 D4482 Jul 27 1995 12:00AM 10 Net 30 PSxxxx
7131 N914008 Jul 27 1995 12:00AM 20 Net 30 PSyyyy

Ensuite, insérez des données de la table newsale dans la table sales. L'instruction est la suivante :
INSERT sales
SELECT * FROM newsale
Les lignes title_id PSxxxx et PSyyyy ne correspondent à aucune ligne de la table titles et le déclencheur conditionalinsert supprime ces deux lignes des tables sales et inserted.

Premier et dernier déclencheurs
Vous pouvez définir l'un des déclencheurs AFTER associés à une table comme étant le premier ou le dernier déclencheur AFTER exécuté pour chaque action de déclenchement INSERT, DELETE et UPDATE. L'ordre d'exécution des déclencheurs AFTER activés entre les premier et dernier déclencheurs est indéfini.
Pour spécifier l'ordre pour un déclencheur AFTER, utilisez la procédure stockée sp_settriggerorder. Les options suivantes sont disponibles :
· Premier
qui définit le déclencheur comme étant le premier déclencheur AFTER activé dans le cadre d'une action de déclenchement ;
· Dernier
qui définit le déclencheur comme étant le dernier déclencheur AFTER activé dans le cadre d'une action de déclenchement ;
· Aucun
qui spécifie qu'aucun rang d'exécution n'est affecté au déclencheur, option servant essentiellement à réinitialiser un déclencheur défini comme étant le premier ou le dernier déclencheur.
Voici un exemple d'utilisation de sp_settriggerorder :
sp_settriggerorder @triggername = 'MyTrigger', @order = 'first', @stmttype = 'UPDATE'

Important Les premier et dernier déclencheurs doivent être différents.
Une table peut, à un moment donné, comporter plusieurs déclencheurs INSERT, UPDATE et DELETE. Chaque type d'instruction peut posséder des premier et dernier déclencheurs qui lui sont propres, et doivent cependant être différents.
Si le premier ou dernier déclencheur défini pour une table ne couvre pas une action de déclenchement, telle que FOR UPDATE, FOR DELETE ou FOR INSERT, aucun premier ou dernier déclencheur n'est associé aux actions manquantes.
Un déclencheur INSTEAD OF ne peut pas être défini en tant que premier ou dernier déclencheur. Il est activé avant que l'apport de mises à jour aux tables sous-jacentes. Toutefois, si des mises à jour sont apportées par un déclencheur INSTEAD OF à des tables sous-jacentes, elles se produisent après les déclencheurs définis sur la table, y compris le premier déclencheur. Par exemple, si un déclencheur INSTEAD OF sur une vue met à jour une table de base contenant trois déclencheurs, ces trois déclencheurs sont activés avant l'insertion des données par le déclencheur INSTEAD OF.
Si une instruction ALTER TRIGGER vise à modifier un déclencheur défini comme le premier ou le dernier, l'attribut first ou last est supprimé et le rang d'exécution prend pour valeur none ; le rang d'exécution doit être réinitialisé avec sp_settriggerorder.
La fonction OBJECTPROPERTY précise également si un déclencheur est défini comme le premier ou le dernier par le biais des propriétés ExecIsFirstTrigger et ExecIsLastTrigger.
La réplication génère un premier déclencheur automatiquement pour toute table qui est un abonné avec mise à jour immédiate ou en attente. Elle nécessite un déclencheur qui soit le premier. Elle génère une erreur si vous essayez d'affecter l'attribut d'abonné avec mise à jour immédiate ou en attente à une table détenant un premier déclencheur. Si vous affectez l'attribut de premier déclencheur à un déclencheur défini par l'utilisateur après avoir fait d'une table un abonné avec mise à jour immédiate ou en attente, sp_settriggerorder renvoie une erreur. Si vous utilisez ALTER sur le déclencheur de réplication ou si, à l'aide de sp_settriggerorder, vous définissez son rang d'exécution sur aucun ou dernier, l'abonnement ne fonctionnera pas correctement.

Déclencheurs imbriqués
Les déclencheurs sont imbriqués lorsqu'un déclencheur exécute une action qui active un autre déclencheur, lequel à son tour peut en activer un autre, et ainsi de suite. Les déclencheurs peuvent être imbriqués sur 32 niveaux au maximum, et vous pouvez vérifier si l'imbrication est autorisée au moyen de l'option de configuration de serveur nested triggers.
Si l'imbrication est permise et qu'un déclencheur de la chaîne démarre une boucle infinie, le niveau d'imbrication maximal est dépassé et le déclencheur s'arrête.
Les déclencheurs imbriqués peuvent s'avérer utiles pour effectuer certaines opérations, comme stocker une copie de sauvegarde de lignes modifiées par un déclencheur précédent. Par exemple, vous pouvez créer un déclencheur sur titleauthor, qui effectue une copie de sauvegarde des lignes de la colonne titleauthor qui ont été supprimées par le déclencheur delcascadetrig . Lorsque le déclencheur déclencheur_suppression_cascade est activé, la suppression de la ligne id_titre PS2091 de la table titres supprime la ou les lignes correspondantes dans titreauteur. Pour enregistrer les données, créez un déclencheur DELETE sur titleauthor qui stocke les données supprimées dans une autre table, del_save, créée séparément. Exemple :
CREATE TRIGGER savedel
ON titleauthor
FOR DELETE
AS
INSERT del_save
SELECT * FROM deleted
Il n'est pas recommandé d'utiliser des déclencheurs imbriqués dans une séquence où l'ordre a de l'importance. Employez des déclencheurs séparés pour effectuer des modifications de données en cascade.

Remarque Comme les déclencheurs s'exécutent au sein d'une transaction, un échec à un quelconque niveau d'un ensemble de déclencheurs imbriqués annule la transaction tout entière, entraînant l'annulation (rollback) de toutes les modifications de données. Pour pouvoir déterminer l'emplacement où l'erreur a eu lieu, employez dans vos déclencheurs des instructions PRINT.
Déclencheurs récursifs
Un déclencheur ne s'appelle pas lui-même, à moins que l'option de base de données RECURSIVE_TRIGGERS ne soit définie. Il existe deux types d'autorisations :
· la récursion directe ;
Elle se produit lorsqu'un déclencheur est activé et exécute une action qui l'active de nouveau. Prenons l'exemple d'une application qui met à jour la table T3, ce qui active le déclencheur Trig3. Trig3 met à jour de nouveau la table T3, ce qui active encore le déclencheur Trig3.
· la récursion indirecte ;
Elle se produit lorsqu'un déclencheur est activé et exécute une action qui active un déclencheur sur une autre table. Le second déclencheur active une mise à jour de la table d'origine, ce qui a pour effet d'activer une nouvelle fois le déclencheur d'origine. Par exemple, une application met à jour la table T1, ce qui active le déclencheur Trig1. Trig1 met à jour la table T2, ce qui active le déclencheur Trig2. Trig2 à son tour met à jour la table T1 ce qui active de nouveau le déclencheur Trig1.
Seule la récurrence directe est neutralisée lorsque l'option de base de données RECURSIVE_TRIGGERS est désactivée (OFF). Pour désactiver la récurrence indirecte, affectez en outre la valeur 0 à l'option de serveur nested triggers.
Exemple
A. Utilisation de déclencheurs récursifs pour résoudre des relations d'auto-référence
Une des utilisations des déclencheurs récursifs concerne les tables ayant une relation d'auto-référence, également appelé fermeture transitive. Par exemple, la table emp_mgr définit :
· un employé (emp) dans une entreprise ;
· le responsable de chaque employé (mgr) ;
· le nombre total d'employés dans l'arborescence de l'organisation, sous les ordres de chaque employé (NoOfReports).
Un déclencheur de mise à jour récursif peut être utilisé pour maintenir à jour la colonne NoOfReports au fur et à mesure de l'insertion de l'enregistrement de nouveaux employés. Le déclencheur d'insertion met à jour la colonne NoOfReports de l'enregistrement du responsable, provoquant la mise à jour récursive de la colonne NoOfReports des autres enregistrements en remontant la hiérarchie.
USE pubs
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE pubs
SET RECURSIVE_TRIGGERS ON
GO
CREATE TABLE emp_mgr (
emp char(30) PRIMARY KEY,
mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
NoOfReports int DEFAULT 0
)
GO
CREATE TRIGGER emp_mgrins ON emp_mgr
FOR INSERT
AS
DECLARE @e char(30), @m char(30)
DECLARE c1 CURSOR FOR
SELECT emp_mgr.emp
FROM emp_mgr, inserted
WHERE emp_mgr.emp = inserted.mgr

OPEN c1
FETCH NEXT FROM c1 INTO @e
WHILE @@fetch_status = 0
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly
WHERE emp_mgr.emp = @e -- added employee.

FETCH NEXT FROM c1 INTO @e
END
CLOSE c1
DEALLOCATE c1
GO
-- This recursive UPDATE trigger works assuming:
-- 1. Only singleton updates on emp_mgr.
-- 2. No inserts in the middle of the org tree.
CREATE TRIGGER emp_mgrupd ON emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's
FROM inserted -- (no. of reports) by
WHERE emp_mgr.emp = inserted.mgr -- 1 for the new report.

UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
FROM deleted -- (no. of reports) by 1
WHERE emp_mgr.emp = deleted.mgr -- for the new report.
END
GO
-- Insert some test data rows.
INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL)
INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry')
INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe')
GO
SELECT * FROM emp_mgr
GO
-- Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave'
GO
SELECT * FROM emp_mgr
GO
Résultats avant la mise à jour :
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Joe 0
Harry NULL 1
Joe Alice 1
Paul Alice 0
Résultats après la mise à jour :
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Harry 0
Harry NULL 2
Joe Alice 0
Paul Alice 0
Définition de l'option d'activation des déclencheurs imbriqués (Enterprise Manager)
Pour définir l'option d'activation des déclencheurs imbriqués
1. Développez un groupe de serveurs.
2. Cliquez avec le bouton droit sur un serveur, puis cliquez sur Propriétés.
3. Cliquez sur l'onglet Paramètres du serveur.
4. Sous Comportement du serveur, activez ou désactivez la case à cocher Autoriser l'activation des déclencheurs qui activent d'autres déclencheurs (déclencheurs imbriqués).
Modification des paramètres de configuration d'une base de données (Enterprise Manager)
Modification des paramètres de configuration d'une base de données
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, cliquez avec le bouton droit sur la base de données à modifier, puis cliquez sur Propriétés.
3. Cliquez sur l'onglet Options et choisissez les paramètres de configuration à modifier.
Utilisation des déclencheurs INSTEAD OF
Les déclencheurs INSTEAD OF remplacent les actions standard de l'instruction de déclenchement (INSERT, UPDATE ou DELETE). Un déclencheur INSTEAD OF peut être défini pour effectuer une vérification d'erreur ou de valeur sur une ou plusieurs colonnes, puis effectuer des actions supplémentaires avant d'insérer l'enregistrement. Par exemple, lorsque la valeur mise à jour dans une colonne de salaire horaire dans une table de registre du personnel dépasse une valeur spécifiée, il est possible de définir un déclencheur qui soit produit un message d'erreur et annule la transaction, soit insère un nouvel enregistrement dans un journal d'audit avant d'insérer l'enregistrement dans la table de registre du personnel.
Les déclencheurs INSTEAD OF peuvent être définis sur des tables ou des vues ; toutefois, les déclencheurs INSTEAD OF sont plus utiles pour l'extension des types de mises à jour qu'une vue peut prendre en charge. Par exemple, les déclencheurs INSTEAD OF peuvent permettre de modifier des tables de base multiples au travers d'une vue ou de modifier des tables de base contenant les colonnes suivantes :
· type de données timestamp
· colonnes calculées
· colonnes d'identité

Déclencheurs INSTEAD OF INSERT
Les déclencheurs INSTEAD OF INSERT peuvent être définis sur une vue ou une table pour remplacer l'action standard de l'instruction INSERT. Les déclencheurs INSTEAD OF INSERT sont généralement définis sur une vue pour insérer des données dans une ou plusieurs tables de base.
Les colonnes figurant dans la liste de sélection de la vue peuvent accepter ou refuser les valeurs NULL. Si une colonne de la vue n'accepte pas de valeur NULL, une instruction INSERT doit lui fournir des valeurs. Les colonnes d'une vue autorisent les valeurs NULL si l'expression qui les définit comprend des éléments tels que :
· des références à une colonne de table de base qui accepte les valeurs NULL ;
· des opérateurs arithmétiques ;
· des références à des fonctions ;
· une fonction CASE ou COALESCE avec une sous-expression acceptant des valeurs NULL ;
· NULLIF.
Vous pouvez utiliser la propriété AllowsNull indiquée par la fonction COLUMNPROPERTY pour déterminer si une colonne de vue accepte des valeurs NULL. La procédure stockée système sp_help indique également les colonnes de la vue qui acceptent des valeurs NULL.
Une instruction INSERT faisant référence à une vue sur laquelle est défini un déclencheur INSTEAD OF INSERT doit fournir des valeurs pour toutes les colonnes de la vue qui n'acceptent pas de valeur NULL, et notamment celles qui font référence à des colonnes de la table de base pour lesquelles aucune valeur d'entrée ne peut être spécifiée :
· colonnes calculées de la table de base ;
· colonnes identité de la table de base pour lesquelles IDENTITY_INSERT est désactivée (OFF) ;
· colonnes de la table de base de type de données timestamp.
Si le déclencheur INSTEAD OF INSERT de la vue génère une instruction INSERT sur la table de base avec les données de la table inserted, il doit ignorer les valeurs correspondant à ces types de colonnes en n'incluant pas les colonnes dans la liste de sélection de l'instruction INSERT. L'instruction INSERT peut générer des valeurs factices pour ces types de colonnes.
Par exemple, lorsqu'une instruction INSERT doit spécifier une valeur pour une colonne de vue qui correspond à une colonne calculée de la table de base, elle peut fournir une valeur d'espace réservé. Le déclencheur INSTEAD OF peut ignorer la valeur fournie lorsqu'il élabore l'instruction INSERT qui insère les valeurs dans la table de base.
Les instructions suivantes illustrent ce processus au travers de la création d'une table, d'une vue et d'un déclencheur :
CREATE TABLE BaseTable
(PrimaryKey int IDENTITY(1,1)
Color nvarchar(10) NOT NULL,
Material nvarchar(10) NOT NULL,
ComputedCol AS (Color + Material)
)
GO

--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
GO

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserted.PrimaryKey and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color, Material
FROM inserted
END
GO
Une instruction INSERT qui fait directement référence à BaseTable ne peut pas fournir de valeur pour les colonnes PrimaryKey et ComputedCol. Exemple :
--A correct INSERT statement that skips the PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
VALUES (N'Red', N'Cloth')

--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable

--An incorrect statement that tries to supply a value for the
--PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable
VALUES (2, N'Green', N'Wood', N'GreenWood')
Cependant, les instructions INSERT qui font référence à InsteadView doivent fournir une valeur pour PrimaryKey et ComputedCol :
--A correct INSERT statement supplying dummy values for the
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView
La table inserted transmise à InsteadTrigger est conçue avec une colonne PrimaryKey et ComputedCol n'acceptant pas de valeur NULL, si bien que l'instruction INSERT qui fait référence à la vue doit fournir une valeur pour ces colonnes. Les valeurs 999 et N'XXXXXX' sont transmises à InsteadTrigger, mais l'instruction INSERT du déclencheur ne sélectionne pas inserted.PrimaryKey ou inserted.ComputedCol, si bien que les valeurs sont ignorées. La ligne effectivement insérée dans BaseTable présente la valeur 2 dans PrimaryKey et N'BluePlastic' dans ComputedCol.
Les valeurs contenues dans la table inserted pour les colonnes calculées, identité et timestamp diffèrent selon que vous appliquez des déclencheurs INSTEAD OF INSERT spécifiés sur des tables ou sur des vues.
Colonne de table de base Valeur dans la table inserted d'un déclencheur INSERT de table Valeur dans la table inserted d'un déclencheur INSTEAD OF INSERT de vue
Est une colonne calculée. Expression calculée Valeur définie par l'utilisateur ou NULL
elle a une propriété IDENTITY, 0 si IDENTITY_INSERT est désactivée (OFF), valeur spécifiée si IDENTITY_INSERT est activée (ON) Valeur définie par l'utilisateur ou NULL
elle a un type de données timestamp, Zéros binaires si la colonne n'accepte pas de valeur NULL, NULL dans le cas contraire Valeur définie par l'utilisateur ou NULL

Une instruction INSERT qui fait directement référence à une table de base n'a pas besoin de fournir de valeurs pour une colonne NOT NULL dotée d'une valeur par défaut (DEFAULT). Si elle ne fournit pas de valeur, la valeur par défaut (DEFAULT) est utilisée. Toutefois, si une colonne NOT NULL possédant une valeur par défaut (DEFAULT) est référencée par une expression simple dans une vue à laquelle est associé un déclencheur INSTEAD OF INSERT, toute instruction INSERT faisant référence à la vue doit fournir une valeur pour la colonne. Cette valeur est requise pour élaborer la table inserted transmise au déclencheur. Une convention doit être adoptée quant à la valeur indiquant au déclencheur que la valeur par défaut doit être utilisée. La meilleure convention consiste à ce que l'instruction INSERT fournisse la valeur par défaut.
Déclencheurs INSTEAD OF UPDATE
Les déclencheurs INSTEAD OF UPDATE peuvent être définis sur une vue ou une table pour remplacer l'action standard de l'instruction UPDATE. Les déclencheurs INSTEAD OF UPDATE sont généralement définis sur une vue pour modifier des données dans une ou plusieurs tables de base.
Une instruction UPDATE qui fait référence à une vue dotée d'un déclencheur INSTEAD OF UPDATE doit fournir des valeurs pour toutes les colonnes de vue n'acceptant pas de valeur NULL et référencées dans la clause SET, et notamment celles qui font référence à des colonnes de la table de base pour lesquelles aucune valeur d'entrée ne peut être spécifiée :
· colonnes calculées de la table de base ;
· colonnes identité de la table de base pour lesquelles IDENTITY_INSERT est désactivée (OFF) ;
· colonnes de la table de base de type de données timestamp.
Généralement, lorsqu'une instruction UPDATE qui référence une table tente de définir la valeur d'une colonne calculée, d'identité ou timestamp, une erreur est générée car les valeurs de ces colonnes doivent être déterminées par Microsoft® SQL Server ™. Ces colonnes doivent être incluses dans l'instruction UPDATE afin de satisfaire l'exigence NOT NULL de la colonne. Toutefois, si l'instruction UPDATE référence une vue avec un déclencheur INSTEAD OF UPDATE, la logique définie dans le déclencheur peut ignorer ces colonnes et éviter l'erreur. Pour cela, le déclencheur INSTEAD OF UPDATE ne doit pas essayer de mettre à jour les valeurs des colonnes correspondantes de la table de base. Cette opération s'effectue en n'incluant pas les colonnes dans la clause SET de l'instruction UPDATE. Lors du traitement d'un enregistrement à partir de la table inserted, il se peut que la colonne calculée, d'identité ou timestamp contienne une valeur factice pour satisfaire l'exigence NOT NULL de la colonne, mais le déclencheur INSTEAD OF UPDATE ignore ces valeurs et les valeurs correctes sont définies par SQL Server.
Un déclencheur INSTEAD OF UPDATE n'a pas besoin de traiter les données émanant de colonnes inserted non mises à jour. Les colonnes de la table inserted transmise à un déclencheur INSTEAD OF UPDATE et spécifiées dans la clause SET respectent les mêmes règles que les colonnes inserted figurant dans un déclencheur INSTEAD OF INSERT. Concernant les colonnes non spécifiées dans la clause SET, les valeurs contenues dans la table inserted sont celles qui existaient avant l'émission de l'instruction UPDATE. Le déclencheur peut tester si une colonne donnée a été mise à jour à l'aide de la clause IF UPDATED(column).
Il doit utiliser les valeurs fournies pour les colonnes calculées, identité ou timestamp uniquement dans les conditions de recherche d'une clause WHERE.
La logique que doit utiliser le déclencheur INSTEAD OF UPDATE d'une vue afin de traiter les valeurs mises à jour fournies pour les colonnes calculées, identité, timestamp ou dotée d'une valeur par défaut est la même que celle appliquée aux valeurs insérées pour ces types de colonnes.

Remarque Les déclencheurs INSTEAD OF UPDATE ne peuvent pas être définis sur une table qui a une clé étrangère définie avec une action UPDATE.

Les déclencheurs INSTEAD OF DELETE peuvent être définis sur une vue ou une table pour remplacer l'action standard de l'instruction DELETE. Les déclencheurs INSTEAD OF DELETE sont généralement définis sur une vue pour modifier des données dans une ou plusieurs tables de base.
Les instructions DELETE ne spécifient pas de modifications pour les valeurs de données existantes. Elles indiquent uniquement les lignes à supprimer. La table inserted transmise à un déclencheur DELETE est toujours vide. La table deleted envoyée à un déclencheur DELETE contient une image des lignes dans l'état où elles se trouvaient avant l'émission de l'instruction UPDATE. Dans le cas d'un déclencheur INSTEAD OF DELETE créé sur une vue, le format de la table deleted est basé sur celui de la liste de sélection définie pour la vue.

Remarque Les déclencheurs INSTEAD OF DELETE ne peuvent pas être définis sur une table qui a une clé étrangère définie avec une action DELETE

Expressions et colonnes calculées dans les déclencheurs INSTEAD OF
La liste de sélection d'une vue peut contenir des expressions autres que des expressions simples composées uniquement d'un nom de colonne. Les déclencheurs INSTEAD OF créés sur ces vues doivent posséder une logique telle qu'il leur soit possible de déterminer correctement, parmi les valeurs spécifiées dans les instructions INSERT et UPDATE, celles qui doivent être appliquées aux colonnes de la table de base. Voici quelques exemples d'expressions de ce type :
· les expressions de vue sans mappage vers une colonne d'une table quelle qu'elle soit, telles qu'une constante ou certains types de fonctions ;
· les expressions de vue avec mappage vers plusieurs colonnes, telles que les expressions complexes qui résultent d'une concaténation de chaînes à partir de deux ou de plusieurs colonnes ;
· les expressions de vue qui transforment la valeur d'une seule colonne de la table de base, comme dans le cas d'une référence à une colonne dans une fonction.
Ces considérations s'appliquent également aux colonnes de vue qui sont des expressions simples faisant référence à une colonne calculée dans une table de base. L'expression qui définit la colonne calculée peut présenter la même forme qu'une expression plus complexe dans la liste de sélection de la vue.
Les vues peuvent contenir, dans leur liste de sélection, des expressions n'établissant de mappage vers aucune colonne de la table de base :
CREATE VIEW ExpressionView
AS
SELECT *, GETDATE() AS TodaysDate
FROM Northwind.dbo.Employees
Alors que la colonne TodaysDate n'est mappée vers aucune colonne de la table, SQL Server doit créer une colonne TodaysDate dans la table inserted qu'il transmet à un déclencheur INSTEAD OF défini sur ExpressionView. Toutefois, la colonne inserted.TodaysDate acceptant les valeurs NULL, une instruction INSERT faisant référence à ExpressionView ne doit pas forcément fournir une valeur pour cette colonne. Étant donné que l'expression n'établit de mappage vers aucune colonne de table, le déclencheur peut ignorer toute valeur fournie par l'insertion (INSERT) dans cette colonne.
La même approche doit s'appliquer aux expressions de vue simples faisant référence à des colonnes calculées de tables de base et dont le résultat ne dépend d'aucune autre colonne :
CREATE TABLE ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
)
Certaines expressions complexes établissent un mappage vers plusieurs colonnes :
CREATE TABLE SampleTable
(
PriKey int,
FirstName nvarchar(20),
LastName nvarchar(30)
)
GO
CREATE VIEW ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable
Les valeurs de l'expression CombinedName dans ConcatView sont le fruit de la concaténation des valeurs FirstName et LastName. Si un déclencheur INSTEAD OF INSERT est défini sur ConcatView, vous devez disposer d'une convention qui établit la façon dont les instructions INSERT fournissent une valeur pour la colonne CombinedName grâce à laquelle le déclencheur détermine la partie de la chaîne à intégrer dans la colonne FirstName et celle qu'il faut placer dans la colonne LastName. Si vous souhaitez que les instructions INSERT spécifient la valeur de CombinedName suivant la convention 'first_name;last_name', ce déclencheur est à même de traiter une opération d'insertion :
CREATE TRIGGER InsteadSample on ConcatView
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO SampleTable
SELECT PriKey,
-- Pull out the first name string.
SUBSTRING(
CombinedName,
1,
(CHARINDEX(';', CombinedName) - 1)
),
-- Pull out the last name string.
SUBSTRING(
CombinedName,
(CHARINDEX(';', CombinedName) + 1),
DATALENGTH(CombinedName)
)
FROM inserted
END
Une logique similaire est requise pour traiter les colonnes de vue qui sont des expressions simples faisant référence à des colonnes calculées dotées d'expressions complexes.
Certaines expressions de vue peuvent transformer la valeur d'une colonne de table de base, par exemple en effectuant une opération mathématique ou en utilisant la colonne comme paramètre de fonction. Dans ce cas, la logique du déclencheur INSTEAD OF INSERT peut répondre à deux approches :
· La convention peut établir que toutes les instructions INSERT fournissent la valeur brute à placer dans la table de base, et la logique du déclencheur déplace la valeur de la table inserted vers la table de base.
· La convention peut établir que toutes les instructions INSERT fournissent la valeur qui doit normalement être renvoyée par une opération SELECT appliquée à la vue, auquel cas la logique du déclencheur doit inverser l'opération. Exemple :
CREATE TABLE BaseTable
(
PrimaryKey int PRIMARY KEY,
ColumnB int,
ColumnC decimal(19,3)
)

CREATE VIEW SquareView AS
SELECT PrimaryKey, ColumnB,
-- Square the value of ColumnC
SQUARE(ColumnC) AS SquareC
FROM BaseTable

CREATE TRIGGER SquareTrigger ON SquareView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO BaseTable
SELECT PrimaryKey, ColumnB,
-- Perform logical inverse of function in view.
SQRT(SquareC)
FROM inserted
END
Pour certaines expressions, telles que les expressions complexes utilisant des opérations mathématiques comme l'addition et la soustraction, il peut s'avérer impossible pour les utilisateurs de fournir une valeur qui permette au déclencheur d'élaborer des valeurs sans ambiguïté pour les colonnes de la table de base de destination. Par exemple, si la liste de sélection d'une vue contient l'expression IntColA + IntColB AS AddedColumns, que représente la valeur 10 dans inserted.AddedColumns ? 10 est-il le résultat de 3 + 7, 2 + 8 ou 5 + 5 ? Il est impossible de déterminer, uniquement à partir de la valeur de inserted.AddedColumns, les valeurs à placer dans IntColA et IntColB.
En pareil cas, vous pouvez programmer le déclencheur de telle sorte qu'il utilise d'autres sources d'informations pour déterminer les valeurs à définir dans les colonnes de la table de base. La liste de sélection des vues dotées d'un déclencheur INSTEAD OF doit contenir suffisamment d'informations pour l'élaboration des valeurs de toutes les colonnes non NULL des tables de base modifiées par le déclencheur. Les données ne doivent pas toutes provenir directement de la table inserted. Dans certains cas, les valeurs de la table inserted peuvent être des valeurs de clé utilisées par le déclencheur pour extraire les données adéquates d'autres tables de base.

Données text, ntext et image dans les déclencheurs INSTEAD OF
Les modifications de données peuvent impliquer des colonnes text, ntext et image. Dans les tables de base, la valeur stockée dans une colonne text, ntext ou image est un pointeur de texte qui pointe vers les pages dans lesquelles figurent les données.
Contrairement aux déclencheurs AFTER, les déclencheurs INSTEAD OF prennent en charge les données text, ntext ou image dans les tables inserted et deleted. Le stockage de ces types de données dans les tables inserted et deleted ne s'effectue pas de la même façon que celui des données dans les tables de base : ces données ne sont pas stockées sous la forme d'une chaîne de pages séparée. Elles sont au contraire représentées par une chaîne continue dans chaque ligne, ce qui signifie qu'il n'existe pas de pointeur de texte pour les colonnes text, ntext ou image dans les tables inserted et deleted. Les fonctions TEXTPTR et TEXTVALID ainsi que les instructions READTEXT, UPDATETEXT et WRITETEXT ne peuvent pas être appliquées aux colonnes text, ntext ou image des tables inserted ou deleted. Toutes les autres utilisations des colonnes text, ntext et image sont prises en charge, comme leur référencement dans des listes de sélection, les conditions de recherche de clause WHERE ou les fonctions SUBSTRING, PATINDEX et CHARINDEX. Les opérations sur des données text, ntext ou image dans des déclencheurs INSTEAD OF sont affectées par l'option SET TEXTSIZE en cours, qui peut être déterminée avec la fonction @@TEXTSIZE.
Le type de données text, ntext ou image stocké dans les tables inserted et deleted varie suivant l'action de déclenchement (INSERT, UPDATE ou DELETE) :
· Dans le cas d'une instruction INSERT, la table inserted contient la nouvelle valeur de la colonne text, ntext ou image. Aucune ligne ne figure dans la table deleted.
· Dans le cas d'une instruction DELETE, aucune ligne ne figure dans la table inserted, et les lignes de la table deleted contiennent les valeurs que détenait la colonne text, ntext ou image avant l'exécution de l'instruction DELETE.
· Dans le cas d'une instruction UPDATE qui ne modifie pas la valeur text, ntext ou image, les lignes des tables inserted et deleted contiennent les mêmes valeurs pour les colonnes text, ntext ou image.
· Dans le cas d'une instruction UPDATE qui modifie la valeur text, ntext ou image, les valeurs de données contenues dans la table deleted sont celles qui existaient avant que l'instruction ne débute, tandis que celles de la table inserted reflètent toute modification définie dans la clause SET.
Si une instruction INSERT, UPDATE ou DELETE modifie de nombreuses lignes contenant des valeurs text, ntext ou image volumineuses, d'importantes ressources mémoire peuvent être requises pour le stockage des copies des données text, ntext ou image dans les tables inserted et deleted. La copie de ces quantités importantes de données peut également affaiblir les performances. Les instructions INSERT, UPDATE et DELETE qui font référence à des vues ou des tables détenant des déclencheurs INSTEAD OF doivent modifier une ligne à la fois ou uniquement quelques lignes à la fois, dans la mesure du
Activation de déclencheurs avec des valeurs NULL implicites et explicites
L'insertion dans une colonne d'une valeur NULL explicite ou l'utilisation du mot clé DEFAULT pour affecter une valeur à une colonne activent le déclencheur comme prévu. De même, si aucune valeur n'est spécifiée dans l'instruction INSERT pour une colonne, le déclencheur est malgré tout activé si :
· une valeur NULL implicite est insérée dans une colonne car aucune définition DEFAULT n'existe ;
· une valeur par défaut est insérée dans une colonne car une définition DEFAULT existe.
Exemple
A. Activation de déclencheurs test avec des valeurs NULL et par défaut
Les exemples suivants montrent comment un déclencheur est affecté par des valeurs NULL implicites et explicites. Une petite table est créée afin de contenir deux valeurs entières. Une colonne peut contenir des valeurs NULL ; l'autre colonne contient une valeur par défaut. Un déclencheur détecte lorsque les deux colonnes sont modifiées et affiche un message lors de son activation. Une série d'instructions INSERT teste l'activation du déclencheur en insérant des combinaisons de valeurs NULL implicites et explicites.
CREATE TABLE t1
(a int NULL, b int NOT NULL DEFAULT 99)
GO

CREATE TRIGGER t1trig
ON t1
FOR INSERT, UPDATE
AS
IF UPDATE(a) AND UPDATE(b)
PRINT 'FIRING'
GO

--When two values are inserted, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (a, b)
VALUES (1, 2)

--When two values are updated, the UPDATE is TRUE for both columns and the trigger is activated.
UPDATE t1
SET a = 1, b = 2

--When an explicit NULL is inserted in column a, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1
VALUES (NULL, 2)

--When an explicit NULL is updated in column a, the UPDATE is TRUE for both columns,the trigger is activated.
UPDATE t1
SET a = NULL, b = 2

--When an implicit NULL is inserted in column a, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (b)
VALUES (2)

--When column a is updated with an implicit NULL, the UPDATE is FALSE for both columns and the trigger is not activated.
UPDATE t1
SET b = 2

--When the default value is implicitly inserted in column b, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (a)
VALUES (2)

--When column b is updated with an implicit NULL, the UPDATE is FALSE for both columns and the trigger is not activated.
UPDATE t1
SET a = 2

--When the default value is explicitly inserted in column b, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (a, b)
VALUES (2, DEFAULT)

--When column b is updated explicitly with the default value, the UPDATE is TRUE for both columns and the trigger is activated.
UPDATE t1
SET a = 2, b = DEFAULT
Modification d'un déclencheur et de son nom
Si vous devez modifier la définition d'un déclencheur, vous pouvez soit le supprimer puis le recréer, soit redéfinir le déclencheur existant en une seule opération.
Si vous changez le nom d'un objet référencé par un déclencheur, vous devez modifier le déclencheur pour que sa définition fasse référence au nouveau nom de l'objet. Par conséquent, avant de renommer un objet, affichez les dépendances de l'objet pour savoir si des déclencheurs peuvent être concernés par la modification projetée.
Vous pouvez aussi renommer un déclencheur. Le nouveau nom doit se conformer aux règles en vigueur pour les identificateurs. Un utilisateur peut uniquement renommer les déclencheurs dont il est le propriétaire, mais le propriétaire de la base de données peut changer le nom des déclencheurs de n'importe quel utilisateur. Le déclencheur à renommer doit se trouver dans la base de données en cours.
Un déclencheur peut aussi être modifié pour en crypter la définition.

Modification d'un déclencheur (Enterprise Manager)
Pour modifier un déclencheur
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la table qui contient le déclencheur, puis cliquez sur Tables.
3. Dans le volet de détails, cliquez avec le bouton droit sur la table dans laquelle se trouve le déclencheur, pointez sur Toutes les tâches, puis cliquez sur Gérer les déclencheurs.
4. Dans la liste Nom, sélectionnez le nom du déclencheur.
5. Modifiez le texte du déclencheur dans le champ Texte comme il convient. Utilisez les touches CTRL+TAB pour créer un alinéa dans le texte du déclencheur SQL Enterprise Manager.
6. Pour vérifier la syntaxe, cliquez sur Vérifier la syntaxe.

Remarque En renommant un déclencheur, vous ne changez pas le nom du déclencheur dans le texte de sa définition. Pour cela, vous devez modifier directement le déclencheur.
Affichage des dépendances d'un déclencheur (Enterprise Manager)
Affichage des dépendances d'un déclencheur
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient le déclencheur, puis cliquez sur Tables.
3. Dans le volet de détails, cliquez avec le bouton droit sur la table à laquelle appartient le déclencheur, pointez sur Toutes les tâches, puis cliquez sur Afficher les dépendances.
4. Dans Objet, cliquez sur le nom du déclencheur dont vous souhaitez afficher les dépendances.
Affichage d'un déclencheur
SQL Server permet de connaître les types de déclencheurs existant pour une table, ainsi que leur nom, leur propriétaire et la date de leur création.
Vous pouvez aussi :
· obtenir des informations sur la définition d'un déclencheur s'il n'a pas été crypté lorsqu'il a été créé ou modifié, ce qui peut être utile pour prendre connaissance des instructions Transact-SQL utilisées ou pour comprendre comment il agit sur la table pour laquelle il est défini ;
· dresser la liste des objets utilisés par le déclencheur spécifié, informations notamment utilisées pour identifier les objets dont l'éventuelle suppression ou modification aurait une répercussion sur le déclencheur.
L'ensemble de résultats de sp_helptrigger contient les colonnes isafter et isinsteadof, qui indiquent si un déclencheur est de type AFTER ou INSTEAD OF. La fonction OBJECTPROPERTY précise si un déclencheur est de type AFTER ou INSTEAD OF par le biais des propriétés ExecIsInsteadOfTrigger et ExecIsAfterTrigger.
Affichage d'un déclencheur (Enterprise Manager)
Affichage d'un déclencheur
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la table qui contient le déclencheur, puis cliquez sur Tables.
3. Dans le volet de détails, cliquez avec le bouton droit sur la table dans laquelle se trouve le déclencheur, pointez sur Toutes les tâches, puis cliquez sur Gérer les déclencheurs.
Affichage des dépendances d'un déclencheur (Enterprise Manager)
Affichage des dépendances d'un déclencheur
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient le déclencheur, puis cliquez sur Tables.
3. Dans le volet de détails, cliquez avec le bouton droit sur la table à laquelle appartient le déclencheur, pointez sur Toutes les tâches, puis cliquez sur Afficher les dépendances.
4. Dans Objet, cliquez sur le nom du déclencheur dont vous souhaitez afficher les dépendances.
Suppression d'un déclencheur
Supprimez un déclencheur s'il ne vous est plus utile. Alors, la table et les données sur lesquelles il est basé ne sont pas affectées. La suppression d'une table supprime automatiquement les déclencheurs qui en dépendent. Les autorisations de suppression d'un déclencheur sont attribuées par défaut au propriétaire de la table sur laquelle le déclencheur a été défini.
Suppression d'un déclencheur (Enterprise Manager)
Suppression d'un déclencheur
1. Développez un groupe de serveurs, puis développez un serveur.
2. Développez le dossier Bases de données, développez la base de données à laquelle appartient la table qui contient le déclencheur, puis cliquez sur Tables.
3. Dans le volet de détails, cliquez avec le bouton droit sur la table dans laquelle se trouve le déclencheur, pointez sur Toutes les tâches, puis cliquez sur Gérer les déclencheurs.
4. Dans la liste Nom, cliquez sur le nom du déclencheur à supprimer.
5. Cliquez sur Supprimer.
6. Confirmez la suppression.








EXERCICES sur les procedures stockées

1-Recherche d'une occurrence de mot dans n'importe quelle colonne de type caractères d'une table donnée
Ecrire une procédure stockée pour cette tâche

CORRECTION


CREATE PROCEDURE SP_SEARCH_STRING_ANYFIELD
@TableName Varchar(128), -- nom de la table passé en argument
@SearchWord Varchar(32) -- mot recherché
AS

IF @TableName IS NULL OR @SearchWord IS NULL
RAISERROR ('Paramètres NULL impossible à traiter', 16, 1)
IF @@ERROR <> 0 GOTO LBL_ERROR

-- test d'existence de la table
IF NOT EXISTS(SELECT *
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_NAME = @TableName)
RAISERROR ('Références de table inconnue %s', 16, 1, @TableName)
IF @@ERROR <> 0 GOTO LBL_ERROR

Declare @ColumnList varchar(1000) -- liste des noms de colonnes dans
-- lesquels la recherche va s'effectuer
Declare @SQL varchar(1200) -- requête de recherche

-- obtention de la liste des colonnes pour la requête de recherche
SELECT @ColumnList = COALESCE(@ColumnList + ' + COALESCE(', 'COALESCE(') + column_name +', '''')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND DATA_TYPE LIKE '%char%'

IF @ColumnList IS NULL
RAISERROR ('Aucune colonne de recherche trouvé dans la table %s',
16, 1, @TableName)
IF @@ERROR <> 0 GOTO LBL_ERROR

PRINT 'INFO - @ColumnList value is : ' + @ColumnList

-- assemblage du texte de la requête de recherche
Set @SQL =
'SELECT * FROM '+ @TableName
+ ' WHERE ' + @ColumnList
+ ' LIKE ''%' + @SearchWord +'%'''

PRINT 'INFO - @SQL value is : ' + @SQL

-- exécution de la requête de recherche
Exec (@SQL)

RETURN

LBL_ERROR:
PRINT 'ERREUR LORS DE L''EXÉCUTION DE LA PROCÉDURE STOCKÉE SP_SEARCH_STRING_ANYFIELD'

2-Il s'agit de donner la liste de tous les nombres premiers entre 1 et 5000.

Correction

Create porocedur nb-premier
As

* recherche de tous les nombres premiers de 1 à 5000 */
/* version procédurale (itérations) */
-- création d'une table provisoire pour stockage des données
create table #n (n int)

declare @n integer, @i integer, @premier bit
set @n = 1
set nocount on
-- un nombre premier n'est divisible que par 1 et lui même
while @n < 5000
BEGIN
-- on pré suppose qu'il est premier
set @premier = 1
set @i = 2
while @i < @n
BEGIN
-- autrement dit, tout diviseur situé entre 2 et lui même moins un
-- fait que ce nombre n'est pas premier
if (@n / @i) * @i = @n
SET @premier = 0
SET @i = @i + 1
END
if @premier = 1
insert into #n VALUES (@n)
SET @n = @n + 1
END

SELECT * FROM #n

3-Par ce code procédural, nous avons utilisé la formulation suivante : "n est premier si aucun nombre de 2 à n-1 ne le divise".

Une autre façon de faire est de travailler en logique ensembliste. Si nous disposons d'une table des entiers, il est alors facile de comprendre que les nombres premiers sont tous les nombres, moins ceux qui ne sont pas premiers... Il s'agit ni plus ni moins que de réaliser une différence ensembliste.

Correction
Create porocedur nb-premier_ver2
As


* recherche de tous les nombres premiers de 1 à 5000 */
/* version ensembliste (requêtes) */
DECLARE @max int
SET @max = 5000
-- cet exemple utilise la logique ensembliste pour calculer tous les nombres entiers
SET NOCOUNT ON
-- table temporaire de stockage des entiers de 1 à 5000
CREATE TABLE #n (n int)
-- boucle d'insertion de 0 à 5000
DECLARE @i int
SET @i = 0
WHILE @i < @max
BEGIN
INSERT INTO #n VALUES (@i)
SET @i = @i + 1
END
-- on prend tous les entiers de la table n moins les entiers de la table n pour
-- lesquels le reste de la division entière (modulo) par un entier moindre donne 0
-- NOTA l'opération MODULO se note % dans Transact SQL
SELECT distinct n
FROM #n
WHERE n not in (SELECT distinct n1.n
FROM #n n1
CROSS JOIN #n n2
WHERE n1.n % n2.n = 0
AND n2.n BETWEEN 2 AND n1.n - 1)
ORDER BY n

4- Améliorer ce programme en notant la remarque suivante :
limiter le diviseur au maximum à CAST(SQRT(CAST(n2.n AS FLOAT)) AS INTEGER) + 1, car le plus grand des diviseurs d'un nombre ne peut dépasser sa racine carrée.

EXERCICES sur les triggers

1- - contrôle de validité de format de données. On désire empêcher la saisie de tout numéro de téléphone dans la table client qui possède d'autres caractères que des chiffres (au maximum 20) et des points de séparation :

CORRECTION

CREATE TRIGGER E_CLI_INS
ON T_CLIENT
FOR INSERT, UPDATE
AS
-- requête de contrôle avec table d'insertion
SELECT CAST(REPLACE(CLI_TEL, '.', '') as DECIMAL(20))
FROM INSERTED
-- rollback en cas d'erreur
IF @@Error <> 0
ROLLBACK TRANSACTION
La première tentative de modification :

UPDATE T_CLIENT
SET CLI_TEL = '01 02 03 04 05'
WHERE CLI_ID = 1
Serveur : Msg 8114, Niveau 16, État 5, Procédure E_CLI_INS, Ligne 6
Erreur de conversion du type de données varchar en numeric.
provoque une erreur et l'insertion n'a pas lieu.

Tandis que la seconde va bien produire ses effets :

UPDATE T_CLIENT
SET CLI_TEL = '91.92.93.94.95'
WHERE CLI_ID = 1
Le seul inconvénient est que cette façon de procéder rejette toutes les lignes insérées ou mise à jour sans accepter celles qui peuvent être correctement formatées.
D'autre part on exécute cette procédure jusqu'au bout, même si la colonne CLI_TEL ne subie aucune modification. Néanmoins ce cas peut être résolu par un traitement spécifique utilisant la fonction UPDATE :

CREATE TRIGGER E_CLI_INS
ON CLIENT
FOR INSERT, UPDATE
AS
-- inutile si pas d'update de la colonne visée
IF NOT UPDATE(CLI_TEL)
RETURN
-- requête de contrôle avec table d'insertion
SELECT CAST(REPLACE(CLI_TEL, '.', '') as DECIMAL(20))
FROM INSERTED
-- rollback en cas d'erreur
IF @@Error <> 0
ROLLBACK TRANSACTION

2- - L'exercice consiste maintenant à corriger à la volée des saisie incorrectes. Tous les caractères de séparation tel que le tiret ou l'espace d'un numéro de téléphone devra être convertis en point.

CORRECTION


CREATE TRIGGER E_CLI_INS
ON CLIENT
FOR UPDATE
AS
-- inutile si pas d'update de la colonne visée
IF NOT UPDATE(CLI_TEL)
RETURN
-- requête de correction avec table d'insertion
UPDATE client
SET cli_tel =
REPLACE(REPLACE(I.CLI_TEL, ' ', '.'), '-', '.')
FROM T_CLIENT C
INNER JOIN INSERTED I
ON C.CLI_ID = I.CLI_ID
-- rollback en cas d'erreur
IF @@Error <> 0
ROLLBACK TRANSACTION
Ainsi l'ordre :

UPDATE T_CLIENT
SET CLI_TEL = '88 77-66 55.44'
WHERE CLI_ID = 1
donne pour résultat :

cli_id cli_nom Cli_tel
----------- -------------------------------- --------------------
1 DUPONT 88.77.66.55.44
et la saisie du numéro de téléphone a été corrigé à la volée et se trouve désormais au format voulu !

Attention : le danger réside dans l'exécution récursive de tels triggers. Comme l'on remet à jour la table à l'intérieur même du trigger, celui-ci est à nouveau déclenché. Le phénomène, s'il n'était pas limité, pourrait provoquer une famine du processus. Il faut donc veiller à le limiter. Dans ce sens SQL Server propose deux garde fous : le premier, intrinsèque au serveur est de ne jamais dépasser 16 niveaux de récursion. Le second est de proposer une limite plus restrictive à l'aide de la procédure sp_configure, qui permet de modifier la variable nested triggers afin d'étendre les limites d'appel de triggers imbriqués.
De plus pour connaître le niveau d'imbrication du trigger à l'intérieur de ce dernier il suffit de lancer la fonction TRIGGER_NESTLEVEL() qui renvoie une variable de niveau.
Conseil : il est préférable de ne pas utiliser de triggers imbriqués et donc de laisser le paramètre nested triggers de la configuration à 1.

3-Bien entendu ou pourrait être beaucoup plus fin dans ce genre de contrôle et analyser puis remplacer, caractères par caractères.
A titre d’ exemple, nous allons réaliser un tel trigger :

CREATE TRIGGER E_CLI_INS
ON CLIENT
FOR UPDATE
AS
-- inutile si pas d'update de la colonne visée
IF NOT UPDATE(CLI_TEL)
RETURN

-- ouverture d'un curseur sur la table INSERTED
-- pour les téléphones renseignés
DECLARE CurIns CURSOR
FOR
SELECT CLI_ID, CLI_TEL
FROM INSERTED
WHERE CLI_TEL IS NOT NULL
IF @@error <> 0 GOTO LBL_ERROR

-- variable de travail
DECLARE @IdCli int, @TelAvant VARCHAR(20), @TelApres VARCHAR(20),
@car CHAR(1), @i int, @j int

-- ouverture du curseur
OPEN CurIns
IF @@error <> 0 GOTO LBL_ERROR

-- lecture première ligne
FETCH CurIns INTO @IdCli, @TelAvant

-- boucle de lecture
WHILE @@Fetch_Status = 0
BEGIN
-- si vide reboucle immédiatement
IF @TelAvant = ''
BEGIN
FETCH CurIns INTO @IdCli, @TelAvant
CONTINUE
END
-- scrutation de la valeur du téléphone
SET @i = 1
SET @j = 0
SET @TelApres = ''
-- boucle de nettoyage sur tous les caractères
WHILE @i <= LEN(@TelAvant)
BEGIN
-- reprise du caractère d'ordre i
SET @car = SUBSTRING(@TelAvant,@i,1)
-- on ne traite que les caractères de 0 à 9
IF @car = '0' or @car = '1' or @Car = '2' or @Car = '3'
or @car = '4' or @car = '5' or @Car = '6' or @Car = '7'
or @car = '8' or @car = '9'
BEGIN
SET @TelApres = @TelApres + @Car
SET @j = @j + 1
END
SET @i =@i + 1
END
-- si vide reboucle immédiatement
IF @TelApres = ''
BEGIN
FETCH CurIns INTO @IdCli, @TelAvant
CONTINUE
END
-- découpage par tranche de 2 nombres
SET @TelAvant = @TelApres
SET @i = 1
SET @TelApres = ''
-- boucle de découpage
WHILE @i <= LEN(@TelAvant)
BEGIN
SET @car = SUBSTRING(@TelAvant,@i,1)
SET @TelApres = @TelApres + @Car
IF @i % 2 = 0
SET @TelApres = @TelApres + '-'
SET @i =@i + 1
END
-- petit effet de bord si @TelApres se termine par un nombre pair,
-- alors tiret en trop !
IF @j % 2 = 0 -- au pasage % est la fonction MODULO dans SQL Server
SET @TelApres = SUBSTRING(@TelApres, 1, LEN(@TelApres)-1)
-- mise à jour si différence
IF @TelAvant <> @TelApres
UPDATE CLIENT
SET CLI_TEL = @TelApres
WHERE CLI_ID = @IdCli
IF @@error <> 0 GOTO LBL_ERROR
FETCH CurIns INTO @IdCli, @TelAvant
END

-- fermeture du curseur et désallocation de l'espace mémoire
CLOSE CurIns
DEALLOCATE CurIns

RETURN

-- rollback en cas d'erreur
LBL_ERROR:
ROLLBACK TRANSACTION

4 - il s'agit maintenant de supprimer en cascade dans différentes tables. Si un client (table T_CLIENT) est supprimé on doit lui retirer les factures (table T_FACTURE) qui le concerne :

CORRECTION

CREATE TRIGGER E_DEL_CLI ON T_CLIENT
FOR DELETE
AS

DELETE FROM T_FACTURE
FROM T_FACTURE F
INNER JOIN DELETED D
ON F.CLI_ID = D.CLI_ID
IF @@ERROR <> 0
ROLLBACK TRANSACTION
Bien entendu si vous avez placé de nouveau un trigger permettant de faire de la suppression dans les lignes de facture, alors il sera déclenché et supprimera les occurrences désirées. C'est ce que l'on appelle un déclenchement de triggers en cascade.

5- - la gestion d'un lien d'héritage suppose souvent une exclusion mutuelle entre les fils nous allons voir comment gérer ce cas de figure. Partons d'une table T_VEHICULE dont la spécialisation provoque deux tables : T_AVION et T_BATEAU. Un véhicule peut être un avion ou bien un bateau mais pas les deux. Une valeur de clef présente dans T_VEHICULE peut donc se retrouver soit dans T_BATEAU soit dans T_AVION mais on doit éviter qu'elle se retrouve dans les deux tables.

CORRECTION


CREATE TRIGGER E_AVI_INS ON T_AVION
FOR INSERT
AS
DECLARE @rowInUse int, @rows int

-- on regarde si les clefs existent bien dans la table T_VEHICULE
SELECT @RowInUse = COUNT(*)
FROM INSERTED
SELECT @Rows = COUNT(*)
FROM T_VEHICULE V
JOIN INSERTED I
ON V.VHC_ID = I.VHC_ID
IF @RowInUse <> @Rows
BEGIN
ROLLBACK
RAISERROR ('Identifiant de l''héritant inexistant',16,1)
RETURN
END

-- on regarde si les clefs n'existent pas dans la table T_BATEAU
SELECT @Rows = COUNT(*)
FROM T_BATEAU B
JOIN INSERTED I
ON B.VHC_ID = I.VHC_ID
IF @Rows <> 0
BEGIN
ROLLBACK
RAISERROR ('Fils pré existant dans l''entité soeur BATEAU',16,1)
END
Jeu de test :

CREATE TABLE T_VEHICULE
(VHC_ID INT)

CREATE TABLE T_AVION
(VHC_ID INT,
AVI_MARQUE VARCHAR(16),
AVI_MODELE VARCHAR(16))

CREATE TABLE T_BATEAU
(VHC_ID INT,
BTO_NOM VARCHAR(16),
BTO_PORT VARCHAR(16))

INSERT INTO T_VEHICULE VALUES (1)
INSERT INTO T_VEHICULE VALUES (2)
INSERT INTO T_VEHICULE VALUES (3)

INSERT INTO T_BATEAU VALUES (2, 'Penduick', 'Lorient')
INSERT INTO T_BATEAU VALUES (3, 'Titanic', 'Liverpool')
INSERT INTO T_AVION VALUES (1, 'Boeing', '747')

INSERT INTO T_AVION VALUES (3, 'Tupolev', '144')
INSERT INTO T_AVION VALUES (5, 'Airbus', 'A320')
Les deux dernières insertions doivent être rejetées : l'id 3 existant dans l'entité frère T_BATEAU et l'id 5 n'existant pas dans l'entité mère.

Mais cet exemple est incomplet car il faudrait créer ce même type de trigger dans la table T_BATEAU pour vérifier la présence de la clef dans la table père et vérifier son absence dans la table sœur. De même qu'il serait souhaitable de gérer une suppression en cascade pour le père et éventuellement une modification de la valeur de la clef en cascade ! Bref, à vous de jouer...

6- - voici maintenant une association d'un genre particulier. L'association 0:0 ! Comment gérer une telle relation ? Comme à mon habitude un exemple concret est plus compréhensible : nous voici avec un texte à indexer mot pour mot, et pour cela nous devons classer chaque mot rencontré dans le texte dans une table T_MOT (MOT_MOT, MOT_REF, MOT_PAGE, MOT_LIGNE, MOT_OFFSET) avec la référence du texte, la page, la ligne et l'offset en nombre de caractère. Mais il serait absurde d'indexer tous les mots. C'est pourquoi une table T_MOT_NOIR(MNR_MOT) de mot indésirables (les mots "noirs") est créée, et l'on souhaite qu'aucun des mots indexé pour le texte ne soit un mot noir, ni qu'aucun mot noir ne se trouve dans les mots indexé. C'est donc bien une relation d'exclusion totale, telle que l'intersection des colonnes MOT_MOT de T_MOT et MNR_MOT de T_MOT_NOIR produise un ensemble vide, ou plus simplement que :

NOT EXISTS(SELECT *
FROM T_MOT MOT
JOIN T_MOT_NOIR MNR
ON MOT.MOT_MOT = MNR.MNR_MOT)
Soit toujours évaluée à vrai !

Un tel trigger n'est pas difficile à écrire :

CORRECTION


CREATE TRIGGER E_INS_MOT ON T_MOT
FOR INSERT
AS
IF EXISTS(SELECT *
FROM INSERTED I
JOIN T_MOT_NOIR M
ON I.MOT_MOT = M.MNR_MOT)
BEGIN
ROLLBACK
RAISERROR ('Insertion d''un mot noir impossible',16,1)
RETURN
END
Il faudrait d'ailleurs penser à écrire son réciproque dans la table T_MOT_NOIR empêchant ainsi l'insertion d'un mot noir pré existant dans la table T_MOT.

On peut bien entendu tester un tel trigger avec le jeu d'essai suivant :

CREATE TABLE T_MOT
(MOT_MOT CHAR(32),
MOT_REF CHAR(,
MOT_PAGE INT,
MOT_LIGNE INT,
MOT_OFFSET INT)

CREATE TABLE T_MOT_NOIR
(MNR_MOT CHAR(32))

INSERT INTO T_MOT_NOIR VALUES ('LE')
INSERT INTO T_MOT_NOIR VALUES ('LA')
INSERT INTO T_MOT_NOIR VALUES ('LES')
INSERT INTO T_MOT_NOIR VALUES ('UN')
INSERT INTO T_MOT_NOIR VALUES ('UNE')
INSERT INTO T_MOT_NOIR VALUES ('DES')
INSERT INTO T_MOT_NOIR VALUES ('DE')

INSERT INTO T_MOT VALUES('LA', 'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('VALLÉE', 'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('DE', 'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('LA', 'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('MORT', 'BIBLE', 147, 23, 14)
En conclusion nous pouvons dire que les triggers de la version 7 de SQL Server sont assez limités en ne permettent pas de gérer très finement les données. Ils ne fournissent pas un mécanisme pratique et simple lorsque l'on veut par exemple manipuler ligne à ligne et colonne par colonne la vailidité des données et les rectifier à la volée avant l'insertion définitive. Il semble que la version 2000 de SQL Server respecte plus la norme SQL 2 sur ce point.

7-- dans une relation d'héritage, comment insérer dans une table fille alors que l'insertion dans la table mère est un pré requis ?
Par exemple, nous avons une table des personnes, une table des clients et une table des employés. Ces tables sont construites de la sorte :

CREATE TABLE T_PERSONNE_PRS(PRS_ID INT IDENTITY NOT NULL PRIMARY KEY, PRS_NOM CHAR(32) NOT NULL, PRS_PRENOM VARCHAR(16))
CREATE TABLE T_EMPLOYE_EMP(PRS_ID INT NOT NULL PRIMARY KEY REFERENCES T_PERSONNE_PRS (PRS_ID), EMP_MATRICULE VARCHAR()
On ne peut donc pas insérer directement dans T_EMPLOYE_EMP, sauf à utiliser une vue et un trigger INSTEAD OF...

CORRECTION


Creation de la vue V_EMPLOYEE_EMP :

CREATE VIEW V_EMPLOYEE_EMP
AS
SELECT P.PRS_ID, P.PRS_NOM, P.PRS_PRENOM, E.EMP_MATRICULE
FROM T_PERSONNE_PRS P
INNER JOIN T_EMPLOYE_EMP E
ON P.PRS_ID = E.PRS_ID
Dès lors on peut créer un trigger d'insertion dans cette vue qui va décomposer les éléments à insérer et injecter les données dans les deux tables :

CREATE TRIGGER TRG_INS_EMPLOYE
ON V_EMPLOYEE_EMP
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM)
SELECT PRS_NOM, PRS_PRENOM
FROM INSERTED

INSERT INTO T_EMPLOYE_EMP (PRS_ID, EMP_MATRICULE)
SELECT @@IDENTITY, EMP_MATRICULE
FROM INSERTED
END
Utilisation :

INSERT INTO V_EMPLOYEE_EMP VALUES (1, 'DUPONT', 'Maurice', 'XF5090AZ')
SELECT * FROM T_PERSONNE_PRS
PRS_ID PRS_NOM PRS_PRENOM ----------- -------------------------------- ----------------1 DUPONT Maurice
SELECT * FROM T_EMPLOYE_EMP
PRS_ID EMP_MATRICULE----------- -------------1 XF5090AZ
NOTA : voici le trigger de contrôle d'intégrité des bornes des arborescence exprimées sous forme intervallaire

CREATE TRIGGER E_DEV_UNIQUE_BORNE ON T_DEVELOPPEMENT_DEV
FOR INSERT, UPDATE, DELETE
AS

-- vérification de l'unicité de l'ensemble des bornes (bornes gauches et bornes droite)
IF EXISTS (SELECT COUNT(*), BORNE
FROM (SELECT DEV_BORNE_DROITE AS BORNE
FROM T_DEVELOPPEMENT_DEV
UNION ALL
SELECT DEV_BORNE_GAUCHE AS BORNE
FROM T_DEVELOPPEMENT_DEV) T
GROUP BY BORNE
HAVING COUNT(*) <> 1)
ROLLBACK

-- vérification de la borne maximale comme étant deux fois le nombre de lignes de la table
IF (SELECT MAX(BORNE)
FROM (SELECT DEV_BORNE_DROITE AS BORNE
FROM T_DEVELOPPEMENT_DEV
UNION ALL
SELECT DEV_BORNE_GAUCHE AS BORNE
FROM T_DEVELOPPEMENT_DEV) T) <> (SELECT COUNT(*) * 2
FROM T_DEVELOPPEMENT_DEV)
BEGIN
ROLLBACK
RAISERROR ('Une borne dépasse la valeur maximale attendue', 16, 1)
END

-- vérification de la borne minimale comme étant égale à un
IF (SELECT MIN(BORNE)
FROM (SELECT DEV_BORNE_DROITE AS BORNE
FROM T_DEVELOPPEMENT_DEV
UNION ALL
SELECT DEV_BORNE_GAUCHE AS BORNE
FROM T_DEVELOPPEMENT_DEV) T) <> 1
BEGIN
ROLLBACK
RAISERROR ('Une borne dépasse la valeur minimale attendue', 16, 1)
END

CONTRAINTES ET TRIGGERS
1. CONTRAINTES
1.1 Rappels

Les contraintes d’intégrité permettent à l’utilisateur de définir des règles que doivent respecter lesdonnées de la base. La plupart d’entre elles sont définies lors de la création des tables (CREATETABLE) .
contrainte de domaine (valeur possible d’un attribut, éventuellement à l’aide d’un valeur par défaut), contrainte de clé (PRIMARY KEY et UNIQUE),
contrainte d’intégrité référentielle (FOREIGN KEY), contrainte de tuple (clause CHECK, condition liant les valeurs des différents attributs pour tous les n-uplets de la table, ne peut pas contenir de sous requête).

1. Exercices
La base de données d’une entreprise contient entre autres les relations suivantes :

EMPLOYE (ID_EMP, NOM, PRENOM, FONCTION, ID_CHEF, DATE_EMBAUCHE,
SALAIRE, PRIME, ID_PROJET)

DIRECTION (ID_DIR, NOM, ID_RESP, VILLE)

PROJET (ID_PROJ, NOM, ID_CHEF_PROJET)

Les attributs « id_ » sont les clés primaires des relations correspondantes.
1.2.1 Ecrire les commandes de création des trois tables en SQL sachant que :
♦ Les identificateurs sont des entiers de petite taille.
♦ Les noms sont tous des chaînes de 20 caractères
♦ La fonction d’un employé est une chaîne de 7 caractères, une ville une chaîne de 9.
♦ Salaire et prime sont en euro et centime d’euro, aucun salaire ne dépassant 40 000 euros.

1.2.2 On rajoute les contraintes suivantes :

3. Nom, prénom, salaire, prime doivent être connus

4. Les fonctions possibles sont répertoriées (‘Vente’, ‘Gestion’, ‘Comptab’) et sont utilisées
Dans d’autres tables de la base.
5. Par défaut, un nouvel employé est inséré dans le base le jour de son embauche.
6. Les villes possibles sont ‘Paris’ (par défaut), ‘Zurich’, ‘Istanbul’
7. Il n’existe pas deux personnes ayant même nom et même prénom
8. Le salaire minimum est de 1000 euros
9. L’ensemble salaire et prime ne doit pas dépasser 40 000 euros pour un employé
10. Le chef d’un employé, le chef d’un projet et le responsable d’une direction sont tous des
Employés. Une direction peut ne pas avoir de responsable.
11. Le projet d’un employé est un projet existant. Un employé peut ne pas être affecté à un
projet .
12. Un employé a toujours un chef éventuellement remplaçable.
13. Un projet a forcément un chef de projet, irremplaçable (s’il s’en va le projet s’arrête).
14. Le budget global annuel de l’entreprise pour son personnel ne doit pas dépasser 10000000
Euros.
Indiquez de quel type est chacune de ces contraintes. Exprimer, quand c’est possible, les
contraintes 5 à 12 en logique du premier ordre.

1.2.3 Reprendre la création du schéma en SQL afin d’intégrer les contrainte de la question
précédente.

1.2.4 Comment modifier le schéma si, au bout d’un an d’utilisation de la base, on décide qu’il ne
peut y avoir de direction sans responsable ? La commande sera-t-elle acceptée ?

2. TRIGGERS
Exercices
1 Ecrire un trigger INSTED OF qui évite qu’un salaire ne puisse diminuer.
2 Idem avec un trigger AFTER.
3 Ecrire un trigger qui empêche qu’on supprime plus de 50 n-uplets à la fois dans la relation
EMPLOYE
4 Ecrire deux triggers AFTER implantant la contrainte d’intégrité référentielle liée au projet
D’un employé (contrainte n°9 de la question 1.2.2.) :

CONTRAINTES D’INTEGRITE – TRIGGERS
1. CONTRAINTES D’ATTRIBUTS, CONTRAINTES DE TUPLES D’UNE TABLE
1.1 Rappels

1.2. Exercices
On considère une base de données concernant les enseignements et les étudiants d’une Université. Elle comprend, notamment, les relations :

TD (DIPLOME, MATIERE, NoTD, JOUR, HEURE, SALLE, NoENSEIGNANT)

INSCRIPTION (NoETUDIANT, DIPLOME, MATIERE, NoTD, DATE-INSCRIPTION).

Un n-uplet de la relation TD rassemble les informations concernant un TD d’une matière donnée, pour un diplôme donné: par exemple, le TD n° 2 de BD de Licence. La matière et lediplôme définissent un module. Le numéro d’un TD est relatif à un module , c’est-à-dire à une matière d’un diplôme donné. L’attribut JOUR donne le jour de semaine où le TD a lieu, les TD fonctionnant au rythme d’une fois par semaine, pendant toute l’année universitaire. Les attributs SALLE et HEURE donnent le lieu et l’horaire de début du TD. L’enseignant qui assure le TD est représenté par un numéro ; la mise en correspondance de ce numéro avec le nom de l’enseignant est assuré dans une autre relation qu’on ne considérera pas ici.
Les étudiants s’inscrivent à chaque module, séparément, et choisissent, à ce moment-là, un TD ( et un seul) pour ce module. Un n-uplets de la relation INSCRIPTION concerne donc l’inscription d’un étudiant donné à un module donné. Le numéro de l’étudiant est mis en correspondance avec son nom, et les autres informations utiles le concernant, dans une autre table qu’on ne considérera pas ici.

1.2.1 Créer les tables TD et INSCRIPTION, avec les contraintes d’intégrité suivantes :
- contraintes de clés (clé primaire et autres clés candidates) ;
- contrainte référentielle ;
- contrainte de domaine (un TD a lieu un jour de semaine, allant du lundi au samedi).
1.2.2 Insérer dans la table TD :
- un n-uplets (t1) correspondant à un TD d’un certain module (par exemple, BD de Licence) .
- puis, en prévision de l’exercice 2.2.2, deux n-uplets (t2, t3) correspondant tous deux au même module (par exemple, ALGO de Licence), ce module étant différent du module de t1 ;
t2 et t1 auront lieu à des moments différents, et pourront donc être choisis par un même étudiant ; par contre, t3 et t1 se passeront au même moment.
1.2.3 Tenter d’insérer de nouveaux n-uplets contredisant les règles d'intégrité de la table TD.
1.2.4 Insérer un n-uplets dans la table INSCRIPTION, puis tenter de faire des insertions ou des modifications contraires aux règles d’intégrité de cette seconde table.

2. TRIGGERS
2.1. Rappels

2.2. Exercices
2.2.1 Créer un trigger qui assure que les valeurs des attributs DIPLOME et MATIERE, entrées dans la table INSCRIPTION, soient en majuscules, quelle que soit la casse utilisée dans les instructions d’insertion ou de mise à jour.

2.2.2 (facultatif)
Pour empêcher un étudiant de s’inscrire à des TD incompatibles (i.e. ayant lieu en même
temps), on introduit une nouvelle contrainte d’intégrité, dans la base : un étudiant ne peut pas
avoir deux inscriptions qui lui imposent d’être, au même moment, à deux endroits différents.
Ecrire un trigger qui assure cette contrainte, de la façon suivante :
- en préambule à chaque inscription, le trigger inscrit le numéro de l’étudiant, avec le jour,
l’heure et la salle du TD qu’il a choisi, dans une table supplémentaire créée au préalable avec
1. ces quatre colonnes
2. une contrainte d’intégrité qui assure l’unicité du triplet (N°ETUDIANT, JOUR, HEURE) ;
- si l’étudiant avait déjà pris une inscription correspondant au même jour et à la même heure,
la contrainte de table précédente provoque le rejet du nouveau quadruplet (N°ETUDIANT,
JOUR, HEURE, SALLE), le trigger est arrêté sur erreur, et l’instruction d’insertion d’un nouvel élément dans la table INSCRIPTION est, par suite, arrêtée sur erreur.
Etude de cas avec corrigé
Rappel
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 Dupont leon
3 Durand Jean
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
Etude de cas " COMPTES 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 comptesLes opérations permettent de calculer le solde d'un compte, différence entre la somme desopé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éesBANQUE (Bid, Bnom) Bid : clé primaireCOMPTE (Cid, Cnumero, Clibelle, CsoldeReel, CsoldeBanque, Cbanque) Cid : clé primaire Cbanque : clé étrangère en référence à Bid de BANQUEOPERATION (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 COMPTERELEVE(Rid, Rdate, Rcompte) Rid : clé primaire Rcompte : clé étrangère en référence à Cid de COMPTERemarques :- 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 parameters : // n fois variables : // n fois begin // liste d'instructions end> Création d'un déclencheur create trigger on (insert | update | delete ) for variables : // n fois begin // liste d'instructions end> Syntaxe du langage de définition des procédures stockéesLe 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 SQLLe 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 triggerLors 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 SignificationINSERT NEW. Valeur de l'attribut indiquée dans l'instruction INSERTUPDATE OLD. Valeur de l'attribut avant l'instruction UPDATEUPDATE NEW. Nouvelle valeur (indiquée dans l'instruction UPDATE)DELETE OLD. 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

CORRECTION


Question 1 :
le schéma entité-association 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éelbegin if (sens='c') thenupdate compte set CsoldeBanque=CsoldeBanque+@montant where Cid=@idCompte else update compte set CsoldeBanque=CsoldeBanque-@montant where Cid=@idCompte endifendCREATE PROCEDURE MAJSOLDEREELPARAMETERS@idCompte entier@sens : caractère@montant : réelbegin if (@sens='c') then update compte set CsoldeReel=CsoldeReel+@montant where Cid=@idCompte else update compte set CsoldeReel=CsoldeReel-@montant where Cid=@idCompte endifendCREATE TRIGGER trigInsOperation ON INSERT FOR OPERATION VARIABLES@sens : caractèrebegin majSoldeReel(new.Ocompte, new.Osens, new.Omontant) if (new.Oreleve is not null) then majSoldeBanque(new.Ocompte, new.Osens, new.Omontant) endifendCREATE TRIGGER trigUpdateOperation ON UPDATE FOR OPERATION VARIABLES@sens : caractèrebegin // 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 montantmajSoldeReel(new.Ocompte, new.Osens, new.Omontant) if (new.Oreleve is not null) then majSoldeBanque(new.Ocompte, new.Osens, new.Omontant) endifendCREATE TRIGGER trigDelOperation ON DELETE FOR OPERATION VARIABLES@sens : caractèrebegin 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) endifend
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.


EXERCICE
Ce petit exemple permet de voir le fonctionnement d'un trigger sur un insert dans la table invent.
Ce trigger se déclenche lorsque l'on insert un enregistrement dans la table invent. Il décrémente ainsi le stock de l'article si celui-ci est suivi en stock. On retrouve cette information dans la table article grâce au champ "ART_suivi". si ART_suivi = 0 pas de suivi de stock. Si ART_suivi = 1 => suivi de stock donc décrémentation du stock de cette article.


CORRECTION

SOURCE
1. create trigger Gestion_stock
2.
3. on invent
4.
5. FOR INSERT
6.
7. AS
8.
9. IF (select ART_suivi from Articles,inserted where Articles.ART_Ref = Inserted.ART_Ref) > 0
10.
11. BEGIN
12.
13. update S SET
14.
15. S.Stock_Art = (S.Stock_Art - I.Quantite)
16.
17. FROM Stock as S INNER JOIN Inserted as I
18.
19. ON S.ART_Ref = I.ART_Ref
20.
21. END

EXERCICE
Il s'agit de faire du delete on cascade ou plus exactement de supprimer des enregistrements presents dans une table (EX : Tcivilites)
qui est en relation avec une autre (EX : Tpersonnes) par une foreign key
place dans la table Tpersonnes.

Si je supprime une ligne de la table Tcivilites et que j'ai une reference qui pointe sur la table
Tpersonnes je vais bien sur avoir un message d'erreur du sgdbr !

Pour remedier a cela, utilisez le on delete cascade qui est bien pratique !
Donc si je supprime une ligne de la table Tcivilites et que je retrouve
sa reference dans la table Tpersonnes il me supprimera toutes les lignes correspondantes !

CORRECTION

SOURCE

/** Les tables pour l'exemple **/
1. create table Tcivilites
2. (
3. ID int not null primary key identity,
4.
5. sexe varchar(20)
6. )
7. GO
8. create table Tpersonnes
9. (
10. ID int not null primary key identity,
11. nom varchar(50),
12.
13. prenom varchar(50),
14.
15. fkcivilite int foreign key references Tcivilites(ID) on delete cascade
16. )
17. Go
18. /** Quelques donnees pour la table Tcivilites **/
19.
20. insert into Tcivilites(sexe) values('Madame')
21.
22. insert into Tcivilites(sexe) values('Mademoiselle')
23.
24. insert into Tcivilites(sexe) values('Monsieur')
25.
26. /** Donnes pour la table Tcategories **/
27.
28. insert into Tpersonnes(nom, prenom, fkcivilite) values('BERNARD','Christophe',3)
29.
30. insert into Tpersonnes(nom, prenom, fkcivilite) values('Thunissen','Marc',2)
31.
32. insert into Tpersonnes(nom, prenom, fkcivilite) values('RIOLO','Francis',2)
33.
34. /** Supprimons une ligne de la table Tcivilites
35.
36. ** et nous verrons que les donnees contenues dans la table
37.
38. ** Tpersonnes seront bien supprimees aussi
39.
40. **/
41.
42. DELETE from Tcivilites where ID = primarykey_Tcivilite
43.
44.
EXERCICES


La base de données "uniPubs" est une traduction en français de la base Pubs fournie avec SQL Server.
En voici le schéma relationnel :


Exercice 1 : écrire un trigger qui générera le message ci-dessous quand un auteur sera inséré dans la table Auteur, par exemple :




Exercice 2
Ecrire un trigger qui affiche l'ancien n° de tél ainsi que le nouveau lorsqu'un update de la colonne est effectué sur un tuple de Auteur.
Lorsqu'une table est mise à jour (clause UPDATE …) deux tables virtuelles sont créées : une table contenant les valeurs avant la mise à jour, et une table contenant les valeurs après la mise à jour.


Exercice 3
Ecrire un trigger for ou after update sur la table Vente qui utilise la fonction columns_updated() et affiche à l'utilisateur le message suivant : "Attention : vous venez de modifier une valeur de clé étrangère pour la commande n° XXX"

Exercice 4 : générer une valeur à insérer
Ecrire un trigger qui (quelle que soit la valeur donnée comme identifiant de Emploi) insérera le n° immédiatement supérieur au plus grand n° présent dans la table.



Devra donner, par exemple, si le dernier emploi est Designer :
…
141 Designer 25 100
142 Contrôleur de travaux finis 100 200
143 Inspecteur de repos 20 200
144 Consultant en congés payés 10 150


Exercice 5 :
Les contraintes de domaine déclaratives (constraint check) ne permettent pas de se référer à d'autres colonnes que celles pour lesquelles la contrainte a été définie.
Une règle de l'entreprise UniPubs est que pour une catégorie d'emploi (idEmploi) donnée, le niveau d'emploi (sur lequel est basé le salaire) doit être compris entre un minimum (nivMin) et un maximum (nivMax). Ce niveau d'emploi est donné par posEmploi.

Créer un trigger for insert, update qui fait le contrôle de validité lorsque le niveau d'emploi (posEmploi) d'un employé est modifié ou inséré. S'il n'est pas compris entre les valeurs limites pour la catégorie, la transaction devra être annulée (rollback tran).


Exercice 6 :
La Direction souhaite gèrer l'historique des emplois exercés successivement par les employés (actuellement impossible car un employe n'a qu'un idEmploi).

1. Créer une table histoEmploi(idEmpl, dateEmpl, emploiAvant, emploiApres)
2. Créer un trigger qui sauvegarde l'emploi avant et après du salarié dans la table histoEmpl à chaque fois qu'on fait un update du champ idEmploi.






.

merci bien mon ami