Comprendre les Déclencheurs SQL

Les déclencheurs SQL sont souvent mal compris, ce qui peut entraîner des erreurs dans la gestion des bases de données.

Ignorer la différence entre les niveaux d’exécution des déclencheurs peut conduire à des erreurs répétées et à une inefficacité accrue.

Cet article explore les niveaux d’exécution des déclencheurs SQL pour vous aider à mieux gérer vos bases de données et à éviter les erreurs courantes.

Table de matière

Déclencheurs SQL : Niveaux d'exécution Déclencheurs niveau table vs enregistrement FAQ Conclusion

Formation Le langage PL/SQL : Le Guide Complet

Maîtriser le langage SQL en environnement SGBDR. SQL n'aura plus de secrets pour vous !

L’objectif de ce document est de comprendre les différents niveaux d’exécution d’un déclencheur dans une base de données.

Déclencheurs SQL : Niveaux d'exécution

Les déclencheurs ( triggers ) peuvent s’exécuter à deux niveaux principaux : le niveau table ( STATEMENT ) et le niveau enregistrement ( ROW ).

Niveau table (STATEMENT) :Estun déclencheur de niveau table s’exécuteune seule fois par instruction, peu importe le nombre de lignes affectées.

Astuce Pratique : Utilisez un déclencheur de niveau table si votre opération concerne des actions globales, comme un audit ou la mise à jour d’un journal, et que vous n’avez pas besoin d’une vérification ligne par ligne.
Exemple : Une instruction UPDATE modifie 50 lignes ? Le déclencheur de niveau table s’exécutera une seule fois pour l’ensemble des lignes.

Niveau Enregistrement (ROW) :Est un déclencheur de niveau enregistrement s’exécuteséparément pour chaque ligneaffectée par l’instruction.

Erreur Courante : Attention ! Si vous ne souhaitez pas que le déclencheur s’exécute plusieurs fois, n’utilisez pas le niveau enregistrement, car cela peut entraîner des erreurs ou des modifications involontaires répétées.

Par exemple une instruction INSERT ajoute 10 lignes ? Le déclencheur de niveau enregistrement s’exécutera 10 fois , une fois pour chaque nouvelle ligne ajoutée

Utilisation de FOR EACH ROW

Lorsqu’un déclencheur doit s’exécuter au niveau enregistrement, utilisez l’instruction FOR EACH ROW pour indiquer que le déclencheur doit être appliqué à chaque ligne affectée.

Question : Quand dois-je utiliser FOR EACH ROW ?

Utilisez FOR EACH ROW uniquement pour les déclencheurs de niveau enregistrement, car ils nécessitent une exécution individuelle pour chaque ligne modifiée.

Déclencheurs niveau table vs enregistrement

Voici des exemples de déclencheurs pour chaque niveau d’exécution (niveau table et niveau enregistrement) sur la table CATEGORIES.

Déclencheur de niveau table (STATEMENT)

Ce déclencheur s’exécute une seule fois par instruction, quel que soit le nombre de lignes affectées.

				
					
 -- Déclencheur de niveau table : enregistre l'heure de modification après toute mise à jour de la table
CREATE OR REPLACE TRIGGER trg_cat_upd_stmt
AFTER UPDATE ON CATEGORIES
BEGIN
INSERT INTO CATEGORIES_LOG (LOG_ID, CODE_CAT, LIBELLE_CAT, ACTION, ACTION_DATE)
VALUES (CATEGORIES_LOG_SEQ.NEXTVAL, NULL, NULL, 'UPDATE_STATEMENT', SYSDATE);
END;
/

Explication :

Ce déclencheur est de niveau table et se déclenche après toute mise à jour sur la table CATEGORIES, peu importe le nombre de lignes affectées.
Il enregistre une entrée dans CATEGORIES_LOG pour indiquer qu’une mise à jour globale a eu lieu, avec CODE_CAT et LIBELLE_CAT laissés vides, car le déclencheur ne traite pas les lignes individuellement.
Déclencheur de niveau enregistrement (ROW)

Ce déclencheur s’exécute pour chaque ligne affectée par une instruction, ce qui signifie qu’il peut s’exécuter plusieurs fois pour une même commande si plusieurs lignes sont affectées.

				
					
 -- Déclencheur de niveau enregistrement : journalise chaque insertion dans la table CATEGORIES
CREATE OR REPLACE TRIGGER trg_categories_insert_row
AFTER INSERT ON CATEGORIES
FOR EACH ROW
BEGIN
INSERT INTO CATEGORIES_LOG (LOG_ID, CODE_CAT, LIBELLE_CAT, ACTION, ACTION_DATE)
VALUES (CATEGORIES_LOG_SEQ.NEXTVAL, :NEW.CODE_CAT, :NEW.LIBELLE_CAT, 'INSERT_ROW', SYSDATE);
END;
/

Explication :

Ce déclencheur est de niveau enregistrement et se déclenche après chaque insertion individuelle dans la table CATEGORIES.
À chaque insertion d’une ligne, une entrée dans CATEGORIES_LOG est ajoutée, enregistrant CODE_CAT, LIBELLE_CAT, et l’action INSERT_ROW.
L’instruction FOR EACH ROW indique que ce déclencheur s’applique à chaque ligne.

Ces exemples montrent comment configurer les déclencheurs en fonction du niveau d’exécution souhaité.

Pour tester les déclencheurs créés, nous allons insérer, mettre à jour et vérifier les entrées dans la table CATEGORIES pour observer leur effet dans la table de journalisation CATEGORIES_LOG. Voici les étapes de test pour chaque déclencheur.

Voici le test du déclencheur de niveau table (STATEMENT)

Effectuons une mise à jour sur plusieurs lignes dans la table CATEGORIES et vérifions si le déclencheur de niveau table enregistre une seule action de type UPDATE_STATEMENT.

				
					
 -- Mise à jour de plusieurs enregistrements dans CATEGORIES
UPDATE CATEGORIES
SET LIBELLE_CAT = 'Catégorie modifiée'
WHERE CODE_CAT < 10;
-- Vérification des entrées dans CATEGORIES_LOG
SELECT * FROM CATEGORIES_LOG WHERE ACTION = 'UPDATE_STATEMENT';

Résultat attendu :

Une seule ligne de log est ajoutée dans CATEGORIES_LOG avec l’action UPDATE_STATEMENT, même si plusieurs lignes dans CATEGORIES sont modifiées.

Test du déclencheur de niveau enregistrement (ROW)

Ajoutons plusieurs lignes dans la table CATEGORIES et vérifions que chaque insertion est journalisée séparément dans CATEGORIES_LOG avec l’action INSERT_ROW.

				
					
 -- Insertion de plusieurs lignes dans CATEGORIES
INSERT INTO CATEGORIES (CODE_CAT, LIBELLE_CAT) VALUES (101, 'Catégorie A');
INSERT INTO CATEGORIES (CODE_CAT, LIBELLE_CAT) VALUES (102, 'Catégorie B');
INSERT INTO CATEGORIES (CODE_CAT, LIBELLE_CAT) VALUES (103, 'Catégorie C');
-- Vérification des entrées dans CATEGORIES_LOG pour l'action 'INSERT_ROW'
SELECT * FROM CATEGORIES_LOG WHERE ACTION = 'INSERT_ROW';

Résultat attendu :

Trois lignes de log sont ajoutées dans CATEGORIES_LOG, chacune correspondant à une insertion avec CODE_CAT et LIBELLE_CAT des nouvelles catégories (101, 102, 103).

Formez-vous gratuitement avec Alphorm !

Maîtrisez les compétences clés en IT grâce à nos formations gratuites et accélérez votre carrière dès aujourd'hui.

FAQ

Qu'est-ce qu'un déclencheur SQL ?

Un déclencheur SQL est un ensemble d’instructions en base de données qui s’exécute automatiquement en réponse à certains événements sur une table ou une vue. Les déclencheurs sont utilisés pour maintenir l’intégrité des données, auditer les modifications ou automatiser des tâches répétitives. Ils peuvent s’exécuter avant ou après une action, comme une insertion, une mise à jour ou une suppression, permettant ainsi de contrôler et d’interagir avec les données modifiées.

Quand utiliser un déclencheur de niveau table ?

Les déclencheurs de niveau table s’exécutent une seule fois pour l’ensemble d’une instruction, indépendamment du nombre de lignes affectées. Ils sont idéaux pour des actions globales, telles que l’audit de modifications ou la mise à jour d’un journal, où une vérification ligne par ligne n’est pas nécessaire. Par exemple, lors d’une mise à jour de plusieurs lignes, un déclencheur de niveau table peut enregistrer une seule entrée pour l’ensemble de l’opération.

Quand utiliser un déclencheur de niveau enregistrement ?

Les déclencheurs de niveau enregistrement s’exécutent individuellement pour chaque ligne affectée par une instruction. Ils sont nécessaires lorsque chaque modification de ligne doit être traitée séparément, par exemple, pour journaliser chaque insertion ou mise à jour spécifique. Utilisez l’instruction FOR EACH ROW pour indiquer qu’un déclencheur doit être appliqué à chaque ligne modifiée, garantissant une gestion précise et granulaire des données.

Comment un déclencheur de niveau table fonctionne-t-il ?

Un déclencheur de niveau table s’exécute une seule fois par instruction exécutée sur une table, quel que soit le nombre de lignes affectées. Par exemple, lors d’une mise à jour massive, le déclencheur enregistrera une seule action dans un journal, indiquant que l’opération a eu lieu. Cela est particulièrement utile pour les tâches générales qui ne nécessitent pas d’examen détaillé de chaque ligne modifiée.

Comment tester les déclencheurs SQL ?

Pour tester les déclencheurs SQL, effectuez des opérations sur la table concernée, telles que des insertions ou des mises à jour, puis vérifiez les résultats dans la table de journalisation associée. Par exemple, après avoir inséré plusieurs lignes, examinez si chaque insertion est correctement enregistrée par le déclencheur de niveau enregistrement. De même, effectuez une mise à jour de masse et vérifiez si le déclencheur de niveau table enregistre une seule entrée pour l’ensemble de l’opération.

Conclusion

Les déclencheurs SQL jouent un rôle essentiel dans la gestion des bases de données. Quel niveau d’exécution considérez-vous le plus adapté pour votre prochain projet SQL ?