Module : Système de gestion de

base de données II
Filière : TDI2

Partie Déclencheurs [TRIGGER]

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.

1 Proposé par :A BENDAOUD

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é.

2 Proposé par :A BENDAOUD

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 Un par action de déclenchement
déclenchement (UPDATE, DELETE (UPDATE, DELETE et INSERT)
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 : Avant :
Traitement des contraintes Traitement des contraintes
Actions référentielles déclaratives Au lieu de :
création de tables inserted et deleted L'action de déclenchement
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 Non applicable
peuvent être spécifiées
Références de colonnes text, Non autorisé Autorisé
ntext et image dans des tables
inserted et deleted

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

3 Proposé par :A BENDAOUD

 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.

4 Proposé par :A BENDAOUD

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.

5 Proposé par :A BENDAOUD

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 [Link] as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE [Link] = [Link]

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),

6 Proposé par :A BENDAOUD

 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 [Link]
FROM Person P, inserted I
WHERE [Link] = [Link]))
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 [Link]
FROM EmployeeTable E, inserted
WHERE [Link] = [Link]))
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 = [Link],
Department = [Link],
Salary = [Link],
Comment = [Link]
FROM EmployeeTable E, inserted I
WHERE [Link] = [Link]
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.

7 Proposé par :A BENDAOUD

  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

8 Proposé par :A BENDAOUD

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 <nouveau>.

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.

9 Proposé par :A BENDAOUD

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

10 Proposé par :A BENDAOUD

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.

11 Proposé par :A BENDAOUD

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

12 Proposé par :A BENDAOUD

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.

13 Proposé par :A BENDAOUD

  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 [Link]

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.

14 Proposé par :A BENDAOUD

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

15 Proposé par :A BENDAOUD

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

16 Proposé par :A BENDAOUD

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

17 Proposé par :A BENDAOUD

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.

18 Proposé par :A BENDAOUD

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 :

19 Proposé par :A BENDAOUD

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 ;

20 Proposé par :A BENDAOUD

  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 = [Link]

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 = [Link] -- 1 for the new report.

UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's

21 Proposé par :A BENDAOUD

 FROM deleted -- (no. of reports) by 1
WHERE emp_mgr.emp = [Link] -- 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).

22 Proposé par :A BENDAOUD

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 ;

23 Proposé par :A BENDAOUD

  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

24 Proposé par :A BENDAOUD

INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring [Link] and
--[Link].
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 [Link] ou [Link], 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.

Valeur dans la table inserted

Colonne de table Valeur dans la table inserted d'un d'un déclencheur INSTEAD
de base déclencheur INSERT de table OF INSERT de vue

25 Proposé par :A BENDAOUD

Est une colonne Expression calculée Valeur définie par l'utilisateur
calculée. ou NULL
elle a une propriété 0 si IDENTITY_INSERT est Valeur définie par l'utilisateur
IDENTITY, désactivée (OFF), valeur spécifiée si ou NULL
IDENTITY_INSERT est activée
(ON)
elle a un type de Zéros binaires si la colonne Valeur définie par l'utilisateur
données n'accepte pas de valeur NULL, ou NULL
timestamp, NULL dans le cas contraire

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

26 Proposé par :A BENDAOUD

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

27 Proposé par :A BENDAOUD

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 [Link]

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 [Link]
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

28 Proposé par :A BENDAOUD

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,

29 Proposé par :A BENDAOUD

 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 [Link] ? 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
[Link], 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,

30 Proposé par :A BENDAOUD

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.
31 Proposé par :A BENDAOUD
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)

32 Proposé par :A BENDAOUD

--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.

33 Proposé par :A BENDAOUD

 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

34 Proposé par :A BENDAOUD
 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.

35 Proposé par :A BENDAOUD

 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',

36 Proposé par :A BENDAOUD

 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

37 Proposé par :A BENDAOUD

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

38 Proposé par :A BENDAOUD

 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 = '[Link].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

39 Proposé par :A BENDAOUD

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 [Link].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

40 Proposé par :A BENDAOUD

-- 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

41 Proposé par :A BENDAOUD

 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

42 Proposé par :A BENDAOUD

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

43 Proposé par :A BENDAOUD

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(8),
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

44 Proposé par :A BENDAOUD

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(8))
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

45 Proposé par :A BENDAOUD

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),

46 Proposé par :A BENDAOUD

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

47 Proposé par :A BENDAOUD

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.
48 Proposé par :A BENDAOUD
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 :

49 Proposé par :A BENDAOUD

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

50 Proposé par :A BENDAOUD

Contexte de travail
Vous participez à la réalisation d'un logiciel permettant de gérer les comptes bancaires
d'un particulier.

Le fonctionnement prévu est le suivant :

Dans un premier temps, l'utilisateur définit les différents comptes à gérer en déclarant
pour chacun le numéro de compte, le nom de la banque, ...

L'utilisateur saisit ensuite chacune des opérations réalisées sur l'un de ces comptes. Une
opération correspond à un débit (paiement par chèque de la facture de téléphone, retrait
d'espèces à l'aide d'une carte bancaire, ...) ou à un crédit (dépôt à la banque de chèques
reçus par exemple).

Chaque fin de mois, l'utilisateur reçoit un "relevé de compte" (un par compte bancaire).
Il effectue alors le rapprochement entre les opérations qu'il a saisies et celles constatées
par la banque :

- Pointage des opérations saisies apparaissant sur le relevé. Ce pointage consiste à

mémoriser le fait qu'une opération a été constatée par la banque. L'opération est dite
"pointée" et le numéro du relevé est mémorisé.

- Ajout des opérations non saisies apparaissant sur le relevé (réception d'un virement
correspondant au salaire mensuel par exemple). Ces opérations sont automatiquement
pointées puisqu'elles ont été constatées par la banque.

Solde des comptes

Les opérations permettent de calculer le solde d'un compte, différence entre la somme des

opérations de crédit et la somme des opérations de débit.

En fait, chaque compte possède deux soldes :

- Le solde réel qui prend en considération toutes les opérations saisies par l'utilisateur, qu'elles aient
été constatées ou non par la banque.

- Le solde en banque qui ne prend en considération que les opérations constatées par la banque,
c'est-à-dire ayant fait l'objet d'un "pointage" à partir d'un relevé.

L'application est développée autour d'un SGBDR. Au niveau physique, on trouve notamment deux
tables :

- COMPTE, table mémorisant les informations concernant les comptes courants.

- OPERATION, table mémorisant chaque opération concernant un compte courant.

Le calcul des soldes des comptes conduit à additionner un grand nombre d'opérations, et ce depuis la
mise en service de l'application. Pour éviter ces calculs, il a été décidé de mémoriser les soldes réel
et "en banque" dans la table COMPTE.

Dès lors se pose le problème de la cohérence entre les opérations et le solde des comptes. La
solution adoptée est le recours à un ensemble de procédures stockées et de déclencheurs (triggers)

51 Proposé par :A BENDAOUD

destinés à maintenir à jour les soldes dans la table COMPTE.

Schéma de la base de données

BANQUE (Bid, Bnom)

Bid : clé primaire

COMPTE (Cid, Cnumero, Clibelle, CsoldeReel, CsoldeBanque, Cbanque)

Cid : clé primaire

Cbanque : clé étrangère en référence à Bid de BANQUE

OPERATION (Oid, Odate, Omontant, Osens, Oreleve, Ocompte)

Oid : clé primaire

Oreleve : clé étrangère en référence à Rid de RELEVE

Ocompte : clé étrangère en référence à Cid de COMPTE

RELEVE(Rid, Rdate, Rcompte)

Rid : clé primaire

Rcompte : clé étrangère en référence à Cid de COMPTE

Remarques :

- CsoldeReel et CsoldeBanque représentent respectivement le "solde réel" et le "solde en banque" du

compte.

- Osens est un caractère mémorisant la nature de l'opération ('d' pour débit, opération diminuant le
solde du compte ; 'c' pour crédit, opération augmentant le solde du compte).

- Oreleve contient le numéro du relevé (Rid) ayant permis de pointer l'opération. Cet attribut
contient la valeur NULL si l'opération n'a pas encore été pointée.

Extrait de la documentation du SGBD utilisé

> Création d'une procédure stockée

create procedure <nom_procédure>

parameters

<nom_paramètre> : <type_paramètre> // n fois

variables

52 Proposé par :A BENDAOUD

 <nom_variable> : <type_variable> // n fois

begin

// liste d'instructions

end

> Création d'un déclencheur

create trigger <nom_trigger> on (insert | update | delete ) for <nom_table>

variables

<nom_variable> : <type_variable> // n fois

begin

// liste d'instructions

end

> Syntaxe du langage de définition des procédures stockées

Le langage permet l'utilisation de tous les types de données courants pour la déclaration des
paramètres et des variables. Les instructions "classiques" sont présentes : affectation, conditionnelle,
boucle, appel de procédure, ...

> Intégration de SQL

Le langage de définition des procédures stockées supporte l'utilisation de requêtes SQL selon la
norme d'intégration de SQL dans un langage hôte (variables hôtes notamment).

> Accès au n-uplet concerné par un trigger

Lors de l'écriture d'un trigger, il est possible d'accéder aux valeurs des attributs du

n-uplet concerné par l'opération. Le tableau ci-dessous résume les possibilités.

Opération Syntaxe Signification

INSERT NEW.<attribut> Valeur de l'attribut indiquée dans l'instruction INSERT

UPDATE OLD.<attribut> Valeur de l'attribut avant l'instruction UPDATE

UPDATE NEW.<attribut> Nouvelle valeur (indiquée dans l'instruction UPDATE)

DELETE OLD.<attribut> Valeur de l'attribut avant la suppression du n-uplet

Travail à Réaliser

1. Présentez le schéma entité-association correspondant au schéma de la base de données.

53 Proposé par :A BENDAOUD

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

54 Proposé par :A BENDAOUD

Remarque : la présence des propriétés calculées CsoldeReel et CsoldeBanque sur le
schéma est discutable, mais elles permettent une meilleure appréhension des réalités
manipulées, pour le développeur comme pour l'utilisateur.

Correction exercice " comptes bancaires : procédures stockées "Question 2 :

CREATE PROCEDURE MAJSOLDEBANQUE

PARAMETERS

@idCompte : entier

@sens : caractère

@montant : réel

begin

55 Proposé par :A BENDAOUD

if (sens='c') then

update compte set CsoldeBanque=CsoldeBanque+@montant

where Cid=@idCompte

else

update compte set CsoldeBanque=CsoldeBanque-@montant

where Cid=@idCompte

endif

end

CREATE PROCEDURE MAJSOLDEREEL

PARAMETERS

@idCompte entier

@sens : caractère

@montant : réel

begin

if (@sens='c') then

update compte set CsoldeReel=CsoldeReel+@montant where

Cid=@idCompte

else

update compte set CsoldeReel=CsoldeReel-@montant where

Cid=@idCompte

endif

end

CREATE TRIGGER trigInsOperation ON INSERT FOR OPERATION

VARIABLES

@sens : caractère

begin

majSoldeReel([Link], [Link], [Link])

56 Proposé par :A BENDAOUD

 if ([Link] is not null) then

majSoldeBanque([Link], [Link], [Link])

endif

end

CREATE TRIGGER trigUpdateOperation ON UPDATE FOR OPERATION

VARIABLES

@sens : caractère

begin

// Annulation du montant originel de l'opération en affectant

// ce même montant en sens inverse

if ([Link]='c') then @sens='d' else @sens='c' endif

majSoldeReel([Link], @sens, [Link])

if ([Link] is not null) then

majSoldeBanque([Link], @sens, [Link])

endif

// MAJ du compte par le nouveau montant

majSoldeReel([Link], [Link], [Link])

if ([Link] is not null) then

majSoldeBanque([Link], [Link], [Link])

endif

end

CREATE TRIGGER trigDelOperation ON DELETE FOR OPERATION

VARIABLES

@sens : caractère

begin

if ([Link]='c') then @sens='d' else @sens='c' endif

57 Proposé par :A BENDAOUD

 majSoldeReel([Link], @sens, [Link])

if ([Link] is not null) then

majSoldeBanque([Link], @sens, [Link])

endif

end

Question 3 :

Le fait de maintenir ces informations calculées permettra d'obtenir de meilleurs temps de

réponse lors de la consultation des comptes.

La centralisation du calcul au niveau du SGBD permet d'éviter de nombreuses erreurs de

codage dans les différents écrans ou états utilisant ces informations. De la même
manière, la maintenabilité de l'application se trouve améliorée.

Dans le cas d'une utilisation de la base de données dans une architecture client/serveur
(ce qui est assez improbable dans le contexte décrit), la solution des procédures stockées
permet également de minimiser le trafic sur le réseau et d'optimiser les temps
d'exécution.

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.

58 Proposé par :A BENDAOUD

11. BEGIN
12.
13. update S SET
14.
15. S.Stock_Art = (S.Stock_Art - [Link])
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,

59 Proposé par :A BENDAOUD

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 :

60 Proposé par :A BENDAOUD

Exercice 1 : écrire un trigger <pourInsAuteur> 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 <tel> est effectué sur un tuple de Auteur.
Lorsqu'une table est mise à jour (clause UPDATE …) deux tables virtuelles sont
créées : une table <Deleted> contenant les valeurs avant la mise à jour, et une table
<Inserted> contenant les valeurs après la mise à jour.

Exercice 3

61 Proposé par :A BENDAOUD

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.

62 Proposé par :A BENDAOUD