1.
Programmation événementielle
La première chose a savoir est que pour chaque table il existe en SQL trois événements (ni plus ni
moins). Ils sont soulevés respectivement par les instructions INSERT, DELETE et UPDATE .
L’objet de cette section est d’apprendre à les utiliser.
1.1. Mise- a-jour et suppression en cascade
Exemple : si on veut désormais que la suppression d’un client entraîne automatiquement celle de
ses commandes, il suffit pour cela de préciser une option lors de la définition de la contrainte clé
étrangère dans la table commandes.
1 ALTER TABLE commandes
2 ADD CONSTRAINT fk_cmd_clt
3 FOREIGN KEY (cmd_clt) REFERENCES clients
4 ON DELETE CASCADE
5 ON UPDATE CASCADE
Remarque :
– de cette façon, la relation entre les deux tables devient non bloquante en suppression et en mise-
a jour
– il n’y a pas ON INSERT CASCADE.
Exercice : pourquoi n’y a-t-il pas d’insertion en cascade ?
1.2. Déclencheurs AFTER
Un déclencheur est une procédure attachée à un événement, en anglais on dit TRIGGER. Ces
procédures
se déclenchent automatiquement après que l’événement concerne a et soulève (donc bien
souvent a l’insu de l’utilisateur) et ne peuvent être appelées directement.
Exemple : la table articles contient une colonne qui précise le nombre d’articles en commande ;
pour mettre a jour cette colonne lors d’insertion de nouvelles commandes on crée un déclencheur.
1 CREATE TRIGGER commandes_insert -- le nom du declencheur
2 ON commandes AFTER INSERT -- la table et l’evenement concernes
3 AS -- la programmation du declencheur
4 UPDATE articles SET nb_commande = nb_commande + cmd_qte
5 FROM articles AS a
6 JOIN inserted AS b ON (a.art_num = b.cmd_art)
7
8-- (si plusieurs instructions : utiliser un bloc BEGIN ... END)
Quelques mots sur les tables inserted et deleted :
– il s’agit de tables temporaires créées et disponibles pendant l’événement ;
– leurs colonnes sont identiques a celles de la table sur laquelle l’événement a et élevé ;
– le déclencheur AFTER INSERT peut utiliser la table inserted qui contient toutes les lignes
insérées ;
– le déclencheur AFTER DELETE peut utiliser la table deleted qui contient toutes les lignes
supprimées ;
– le déclencheur AFTER UPDATE peut utiliser les deux tables (ce qui est logique puisqu’une mise- a-
jour consiste en une insertion et une suppression).
Autre exemple avec cette fois-ci la table deleted :
1 CREATE TRIGGER commandes_delete
2 ON commandes AFTER DELETE
3 AS
4 UPDATE articles SET nb_commande = nb_commande - cmd_qte
5 FROM articles AS a
6 JOIN deleted AS b ON (a.art_num = b.cmd_art)
Troisième exemple, sur mise- a-jour cette fois-ci : pour être tout a fait complet, il faut également
un déclencheur qui réagisse si la colonne cmd qte est touchée par une mise- a-jour.
1 CREATE TRIGGER commandes_update
2 ON commandes AFTER UPDATE
1
3 AS
4 IF UPDATE(cmd_qte) -- si la colonne cmd_qte est touchee par la modification
5 BEGIN
6 UPDATE articles SET nb_commande = nb_commande - b.cmd_qte + c.cmd_qte
7 FROM articles AS a
8 JOIN deleted AS b ON (a.art_num = b.cmd_art)
9 JOIN inserted AS c ON (a.art_num = c.cmd_art)
10 END
Dernier exemple : on veut empˆecher la modification du num ero ISBN d’un ouvrage.
1 CREATE TRIGGER ouvrages_update
2 ON ouvrages AFTER UPDATE
3 AS
4 IF UPDATE(isbn)
5 BEGIN
6 RAISERROR (’Le numéro ISBN ne peut pas être modifie’, 0, 1)
7 -- 0 indique la gravite de l’erreur et 1 l’état (a oublier)
8 ROLLBACK TRANSACTION
9 -- on annule la transaction qui a déclenche l’événement
10 END
Remarque :
– les déclencheurs sont des transactions ;
– il faut que l’utilisateur qui tente d’insérer un emprunt, dispose des droits sur toutes les tables
impliquées dans la programmation du déclencheur ;
– comme on vient de le voir, les déclencheurs sont notamment utiles pour :
– implémenter des règles trop complexes pour les contraintes (ne serait que parce qu’une
contrainte ne peut porter que sur une table) ;
– afficher un message d’erreur personnalise et annuler la transaction appelante.
– comme leur nom l’indique, un déclencheur AFTER se produisent après un événement ;
– du coup, les contraintes sont vérifiées avant le lancement des déclencheurs AFTER, ce qui a pour
une conséquence fâcheuse : les mises- a-jour en cascade éventuellement soulevées par ces
déclencheurs ne se font qu’après vérification des contraintes ;
– avec SQL Server il n’y a pas de déclencheurs BEFORE ;
– par contre les déclencheurs INSTEAD OF (au lieu de) existent ; c’est l’objet du paragraphe
suivant.
Exercice : en quoi le cinquième point est-il fâcheux ?
1.3. Déclencheurs INSTEAD OF
On les utilise si on veut que leurs instructions se lancent a la place de l’insertion, de la suppression
ou de la mise- a-jour qui a soulève l’événement. Avec un déclencheur AFTER la modification des
données a lieu puis le déclencheur est exécute e, tandis qu’avec un déclencheur INSTEAD OF le
corps du déclencheur
se substitue a la modification des données.
D’un point de vue syntaxique, il suffit de remplacer AFTER par INSTEAD OF. Exemple : on historise
automatiquement les commandes insérées dans une table historique commmandes.
1 CREATE TRIGGER commandes_insert2
2 ON commandes INSTEAD OF INSERT
3 AS
4 BEGIN
5 INSERT commandes SELECT * FROM inserted
6 -- cette ligne fais l’insertion prevue
7 INSERT historique_commmandes SELECT * FROM inserted
8 END
9
10 -- on aurait donc pu se contenter d’un declencher AFTER
11 -- avec seulement le 2e INSERT
2
Remarques :
– les tables provisoires inserted et deleted existent et sont remplies pour les déclencheurs INSTEAD
OF (heureusement) ;
– les déclencheurs INSTEAD OF ne se déclenchent pas eux-mêmes (heureusement) ;
– il ne peut y avoir qu’un déclencheur INSTEAD OF par événement et par table (alors qu’il peut y
avoir plusieurs déclencheurs AFTER) ;
– s’il existe une clé étrangère avec une action en cascade (DELETE ou UPDATE) dans la table, alors
on ne peut pas écrire le déclencheur INSTEAD OF correspondant, et inversement.
Exercice : pourquoi ces trois dernières règles existent-elles ?
1.4. Compléments
Toutes les instructions SQL ne sont pas autorisées dans le code d’un déclencheur ; on se limitera
généralement a : INSERT, DELETE, UPDATE, RAISERROR et ROLLBACK TRANSACTION.
Pour modifier un déclencheur on a :
1 ALTER TRIGGER commandes_insert
2 ... -- son nouveau code
Pour supprimer un déclencheur on a :
1 DROP TRIGGER commandes_insert
Pour suspendre provisoirement un déclencheur (sans le supprimer) on a :
1 ALTER TABLE commandes
2 DISABLE TRIGGER commandes_insert
3
4 ... -- d’autres instructions puis
5
6 ALTER TABLE commandes ENABLE TRIGGER commandes_insert
Remarque : on peut remplacer commandes insert par ALL ou commandes insert, commandes
insert2.
On peut créer un déclencheur pour deux ou trois événements a la fois. Exemple :
1 CREATE TRIGGER ...
2 ON ... AFTER INSERT, UPDATE
3 AS
4 ...
1.5. Conclusion
Faisons une synthèse sur le déroulement d’une transaction. Pour chaque instruction de la
transaction on a : verification des autorisations de l’utilisateur (*) puis transfert des données
nécessaires du disque dans la mémoire puis remplissage des tables inserted et/ou deleted puis
modifications (prévues ou INSTEAD OF et/ou en cascade) des données dans la mémoire (*) puis
vérification des contraintes (*) puis déclencheurs AFTER (*) (*) signifie qu’a ce stade la transaction
peut-être annulée.
L’écriture des données sur le disque n’intervient qu’à la fin de la transaction lorsque toutes ses
instructions ont et revalidées.
