Créer des Déclencheurs SQL Efficaces

Les modifications non contrôlées dans les bases de données peuvent compromettre l’intégrité des données.

Cela peut entraîner des erreurs coûteuses et des failles de sécurité si elles ne sont pas bien gérées.

L’article explore l’utilisation des déclencheurs SQL, de la clause WHEN et des prédicats pour une gestion optimale des opérations en base de données.

Table de matière

Clause WHEN des déclencheurs SQL Fonctions prédicats pour triggers 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 !

Ce document vise à vous guider dans la création de déclencheurs avec des conditions de déclenchement (clause WHEN) et l’utilisation de prédicats (INSERTING, UPDATING, DELETING) dans des bases de données. Ces techniques permettent de mieux contrôler l’exécution des déclencheurs en fonction de conditions spécifiques.

Clause WHEN des déclencheurs SQL

Pour commencer, voyons comment utiliser la clause WHEN pour conditionner l’exécution d’un déclencheur. Cette clause est particulièrement utile lorsque vous souhaitez que le déclencheur ne s’exécute que si certaines conditions sont remplies.

Infos : La clause WHEN ne s’applique qu’aux déclencheurs de niveau enregistrement (FOR EACH ROW), car elle agit sur chaque enregistrement individuellement.

La clause WHEN s’utilise simplement en indiquant une condition qui doit être remplie pour déclencher l’action.

				
					
 WHEN <condition>

Exemple pratique

Voici un exemple de déclencheur qui utilise la clause WHEN pour détecter les augmentations de salaire suspectes.

				
					
 CREATE TABLE AUDIT_SECURITE (
datemaj DATE,
utilisateur VARCHAR2(50),
ancien_salaire NUMBER,
nouveau_salaire NUMBER,
message VARCHAR2(100)
);
CREATE OR REPLACE TRIGGER tracerAugmentation
BEFORE UPDATE ON salaries FOR EACH ROW
WHEN (NEW.salaire > OLD.salaire * 1.2)
BEGIN
INSERT INTO audit_securite VALUES (
SYSDATE,
USER,
:OLD.salaire,
:NEW.salaire,
'Augmentation suspecte de plus de 20%'
);
END tracerAugmentation;

Explication du trigger

Dans cet exemple, le déclencheur tracerAugmentation est configuré pour s’exécuter avant chaque mise à jour de la table salaries et pour chaque ligne. Son objectif est de détecter les augmentations de salaire supérieures à 20 % de l’ancien salaire.

Condition : Le déclencheur ne se déclenche que si le nouveau salaire (:NEW.salaire) est supérieur de plus de 20 % à l’ancien salaire (:OLD.salaire * 1.2).

Action : Si cette condition est remplie, une ligne est insérée dans la table audit_securite avec les informations suivantes :

La date actuelle (SYSDATE),
L’utilisateur ayant effectué la modification (USER),
L’ancien salaire ( :OLD.salaire),
Le nouveau salaire ( :NEW.salaire),
Un message indiquant une « Augmentation suspecte de plus de 20 % ».

Enfin, pour vérifier le bon fonctionnement des déclencheurs, vous pouvez effectuer les tests suivants :

Effectuez une mise à jour de salaire un enregistrement dans salaries.

				
					
 UPDATE salaries SET salaire = salaire * 1.25 ;

Voici le résultat après exécution de bloc :

Fonctions prédicats pour triggers

Passons maintenant aux fonctions prédicats, qui permettent de vérifier le type d’opération qui a déclenché le déclencheur. Ces prédicats retournent TRUE ou FALSE selon l’instruction de manipulation de données (LMD) qui est en cours : INSERT, UPDATE, ou DELETE.

Principaux prédicats

INSERTING :Retourne TRUE si l’instruction LMD est un INSERT.
UPDATING :Retourne TRUE si l’instruction LMD est un UPDATE.
DELETING :Retourne TRUE si l’instruction LMD est un DELETE.

Pour illustrer l’utilisation des prédicats, voici un exemple où nous créons un déclencheur qui enregistre le type d’opération effectuée sur la table salaries dans une table d’audit.

				
					
 CREATE TABLE AUDIT_INSTRUCTIONS (
dateinst DATE,
utilisateur VARCHAR2(20),
typeinst VARCHAR2(50)
);
CREATE OR REPLACE TRIGGER typeInstructionSalaries
BEFORE INSERT OR UPDATE OR DELETE ON salaries
BEGIN
IF INSERTING THEN
INSERT INTO AUDIT_INSTRUCTIONS VALUES (
SYSDATE,
USER,
'Exécution INSERT sur SALAIRES'
);
END IF;
IF UPDATING THEN
INSERT INTO AUDIT_INSTRUCTIONS VALUES (
SYSDATE,
USER,
'Exécution UPDATE sur SALAIRES'
);
END IF;
IF DELETING THEN
INSERT INTO AUDIT_INSTRUCTIONS VALUES (
SYSDATE,
USER,
'Exécution DELETE sur SALAIRES'
);
END IF;
END typeInstructionSalaries;

Explications

Ce déclencheur typeInstructionSalaries enregistre dans la table audit_instructions le type d’opération (INSERT, UPDATE, ou DELETE) qui a été effectué sur la table salaries.

INSERTING :Si l’opération est un INSERT, une ligne est ajoutée dans audit_instructions indiquant qu’une insertion a été effectuée.
UPDATING :Si l’opération est une mise à jour (UPDATE), une ligne est ajoutée pour indiquer une modification.
DELETING :Si l’opération est une suppression (DELETE), une ligne est ajoutée pour indiquer une suppression.

Pour tester le déclencheur en va insérez, mettez à jour, et supprimez un enregistrement dans salaries.

				
					
 INSERT INTO salaries VALUES(5, 'Ramou', 'Dacos', 'Encore un chef', 2000, NULL);
SELECT * FROM AUDIT_INSTRUCTIONS;

Voici le résultat après exécution de bloc :

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

Comment utiliser la clause WHEN dans les déclencheurs SQL?

La clause WHEN dans les déclencheurs SQL permet d’exécuter certaines actions uniquement si une condition spécifique est remplie. Par exemple, un déclencheur peut être configuré pour s’activer lors d’une augmentation de salaire supérieure à 20%, en vérifiant la condition avant d’insérer une ligne dans une table d’audit. Cette fonctionnalité est essentielle pour gérer des opérations conditionnelles et maintenir l’intégrité des données.

Quelles sont les fonctions prédicats dans les déclencheurs?

Les fonctions prédicats dans les déclencheurs sont utilisées pour déterminer le type d’opération qui a déclenché le déclencheur. Les principales fonctions prédicats incluent INSERTING, UPDATING, et DELETING, qui retournent TRUE ou FALSE en fonction de l’opération. Cela permet de spécifier des actions différentes selon que l’opération soit une insertion, une mise à jour ou une suppression, facilitant ainsi la gestion des logs et des audits.

Comment enregistrer les opérations dans une table d'audit avec des déclencheurs?

Pour enregistrer les opérations dans une table d’audit avec des déclencheurs, il est possible de créer un déclencheur qui s’active avant chaque opération sur une table cible. Par exemple, un déclencheur peut insérer un enregistrement dans une table d’audit indiquant si une opération d’insertion, de mise à jour ou de suppression a été exécutée. Cela permet d’assurer une traçabilité des modifications et de surveiller l’activité des données.

Quels sont les avantages de l'utilisation de déclencheurs SQL?

Les déclencheurs SQL offrent plusieurs avantages, notamment la possibilité d’automatiser l’exécution d’actions en réponse à des modifications de données. Ils permettent de garantir l’intégrité des données, d’effectuer des audits de sécurité, et de gérer des opérations conditionnelles grâce à des clauses comme WHEN. En codant des actions spécifiques pour les insertions, mises à jour et suppressions, les déclencheurs améliorent la gestion et le contrôle des bases de données.

Comment tester l'efficacité des déclencheurs SQL?

Pour tester l’efficacité des déclencheurs SQL, il est recommandé d’effectuer des opérations de test telles que des insertions, mises à jour et suppressions sur la table cible pour vérifier que le déclencheur s’active correctement et effectue les actions prévues. En examinant les résultats dans les tables d’audit ou de logs, il est possible d’évaluer si les déclencheurs fonctionnent comme attendu et s’ils capturent toutes les modifications nécessaires.

Conclusion

Les déclencheurs SQL sont des outils puissants pour automatiser et sécuriser les opérations en base de données. Quels autres scénarios complexes envisagez-vous d’automatiser avec des déclencheurs?