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.
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.
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.
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.
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 ?
Quand utiliser un déclencheur de niveau table ?
Quand utiliser un déclencheur de niveau enregistrement ?
Comment un déclencheur de niveau table fonctionne-t-il ?
Comment tester les déclencheurs SQL ?
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 ?