Les déclencheurs sous SQL Server

Cours les déclencheurs sous SQL Server, tutoriel & guide de travaux pratiques en pdf.

Les curseurs

En programmation SQL (comme dans les déclencheurs et les procédures stockées), lorsqu’une requête retourne plusieurs lignes de résultat, il est possible de traiter chaque ligne au moyen d’un curseur.
Il s’agit donc d’un mécanisme qui va permettre de parcourir chaque enregistrement dans le résultat pour effectuer une opération précise.
L’implémentation des curseurs dépend fortement du SGBD employé. Ils sont disponibles sous Oracle et SQL Server.
Les curseurs sous SQL Server
Format :
DECLARE nom_curseur CURSOR
FOR
SELECT attr1, …, attrn
FROM table
[JOIN …]
[WHERE condition]
[GROUP BY …]
[HAVING …]
[ORDER BY …]
Le curseur est déclaré comme toutes variables avec le mot clé DECLARE. Le type de cette variable est CURSOR et ensuite, on trouve la requête. La requête peut contenir toutes les options que nous avons déjà vues.
Une fois le curseur déclaré, il est possible de l’utiliser. On distingue les étapes suivantes :
1. Ouverture du curseur au moyen de la commande SQL OPEN :
OPEN nom_curseur
2. Comme dans la lecture d’un fichier, on commence par lire le 1ier résultat :
FETCH nom_curseur INTO @Var1, …, @Varn
Il faut remarquer que tous les attributs présents dans la requêtes sont affectés à une variable via INTO.
3. Ensuite, la boucle de parcours est écrite et les instructions traitant le résultat sont contenues à l’intérieur.
WHILE @@fetch_status = 0
BEGIN
Traitement du résultat
4. Avant la fin de la boucle, il faut procéder à la lecture du résultat suivant :
FETCH nom_curseur INTO @Var1, …, @Varn END
5. Une fois la lecture terminée, on ferme le curseur
CLOSE nom_curseur
6. Si le curseur n’est plus utile, il convient de libérer la ressource :
DEALLOCATE nom_curseur
Exemple : Un exemple complet est montré dans la partie déclencheur.

Les déclencheurs

Pour gérer des contraintes complexes au niveau du SGBD, il est souvent nécessaire de recourir aux déclencheurs (ou triggers). Un déclencheur est une procédure programmée au niveau du SGBD qui s’exécute automatiquement lorsqu’un événement précis survient.
Les événements
Les événements déclencheurs sont l’ajout, la suppression ou la mise à jour d’un enregistrement dans la table ou même d’une colonne particulière. Les déclencheurs ne sont pas normalisés dans la norme SQL-2, il convient dès lors de se référer au dialecte de son SGBD (PL/SQL pour Oracle ou Transact-SQL pour SQL Server). Les événements déclencheurs sont généralement :
• BEFORE INSERT : exécution du code avant une insertion
• BEFORE UPDATE : exécution du code avant une modification
• BEFORE DELETE : exécution du code avant une suppression
• AFTER INSERT : exécution du code après une insertion
• AFTER UPDATE : exécution du code après une mise à jour
• AFTER DELETE : exécution du code après une suppression
Sous SQL Server, les triggers BEFORE ne sont pas disponibles.
Les événements BEFORE sont souvent utilisés pour vérifier une contrainte particulière et éventuellement arrêter l’insertion, la mise à jour ou la suppression si la contrainte n’est pas respectée.
Les événements AFTER sont souvent utilisés pour mettre à jour des données en fonction de la demande : mettre à jour un solde, un stock, … suite à l’ajout, la modification ou la suppression d’un enregistrement, par exemple.
Les déclencheurs sont également très souvent utilisés pour maintenir une certaine « dénormalisation » du schéma de la base de données. Ainsi, les attributs dérivables (i.e. résultat d’une opération entre des informations présentes dans la base de données comme le total d’une commande par exemple) peuvent, pour des questions de performances, être présents dans le schéma du SGBD. Afin d’assurer la cohérence des informations et être sûr que ces attributs dérivables sont toujours à jour, les déclencheurs sont alors très souvent utilisés.
Finalement, il est possible de restreindre l’exécution du déclencheur à l’ajout, la modification, ou la suppression d’un champ particulier dans la table.

Les déclencheurs sous SQL Server

Format :
CREATE TRIGGER nom_trigger ON nom_table
(FOR|AFTER|INSTEAD OF) (INSERT [,] | UPDATE [,] | DELETE [,])+
AS
[IF [NOT] UPDATE (attrx)
BEGIN
END]
[DECLARE @Var1 type, …, @Varn type]
La déclaration d’un déclencheur commence par les mots CREATE TRIGGER. Il faut ensuite spécifier le nom du déclencheur (nom_trigger) et la table sur laquelle il porte (nom_table). Il faut ensuite préciser l’événement déclencheur. Enfin, le code du déclencheur commence. Si on souhaite limiter un déclencheur à un champ particulier, il est nécessaire de spécifier l’option IF [NOT] UPDATE et l’attribut concerné. Cette option n’est possible que pour des déclencheurs INSERT et UPDATE. Exemple de syntaxe :
IF NOT UPDATE (attr)
RETURN
On trouve ensuite la déclaration des variables et le code du déclencheur lui-même.
Evénements déclencheurs
SQL Server permet les options suivantes :
• FOR ou FOR AFTER ou AFTER : Ces déclencheurs s’exécutent après que l’instruction SQL ait été complètement exécutée. Le déclencheur ne sera exécuté que si l’instruction SQL s’est passée correctement. En particulier, si toutes les contraintes programmées (contraintes d’intégrité, contrainte référentielle, clause CHECK) ont été vérifiées. FOR, FOR AFTER et AFTER sont des synonymes.
• INSTEAD OF : Il s’agit d’un type particulier de déclencheur. Ainsi au lieu d’exécuter la requête SQL qui a déclenché le trigger, seul le code du trigger est exécuté.
Les événements déclencheurs sont l’insertion, la mise à jour et la suppression.
Les types de déclencheur
SQL Serveur définit uniquement des déclencheurs de table. Ainsi, le déclencheur n’est exécuté qu’une seule fois même si la requête SQL qui a provoqué son exécution porte sur plusieurs lignes.
Pour travailler sur chaque ligne pointée par la requête SQL, il est nécessaire de définir un curseur sur les pseudo-tables inserted ou deleted.
Les anciennes et nouvelles valeurs
SQL Server permet d’avoir accès, lors de l’exécution du déclencheur, aux anciennes et nouvelles valeurs de la table. A cette fin, et suivant l’événement déclencheur, le programmeur peut faire appel aux pseudo-tables inserted et deleted.
 Arrêter l’exécution du déclencheur
On peut lire dans [3] : « ROLLBACK TRANSACTION behaves differently when used within a trigger than when not within a trigger. (…) When within a trigger, it is not necessary to have a matching BEGIN TRANSACTION statement because each SQL statement that is not within an explicit transaction is effectively a one-statement transaction. Nothing at the SQL batch or stored procedure can get « inside » such a one-statement transaction ; however, the Transact-SQL statement within a trigger is effectively « inside » the one-statement transaction, and therefore it does make sense to allow an unbalanced rollback to the beginning of the one-statement transaction. ».
On peut arrêter l’exécution du déclencheur et revenir au point juste avant l’exécution de la requête SQL ayant déclenché le trigger en utilisant l’option ROLLBACK TRANSACTION. Comme nous pouvons définir uniquement des déclencheurs AFTER, le ROLLBACK aura comme conséquence de « défaire » les modifications effectuées. De cette manière, il est possible de « simuler » un déclencheur de type BEFORE.
Les exceptions en SQL Server
Il est parfois intéressant de signaler une erreur. En effet, en levant une erreur, on peut mentionner, par un message, le type d’erreur rencontré. Pour ce faire, il faut utiliser l’instruction Transact-SQL RAISERROR dont le format est le suivant :
RAISERROR( message, severité, etat)
Le message est une chaîne de caractères libre mentionnant le type d’erreur qui est survenu.
La sévérité est un entier entre 0 et 18 qui représente la gravité de l’erreur.
L’état est un entier, compris entre 1 et 127, qui peut être utilisé pour repérer l’endroit où l’erreur s’est produite. Par exemple, si la même erreur peut survenir à plusieurs endroits du déclencheur, l’état permettra de désigner l’instruction RAISERROR qui a été exécutée.
Particularités de SQL Server
1. Il n’est pas possible de définir un déclencheur AFTER sur une vue. Seul un déclencheur INSTEAD OF est autorisé pour autant que l’option WITH CHECK OPTION n’ait pas été précisée lors de la création de la vue.
2. Il est interdit de modifier les tables inserted ou deleted.
3. Il est interdit d’utiliser des commandes comme CREATE INDEX, ALTER INDEX, DROP INDEX, DROP TABLE ou ALTER TABLE sur la table mentionnée dans le déclencheur. Les autres actions sont permises.
Exemple
CREATE TRIGGER maj_autom_cat_inf_client ON CLIENT
AFTER UPDATE
AS
IF NOT UPDATE(Compte)
RETURN
UPDATE Client
SET Cat=’B1’
WHERE Compte <-10000
AND Cat=’B2’
UPDATE Client
SET Cat=’C1’
WHERE Compte <-10000
AND Cat=’C2’
Met à jour les clients de sorte qu’un client dont le compte passe en dessous de -10000 change de catégorie. Si ce client était en catégorie B2, il passe en catégorie B1 tandis que s’il était en catégorie C2, il passe en catégorie C1.
CREATE TRIGGER maj_cat_client ON CLIENT
AFTER UPDATE
AS
IF NOT UPDATE(Compte)
RETURN
DECLARE @NumClient INTEGER
DECLARE @Cat CHAR(2)
DECLARE @Cpt NUMERIC(9,2)
DECLARE @OldCPT NUMERIC(9,2)
DECLARE curseur CURSOR
FOR
SELECT NumClient, Categorie, Compte
FROM inserted
OPEN curseur
FETCH curseur INTO @NumClient, @Cat, @Cpt
WHILE @@fetch_status = 0
BEGIN
SELECT @OldCPT = Compte
FROM deleted
WHERE NumClient = @NumClient
IF @Cat = ‘B1’
BEGIN
IF @OldCPT > 0 AND @Cpt < 0
BEGIN
RAISERROR(‘Un client B1 ne peut passer en negatif’, 7, 1)
ROLLBACK TRAN
END
END
FETCH curseur INTO @NumClient, @Cat, @Cpt
END
CLOSE curseur
DEALLOCATE curseur
Ce trigger illustre plusieurs choses : l’utilisation d’un curseur pour parcourir tous les éléments d’une table, l’utilisation des tables deleted et inserted contenant les données qui sont en cours de modification, l’utilisation d’un message d’erreur et l’annulation du traitement en cours grâce aux instructions RAISERROR et ROLLBACK TRAN.
Ce trigger assure, lors d’une mise à jour de la table client, que tous les clients modifiés (i.e. qui se trouvent dans inserted et deleted) dont la catégorie est B1 et ayant un compte positif, ne peuvent passer en négatif.

Les transactions
Un concept majeur des bases de données est la notion des transactions. Une transaction est un ensemble de commandes SQL qui respecte les propriétés suivantes (A-C-I-D) :
• Atomicité – Les commandes SQL faisant partie de la transaction sont exécutées complètement ou pas du tout
• Cohérence – Si l’état de la base de données était cohérent avant l’exécution de la transaction, il le sera après également.
• Isolation – Les transactions peuvent s’exécuter de manière concurrente. Le SGBD garantira que l’exécution d’une transaction sera sans effet sur l’exécution des autres transactions
• Durabilité – Les changements effectués au terme de la transaction sont permanents.
Grâce à ces principes, nous savons que la transaction est une opération atomique dont les effets seront permanents si celle-ci se déroule correctement. Pour ce faire, le SGBD va exécuter les commandes SQL de la transaction « de manière temporaire » jusqu’à ce qu’une commande COMMIT ou ROLLBACK soit rencontrée.
La commande COMMIT informe le SGBD qu’il peut sauvegarder les modifications effectuées par la transaction de manière durable. Les modifications sont alors permanentes et la transaction est terminée.
La commande ROLLBACK informe le SGBD qu’un problème est survenu durant le traitement de la transaction et qu’il faut défaire les commandes SQL pour revenir au point précédent l’exécution de la transaction.
Les transactions sont très largement utilisées dans la programmation afin d’obtenir des opérations atomiques. Par exemple : le virement d’argent d’un compte vers un autre est une transaction car :
• Soit l’échange se passe bien et le premier compte est débité d’une somme tandis que le second compte est crédité de cette même somme ;
• Soit l’échange ne se passe pas bien et aucun compte n’est crédité, ni débité.
Ainsi les transactions sont très utiles lorsqu’il faut réaliser plusieurs opérations en une seule fois. Il est bien évident qu’une transaction contenant une seule requête n’a aucun intérêt.
Attention ! Une transaction se doit d’être toujours la plus petite possible et être automatique. En effet, afin de gérer les transactions, le SGBD pose automatiquement des verrous sur des tables. Si la transaction est longue, les performances du SGBD peuvent se dégrader. Si la transaction n’est pas automatique (i.e. une fois lancée, elle attend des données de l’utilisateur par exemple), son temps d’exécution peut ici aussi être très important et donc dégrader fortement les performances du SGBD.
Les transactions sont surtout utilisées du coté applicatif (par exemple en Java, lors de la conception de couche d’accès BD) pour assurer la cohérence des mises à jour. Nous aborderons le coté applicatif lorsque nous détaillerons JDBC.