Bases de données 2

PL/SQL : techniques avancées

D'après le cours d'Emmanuel Waller

Triggers

Définition
- Bloc PL/SQL exécuté automatiquement en réponse à certains événements sur une table (INSERT, UPDATE, DELETE).
Motivation
- Assurer l'intégrité des données, automatiser des tâches, appliquer des contraintes complexes non gérables uniquement par SQL.
Exemples
- Utiliser des triggers pour enregistrer l'historique des modifications ou valider des conditions spécifiques avant les mises à jour.
Syntaxe générale
- Structure de base pour créer un trigger dans PL/SQL.

Qu'est-ce que c'est ?

« Déclencheur » = bloc PL/SQL qui s'exécute automatiquement en réponse à un événement.

Associé à une table ou une vue spécifique
S'exécute lors d'un ordre DML (Data Manipulation Language) comme INSERT, UPDATE, ou DELETE sur cette table
Types de triggers :
- Before trigger : s'exécute avant l'action DML, permet de valider ou modifier des données avant qu'elles soient traitées.
- After trigger : s'exécute après l'action DML, souvent utilisé pour enregistrer des logs ou des modifications dérivées.

À quoi ça sert ?

Vérifier des contraintes non exprimables en SQL (exemple : validation de relations hiérarchiques comme les ancêtres dans une arborescence)
« Tenir un journal » pour certaines mises à jour :
- Par exemple, lorsqu'une mise à jour se produit dans une table, un trigger peut enregistrer l'événement dans une autre table pour garder une trace des modifications.
Automatiser des calculs ou mises à jour complexes :
- Exemple : recalculer automatiquement un total ou une moyenne dans une table parent après une mise à jour dans une table enfant.

Exemple 1

Créer un trigger qui prévienne d'une suppression dans la table client :

create trigger avant_supp_cli
before delete on client
begin
    dbms_output.put_line('je supprime un client');
end;

Explication : Ce trigger avant_supp_cli est un trigger "before delete" qui s'exécute avant chaque suppression dans la table client. Il affiche un message indiquant qu'un client va être supprimé.

Exemple 2

Créer un trigger qui agit en cas de baisse du prix :

CREATE OR REPLACE TRIGGER post_maj_prix
AFTER UPDATE ON article
FOR EACH ROW
WHEN (:OLD.prixht IS NOT NULL AND :NEW.prixht IS NOT NULL AND :OLD.prixht > :NEW.prixht)
BEGIN
    -- Affiche l'ancien prix après mise à jour
    DBMS_OUTPUT.PUT_LINE('Ancien prix : ' || TO_CHAR(:OLD.prixht));
END;
/

Explication : Ce trigger post_maj_prix est un trigger "after update" qui s'exécute après chaque mise à jour de la table article. Il vérifie si le nouveau prix (new.prixht) est inférieur à l'ancien prix (old.prixht), et si c'est le cas, affiche le prix précédent.

Syntaxe

create [or replace] trigger nom
before | after
    insert | update [of col] | delete | insert or delete
        | ... or ...
on table
[ for each row ]
[ when ( ... ) ]
bloc PL/SQL

Remarques :

[or replace] : permet de recréer un trigger sans le supprimer au préalable.
before | after : indique si le trigger doit s'exécuter avant ou après l'action DML.
for each row : s'applique à chaque ligne affectée par l'action DML.
when : condition optionnelle pour déclencher le trigger seulement dans certains cas.

Exceptions et erreurs BD

Gestion des erreurs

PL/SQL permet de gérer les erreurs de deux façons :
- Partie programmation : Gestion des exceptions comme en Java, pour capturer et traiter les erreurs.
- Partie base de données : Gestion des erreurs spécifiques à SQL.
  - Notion d'erreur en SQL : erreurs dues aux contraintes, accès illégaux, etc.
  - Gestion des erreurs SQL en PL/SQL : mécanisme permettant d'intercepter et de traiter ces erreurs.

Pourquoi apprendre cela ? : Cela permet de rendre les applications plus robustes et de mieux contrôler les erreurs de données.

Cas particulier : levée d'erreur BD en PL/SQL

Approche pragmatique : PL/SQL permet au programmeur de lever des exceptions pour gérer les erreurs.
Par exemple, lever une exception si une opération ne respecte pas certaines conditions définies.
Le programmeur peut lever une exception avec une signification compréhensible pour le serveur, comme une erreur BD.
Similaire à la gestion d'exceptions en Java, mais en se concentrant sur les erreurs spécifiques à la base de données.

Erreurs BD : intuition

Exemple 1 : select * from cliant
- ORA-942 : table or view does not exist (erreur typique due à une faute d'orthographe dans le nom de table)
Exemple 2 : Contrainte unique non respectée
- ORA-1 : constraint violated : unique (tentative d'insertion d'une valeur déjà existante dans une colonne unique)
- Remarque : Cette erreur peut entraîner le refus d'une mise à jour ou d'une insertion si une contrainte n'est pas respectée.

Importance : Comprendre les erreurs permet de renforcer la validation et la fiabilité des opérations en base de données.

Levée d'erreur BD définie par le programmeur

raise_application_error(n, msg) : instruction pour lever une erreur personnalisée
- n : numéro d'erreur entre -20000 et -20999 (doit être unique et compréhensible pour identifier l'erreur)
- msg : message décrivant l'erreur (doit être explicite pour l'utilisateur)
Interrompt le déroulement du programme, comme en Java, permettant une gestion structurée des erreurs.
Simule une erreur BD, avec des conséquences similaires à une erreur système, par exemple une contrainte violée.

Utilité : Permet de définir des messages d'erreur précis et de contrôler comment les erreurs sont gérées dans l'application.

Levée d'une erreur BD depuis trigger

Lorsqu'un trigger lève une erreur, l'opération qui a déclenché le trigger est annulée.
Une erreur BD, comme ORA-942 ou ORA-1, est levée et empêche l'exécution de l'action DML.
Effet : Garantit que les règles et contraintes définies dans le trigger sont respectées.
Utilisé pour contrôler les mises à jour et éviter des modifications indésirables sur les données sensibles.

Application : gestion des contraintes non SQL

Rappel : Certaines contraintes complexes ne peuvent pas être exprimées directement en SQL.
Objectif : Utiliser des triggers pour détecter et empêcher les violations de contraintes spécifiques.
On veut :
- Détection : Identifier les opérations qui violeraient la contrainte.
- Refus : Empêcher la mise à jour ou l'insertion qui viole la contrainte.
- Erreur BD : Lever une erreur pour signaler la violation.
Exemple d'application : Vérification de la cohérence d'un arbre généalogique, ou contrôle d'une contrainte d'inventaire.

Un trigger peut donc automatiser la gestion de ces règles, même si elles ne peuvent pas être directement imposées par le modèle SQL.

Pour obtenir le PDF de ces diapositives et les imprimer, cliquez sur ici et utilisez ensuite l'imprimante PDF de votre navigateur. l'imprimante PDF de votre navigateur.