Dans ce cours , on va découvrir un des mécanismes les plus importants et les plus utilisés dans une base de données sqlserver : les déclencheurs en Tsql (aussi utilisés dans le langage Plsql avec le même nom).
Déclencheurs en Tsql : Définition
On représente un déclencheur comme une Forme évoluée de règles utilisées pour renforcer l’intégrité de la base de données. Autrement dit, les déclencheurs sont des contraintes d’intégrité personnalisée. Aussi, on peut dire qu’un déclencheur est un type particulier de procédure stockée. En effet, les triggers sont attachés à des tables, Ils ne peuvent pas être appelés explicitement dans les applications (alors que les procédures stockées normales peuvent être appelés explicitement dans les applications). En fait, les triggers sont déclenchés automatiquement par le noyau SQL à chaque événement (insert, update ou delete) sur la table qui les supportent.
Deux types de déclencheurs tsql existent : les déclencheurs for (ou after) et ceux appelés déclencheurs instead of. Alors, on doit comprendre la spécificité de chacun des deux types et les cas d’utilisation de l’un ou l’autre type.
1- Les déclencheurs for ou after :
syntaxe :
CREATE TRIGGER nom_trigger ON nom_table FOR INSERT[,update][,delete]
AS
bloc d’instruction SQL
Principe de fonctionnement.
Deux tables virtuelles (variable local temporaire dans le programme) sont créées au moment de la mise à jour des données sur une table : INSERTED, DELETED. Premièrement, ces deux tables sont en lecture seule. Aussi, elles sont destinées à contenir les lignes de la table sur lesquelles ont été effectuées des opérations.
Donc, les tables INSERTED et DELETED peuvent être utilisées par le déclencheur pour déterminer comment le traitement doit se dérouler. Ce traitement est à écrire par le développeur.
- Suppression d’une ligne de table (Delete) : La/les lignes supprimées sont placées dans la table temporaire DELETED et supprimées de la table réelle;
- Création d’une ligne de table (Insert) : La ou les nouvelles lignes sont placées dans la table temporaire INSERTED et dans la table réelle;
- Modification d’une ligne de table (Update) : La ligne (les lignes) avant modification sont placées dans la table temporaire DELETED et la/les lignes après modification sont placées dans la table temporaire INSERTED et dans la table réelle.
Exemple: on suppose qu’on a dans notre base de données, la table notation (idn,#idModule,#idStagiaire, note). On veut limiter le nombre de note par stagiaire par Module à 3 .Donc, créer un trigger qui permet de gérer cette contrainte
Solution :alter TRIGGER LimitNote ON notation
FOR INSERT
AS
begin
if (select count(*)
from notation n,inserted i
where n.idstagiaire=i.idstagiaire and n.idmodule=i.idmodule)>3
begin
RAISERROR(‘nombre limite 3 notes par matièer insertion annuler’,15,1)
rollback /*sinon le message s’affiche et l’ajout s’effectue*/
end
end
insert into notation values (1,1,12),(1,1,2),(1,1,1,0),(1,1,1,5);
select * from notation
Exercice :
Créer deux tables de même schéma stagiaire et archive .Puis, créer un trigger qui permet de sauvegarder, dans la table archive , chaque staigiaire supprimé
Allons-y, proposer une solution pour l’exercice et ajouter là dans la partie commentaires.
2.Déclencheurs LMD de type INSTEAD OF (au lieu de) :
Les déclencheurs INSTEAD OF sont exécutés à la place de l’action de déclenchement (UPDATE, DELETE et INSERT). Ainsi, Ils sont exécutés avant toute contrainte, ce qui leur permet d’effectuer un prétraitement qui complète les actions des contraintes.
Remarque : Chaque table ou vue peut disposer d’un seul déclencheur INSTEAD OF par action de déclenchement.
Exemple : écrire un trigger qui permet d’afficher ‘non’ après la demande de suppression d’une ligne de la table notation. De plus, la suppression ne doit pas s’effectuée.
Exercice (Instead of): supposons qu’on a deux tables module et notation. Si on veut supprimer des modules, on aura un problème lié à la contrainte d’intégrité référentiel.
Créer un trigger qui permet de supprimer les modules en supprimant les notes liés à ces modules en premier.
Solution :
CREATE TRIGGER Supp
ON Matière
INSTEAD OF DELETE
AS
begin
delete from Notation
Where Idm in (select idm from deleted)
delete from module
Where Idm in (select idm from deleted)
end
Remarque 1:
– Suppression d’un triggers:
DROP TRIGGER nom_trigger
– Modifier un déclencheur : la même syntaxe de Create, en remplaçant Create par Alter
– Désactiver un trigger :
alter table nomtable DISABLE TRIGGER nomTrigger
Remarque 2: La suppression d’une table entraîne la destruction de ses déclencheurs.
Remarque 3: Avec SQL 2005 et plus, en peut avoir des déclencheurs sur LDD(Langage de Définition de Donnée) comme (Create, Alter, Drop) et à certaines procédures stockées système qui effectuent des opérations de type LDD
Comparaison entre les deux types de déclencheurs
| Fonction | Déclencheur AFTER |
| Applicabilité | Tables |
| Quantité par table ou vue | Plusieurs par action de déclenchement (UPDATE, DELETE et INSERT) |
| Références en cascade | Aucune restriction |
| Exécution | Après : Traitement des contraintes Création de tables inserted et deleted |
| Fonction | Déclencheur INSTEAD OF |
| Applicabilité | Tables et vues |
| Quantité par table ou vue | Un par action de déclenchement (UPDATE, DELETE et INSERT) |
| Références en cascade | Non autorisé sur des tables cible de contraintes d’intégrité référentielle en cascade |
| Exécution | Avant : Traitement des contraintes Après : création de tables inserted et deleted |
Bravo! vous pouvez maintenant commenser le TP de cette partie : TP5-Tsql : les déclencheurs
Vous pouvez suivre la liste des vidéos du cours base de données (Merise + Sql + Transact sql) sur notre chaine youtube : Vidéos Bases de données.