Cours 11 : Les déclencheurs
SQL Server propose deux types de déclencheurs : les déclencheurs du DML et ceux du DDL. Les déclencheurs DML
existent depuis longtemps dans SQL Server et sont présents dans de nombreuses bases de données. C’est ce type de
déclencheur qui est détaillé ici. Les déclencheurs du DDL repose sur le même principe, à savoir associer l’exécution
d’une procédure stockée à l’exécution d’une instruction. La particularité tient ici du fait que le déclencheur va être
associé à une instruction du DDL soit une commande CREATE, ALTER DROP, GRANT, DENY, REVOKE et UPDATE
STATISTICS. L’objectif de ces déclencheurs est de suivre l’évolution de la base pour réaliser au mieux les différentes
tâches administratives.
Syntaxe
CREATE TRIGGER nom_trigger ON { table | vue }
[ WITH ENCRYPTION ]
{FOR | AFTER | INSTEAD OF } { INSERT , UPDATE ,DELETE }
[ WITH APPEND ] [ NOT FOR REPLICATION ]
AS
[ IF UPDATE ( colonne )
| IF ( COLUMNS_UPDATED ( )opérateur_comparaison_bits)]
Instructions_SQL
WITH ENCRYPTION
La définition du déclencheur est enregistrée de façon cryptée. Il n’est donc pas possible de connaître le code du
déclencheur a posteriori. Cette option évite également que le déclencheur soit publié dans le cadre d’une réplication.
FOR
Permet de préciser à quel ordre SQL DML le déclencheur est associé. Par défaut, le déclencheur est de type AFTER.
AFTER
C’est le mode par défaut des déclencheurs. Le code est exécuté après vérification des contraintes d’intégrité et après
modification des données.
INSTEAD OF
Le corps du déclencheur est exécuté à la place de l’ordre SQL envoyé sur la table ou la vue. Ce type de déclencheur
est particulièrement bien adapté pour les vues.
INSERT, UPDATE, DELETE
Un déclencheur peut agir par rapport à une ou plusieurs actions. Dans ce cas, on séparera les actions par des virgules.
WITH APPEND
Cette clause n’est nécessaire que si le niveau de compatibilité de la base est inférieur ou égal à 65. Elle permet alors
d’ajouter plusieurs déclencheurs sur un même ordre SQL et un même objet. Ce comportement est celui par défaut
depuis la version (70).
NOT FOR REPLICATION
Indique que le déclencheur ne doit pas être exécuté lorsque la modification des données est issue d’un processus de
réplication.
IF UPDATE (colonne)
Ne peut être utilisé que pour les déclencheurs UPDATE ou INSERT et ne s’exécutera que si la ou les colonnes sont
concernées.
IF ( COLUMNS_UPDATED ( ) opérateur_comparaison_bits)
Cette fonction permet de connaître les indices de la ou des colonnes qui ont été mises à jour. Pour chaque colonne
affectée par la mise à jour, un bit est levé. Pour savoir quelles ont été les colonnes mises à jour, une simple
comparaison binaire suffit.
Instructions_SQL
Il est possible d’utiliser toutes les instructions Transact SQL de manipulations de données (DML). Les instructions
suivantes ne sont pas autorisées :
• CREATE et DROP.
• ALTER TABLE et ALTER DATABASE.
• TRUNCATE.
• GRANT et REVOKE.
• UPDATE STATISTICS.
• RECONFIGURE.
• LOAD DATABASE.
Il possible de définir, sur une même table, plusieurs déclencheurs pour chaque opération INSERT, UPDATE et DELETE
Les déclencheurs sont exécutés après (AFTER) vérification des contraintes d’intégrité et insertion des données dans la
table. Si l’instruction SQL échoue, alors le déclencheur n’est pas exécuté.
AM1
Cours 11 : Les déclencheurs
Exemples
Création automatique d’une facture lors du changement d’état de la commande :
Demande de création d’un déclencheur sur la table Commandes depuis SQL Server Management Studio :
Création d’un premier déclencheur sur la table Commandes :
AM2
Cours 11 : Les déclencheurs
Création d’un second déclencheur sur la table Commandes pour calculer le taux de remise :
Connaître les déclencheurs posés sur la table des commandes :
Fixer le premier et le dernier déclencheur qui s’exécutent lors de chaque opération d’INSERT sur la table des commandes :
AM3
