Les déclencheurs PL/SQL sont sous-utilisés malgré leur potentiel immense pour automatiser les tâches dans une base de données.
Sans ces automatisations, vous risquez de perdre en efficacité et d’accroître le risque d’erreurs humaines dans la gestion des données.
Cet article vous guide à travers les concepts clés des déclencheurs PL/SQL et propose un exercice pratique pour comprendre leur application.
Maîtriser le langage SQL en environnement SGBDR. SQL n'aura plus de secrets pour vous !
Les déclencheurs (triggers) sont des mécanismes puissants dans PL/SQL qui permettent d’automatiser des actions en réponse à des événements sur une table. Voici les points clés que nous avons étudiées :
- Les Déclencheurs LMD :Ces déclencheurs sont spécifiquement conçus pour s’exécuter lors des opérations deLecture,Modification etDelete sur les données.
- Niveau d’Exécution des Déclencheurs :Les déclencheurs peuvent être définis au niveau d’instruction ou de ligne, ce qui détermine comment ils se déclenchent.
- Accès aux Valeurs via OLD et NEW :Utilisez les mots-clés OLD et NEW pour accéder aux valeurs des colonnes avant et après une opération.
- Déclenchement Conditionnel (WHEN) :Les déclencheurs peuvent être conditionnels, ce qui signifie qu’ils ne s’exécutent que lorsque certaines conditions sont remplies.
- Prédicats (INSERTING, UPDATING et DELETING) :Ces prédicats aident à identifier l’opération en cours (insertion, mise à jour ou suppression).
- Déclencheurs INSTEAD OF :Ces déclencheurs remplacent l’action par défaut lors de l’exécution de l’instruction.
- Directive PRAGMA AUTONOMOUS_TRANSACTION :Permet aux déclencheurs de créer des transactions indépendantes.
- Tâches d’Exploitation des Déclencheurs :Les déclencheurs peuvent être utilisés pour des tâches variées, comme la journalisation, la validation des données, etc.
Exercice : Déclencheur LMD en PL/SQL
Énoncé de l’Exercice :
Créez un déclencheur LMD nommé trigLogClients qui se déclenche après toute instruction INSERT, UPDATE ou DELETE sur la table clients. Le trigger doit tracer les informations ci-dessous dans une table que vous aurez créée au préalable :
- La date d’exécution et le user ayant effectué l’instruction.
- La valeur AVANT et APRES pour les colonnes NUMERO_CLI, NOM, ADRESSE, CODE_POSTAL, VILLE et TELEPHONE.
- L’instruction qui a déclenché le déclencheur (INSERT, UPDATE ou DELETE).
Solution de l’Exercice :
- Création de la table de journalisation :
CREATE TABLE log_clients (
log_id NUMBER PRIMARY KEY,
action VARCHAR2(10),
user_name VARCHAR2(50),
execution_date TIMESTAMP,
numero_cli_old VARCHAR2(50),
nom_old VARCHAR2(100),
adresse_old VARCHAR2(150),
code_postal_old VARCHAR2(10),
ville_old VARCHAR2(50),
telephone_old VARCHAR2(20),
numero_cli_new VARCHAR2(50),
nom_new VARCHAR2(100),
adresse_new VARCHAR2(150),
code_postal_new VARCHAR2(10),
ville_new VARCHAR2(50),
telephone_new VARCHAR2(20)
);
Et voici son résultat lors de son exécution :
- Création du déclencheur
CREATE OR REPLACE TRIGGER trigLogClients
AFTER INSERT OR UPDATE OR DELETE ON clients
FOR EACH ROW
BEGIN
INSERT INTO log_clients (
action,
user_name,
execution_date,
numero_cli_old,
nom_old,
adresse_old,
code_postal_old,
ville_old,
telephone_old,
numero_cli_new,
nom_new,
adresse_new,
code_postal_new,
ville_new,
telephone_new
) VALUES (
CASE
WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END,
USER,
SYSTIMESTAMP,
:OLD.NUMERO_CLI,
:OLD.NOM,
:OLD.ADRESSE,
:OLD.CODE_POSTAL,
:OLD.VILLE,
:OLD.TELEPHONE,
:NEW.NUMERO_CLI,
:NEW.NOM,
:NEW.ADRESSE,
:NEW.CODE_POSTAL,
:NEW.VILLE,
:NEW.TELEPHONE
);
END;
/
Explication de la Solution
- Création de la table log_clients :Cette table enregistre toutes les actions effectuées sur la table clients avec les anciennes et nouvelles valeurs des colonnes pertinentes ainsi que des informations sur l’utilisateur et le moment de l’action.
- Déclencheur trigLogClients :Il est défini pour se déclencher après chaque insertion, mise à jour ou suppression sur la table clients.Utilise la clause FOR EACH ROW pour assurer que chaque ligne affectée par l’action est traitée.La clause CASE détermine quel type d’action a déclenché le déclencheur et insère les valeurs correspondantes dans la table log_clients.Les valeurs OLD et NEW permettent d’accéder aux valeurs avant et après l’opération.
Clés des Déclencheurs PL/SQL
Point Clé | Description |
|---|---|
Les Déclencheurs LMD | Exécutés lors des opérations de Lecture, Modification et Delete. |
Niveau d’Exécution | Déclencheurs définis au niveau d’instruction ou de ligne. |
Accès aux Valeurs | Utilisation des mots-clés OLD et NEW pour accéder aux valeurs avant et après l’opération. |
Déclenchement Conditionnel | Déclencheurs configurés pour s’exécuter uniquement lorsque certaines conditions sont remplies. |
Prédicats | Utilisation de INSERTING, UPDATING, et DELETING pour identifier l’opération en cours. |
Déclencheurs INSTEAD OF | Remplacent l’action par défaut lors de l’exécution de l’instruction. |
Directive PRAGMA AUTONOMOUS_TRANSACTION | Permet aux déclencheurs de créer des transactions indépendantes. |
Tâches d’Exploitation | Utilisation pour diverses tâches, telles que la journalisation et la validation des données. |
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 LMD en PL/SQL ?
Un déclencheur LMD (Language de Manipulation des Données) en PL/SQL est un type de trigger conçu pour s’exécuter automatiquement lors d’opérations de lecture, de modification ou de suppression sur une table. Ces déclencheurs permettent d’automatiser des actions comme la journalisation ou la validation des données, en s’exécutant avant ou après une opération spécifique sur les données.
Comment utiliser les mots-clés OLD et NEW dans un déclencheur PL/SQL ?
Dans un déclencheur PL/SQL, les mots-clés OLD et NEW sont utilisés pour accéder aux valeurs des colonnes avant et après une opération. OLD fait référence à la valeur de la colonne avant l’opération, tandis que NEW se réfère à la valeur après l’opération. Cela permet de suivre les modifications apportées aux données et d’exécuter des actions basées sur ces changements.
Comment créer un déclencheur conditionnel en PL/SQL ?
Pour créer un déclencheur conditionnel en PL/SQL, une clause WHEN est utilisée pour définir les conditions sous lesquelles le déclencheur doit s’exécuter. Cela permet de spécifier des critères précis, comme l’exécution du déclencheur uniquement si une certaine condition est remplie, offrant ainsi un contrôle granulaire sur le comportement du déclencheur.
Quels sont les avantages des déclencheurs INSTEAD OF ?
Les déclencheurs INSTEAD OF remplacent l’action par défaut lors de l’exécution d’une instruction sur une vue. Cela permet de définir un comportement personnalisé lorsque des opérations comme INSERT, UPDATE ou DELETE sont tentées sur une vue qui ne peut pas être modifiée directement, facilitant ainsi des implémentations complexes.
Comment assurer la journalisation des opérations avec les déclencheurs PL/SQL ?
Pour assurer la journalisation des opérations avec les déclencheurs PL/SQL, un déclencheur doit être configuré pour enregistrer les détails de chaque opération dans une table de journalisation. Cela inclut des informations telles que l’utilisateur, l’heure de l’opération, et les valeurs des colonnes avant et après l’opération, permettant une traçabilité et une auditabilité complètes des modifications de données.
Conclusion
En maîtrisant les déclencheurs PL/SQL, vous pouvez considérablement améliorer l’efficacité et la sécurité de vos bases de données. Quel sera votre prochain projet d’automatisation avec des déclencheurs PL/SQL ?
