La gestion des modifications dans une base de données peut être complexe, notamment pour suivre les changements de données sensibles.
Sans un suivi adéquat, des erreurs peuvent survenir, compromettant l’intégrité des données et la conformité aux politiques internes.
Les déclencheurs SQL, utilisant les attributs :OLD et :NEW, offrent une solution robuste pour automatiser ces processus, garantissant un suivi précis et cohérent des modifications.
Maîtriser le langage SQL en environnement SGBDR. SQL n'aura plus de secrets pour vous !
Dans les bases de données, les déclencheurs (ou triggers ) permettent d’exécuter des actions spécifiques en réponse à des événements comme les INSERT , UPDATE , et DELETE . L’objectif de cette section est de comprendre comment utiliser les valeurs des attributs : OLD et : NEW pour manipuler les données avant et après une modification.
Attributs :OLD et :NEW en SQL
Lors de la création de déclencheurs, il est souvent nécessaire d’accéder aux valeurs d’un enregistrement avant et après une modification. C’est ici que les attributs :OLD et :NEW interviennent :
- :OLD.nom_attribut: Permet d’accéder à la valeur avant la modification.
- :NEW.nom_attribut: Permet d’accéder à la valeur après la modification.
Cette distinction est particulièrement utile pour suivre les changements effectués sur des données sensibles, comme les salaires dans une entreprise. Maintenant, examinons comment les attributs :OLD et :NEW sont accessibles selon le type d’opération.
Déclencheurs SQL selon l'opération
Les comportements des attributs :OLD et :NEW varient selon le type d’opération exécutée :
Opération | Accessibilité de :OLD | Accessibilité de :NEW | Description |
|---|---|---|---|
INSERT | Non accessible | Accessible | Comme l’enregistrement est nouveau, il n’existe pas de valeur précédente, donc :OLD n’est pas accessible. |
UPDATE | Accessible | Accessible | Cette opération modifie des enregistrements existants, donc les deux valeurs :OLD et :NEW sont disponibles. |
DELETE | Accessible | Non accessible | Étant donné que l’enregistrement est supprimé, il n’existe plus de valeur nouvelle, donc :NEW n’est pas accessible. |
Tableau 1 : Les comportements des attributs :OLD et :NEW
Maintenant que nous avons couvert les bases des attributs :OLD et :NEW, voyons comment créer des alias pour ces attributs avec la clause REFERENCING.
Optimiser avec la clause REFERENCING
Pour améliorer la lisibilité et la flexibilité des déclencheurs, il est possible de créer des alias pour :OLD et :NEW grâce à la clause REFERENCING. Cette clause permet de donner des noms personnalisés à ces valeurs, facilitant ainsi leur utilisation dans des déclencheurs complexes.
La syntaxe pour utiliser la clause REFERENCING est la suivante :
REFERENCING [ OLD AS <nom_ancien> ] [ NEW AS <nom_nouveau> ]
Grâce à cette clause, vous pouvez nommer :OLD et :NEW selon le contexte du déclencheur, par exemple ANCIEN_SALAIRE et NOUVEAU_SALAIRE. Ceci améliore non seulement la clarté, mais rend également le code plus intuitif.
Passons maintenant à des exemples pratiques pour illustrer l’utilisation de :OLD et :NEW dans des scénarios concrets.
Historisation des salaires en SQL
Dans cet exemple, nous allons créer un déclencheur qui enregistre chaque modification des salaires dans une table de log. Cela permet de garder une trace de l’historique des modifications de chaque enregistrement, utile pour des audits ou des vérifications.
Pour stocker les informations de log, nous devons d’abord créer une table LOG_MAJ_SALAIRE :
CREATE TABLE LOG_MAJ_SALAIRE (
date_maj DATE,
utilisateur VARCHAR2(30),
numero_sal NUMBER,
ancien_salaire NUMBER,
nouveau_salaire NUMBER,
ancienne_commission NUMBER,
nouvelle_commission NUMBER
);
Ensuite, créons un déclencheur qui enregistrera les modifications de salaire dans cette table à chaque mise à jour :
CREATE OR REPLACE TRIGGER log_modif_salaire
AFTER UPDATE ON salaries
FOR EACH ROW
BEGIN
INSERT INTO LOG_MAJ_SALAIRE (
date_maj, utilisateur, numero_sal, ancien_salaire, nouveau_salaire, ancienne_commission, nouvelle_commission
)
VALUES (
SYSDATE, USER, :OLD.numero_sal, :OLD.salaire, :NEW.salaire, :OLD.commission, :NEW.commission
);
END log_modif_salaire;
/
Pour tester notre déclencheur, exécutons une mise à jour des salaires :
UPDATE salaries SET salaire = salaire * 1.10;
SELECT * FROM LOG_MAJ_SALAIRE;
Résultat attendu
La table LOG_MAJ_SALAIRE contiendra les enregistrements des modifications de salaire, incluant les anciennes et nouvelles valeurs. Cela nous permet de suivre les ajustements de salaire dans le temps.
Voyons maintenant un autre exemple plus axé sur le contrôle de la validité des mises à jour de salaire.
Contrôle des salaires par déclencheurs
Ce deuxième exemple montre comment créer un déclencheur pour s’assurer que les augmentations de salaire respectent certaines règles. Par exemple, une augmentation de plus de 50 % ou une réduction du salaire pourraient être interdites.
- Déclencheur pour vérifier les modifications de salaireLe déclencheur suivant contrôle si l’augmentation dépasse 50 % ou si le salaire est réduit, et lève des erreurs si l’une de ces conditions est remplie :
CREATE OR REPLACE TRIGGER checkSalaire
BEFORE UPDATE OF salaire ON salaries
REFERENCING OLD AS ANCIEN_SALAIRE NEW AS NOUVEAU_SALAIRE
FOR EACH ROW
BEGIN
CASE
WHEN :NOUVEAU_SALAIRE.salaire >= :ANCIEN_SALAIRE.salaire * 1.50 THEN
RAISE_APPLICATION_ERROR(-20000, 'Augmentation supérieure à 50 % interdite');
WHEN :NOUVEAU_SALAIRE.salaire < :ANCIEN_SALAIRE.salaire THEN
RAISE_APPLICATION_ERROR(-20001, 'Diminution du salaire interdite');
END CASE;
END checkSalaire;
Test du déclencheur
Essayons de mettre à jour un salaire pour vérifier le fonctionnement du déclencheur :
UPDATE salaries SET salaire = salaire / 2;
Après exécution de cette requête il s’affiche le déclencheur suivant :
Avec ces deux exemples, nous voyons comment :OLD et :NEW peuvent être utilisés pour contrôler et surveiller les modifications dans une base de données, en respectant les règles métier et en assurant la traçabilité des changements.
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 fonctionnent les déclencheurs SQL?
Qu'est-ce que la clause REFERENCING en SQL?
Pourquoi utiliser des déclencheurs pour l'historisation des données?
Comment contrôler les modifications de salaire avec des déclencheurs?
Quels sont les avantages d'utiliser :OLD et :NEW dans les déclencheurs?
Conclusion
En intégrant les déclencheurs SQL dans vos bases de données, vous pouvez automatiser et sécuriser la gestion des modifications de données essentielles. Quelle autre fonctionnalité SQL pourriez-vous explorer pour optimiser vos processus de gestion des données?
