Module 140

Administrer et exploiter des bases de données

Jour 6 - Triggers

# Objectifs du jour

Objectifs du jour

  • Théorie sur les triggers

  • Séquences oracles

  • Exercices triggers

# Corrections des exercices

Correction de l'exercice 4.6

 

  • Correction de l'exercice 4.6 - PL/SQL avec exceptions

  • Corrigé sur le Gitbook :

Triggers

  • Triggers ou déclencheurs pour les bases de données
  • Evénement - Condition - Action
    • Evénement : Insert, Update, Delete
    • Condition : Faire l'action uniquement dans certains cas
    • Action : Instructions SQL de LMD (instructions du LDD, LCD ou LCT ne sont pas admises)
# Triggers
# Triggers

Séquences oracles

Une séquence Oracle est un objet de base de données qui génère automatiquement des nombres uniques et séquentiels. Elle est souvent utilisée pour créer des clés primaires ou des identifiants uniques pour les tables.

Pour créer une séquence, on utilise la commande CREATE SEQUENCE avec plusieurs options :

CREATE SEQUENCE nom_sequence
INCREMENT BY intervalle
START WITH valeur_initiale
MINVALUE valeur_min
MAXVALUE valeur_max
CYCLE | NOCYCLE
CACHE taille_cache | NOCACHE
ORDER | NOORDER;

Pour obtenir la prochaine valeur d'une séquence, on utilise la pseudo-colonne NEXTVAL :

 

Pour obtenir la valeur courante, on utilise CURRVAL :

SELECT nom_sequence.NEXTVAL FROM dual;
SELECT nom_sequence.CURRVAL FROM dual;
# Triggers

Séquences oracles

Exemple pour la clé primaire des clients :

-- Créer un trigger pour alimenter automatiquement la colonne de clé primaire
CREATE OR REPLACE TRIGGER trg_pk_cli_auto
BEFORE INSERT ON clients
FOR EACH ROW
BEGIN
    IF :NEW.pk_cli IS NULL THEN
        :NEW.pk_cli := seq_clients.NEXTVAL;
    END IF;
END;
/
CREATE SEQUENCE seq_clients
 START WITH     1
 INCREMENT BY   1

Puis un trigger est utilisé pour ajouter automatiquement le prochain nombre à la PK :

Exercice 5.1

Ajouter des séquences au script de
création du magasin :

Exercice sur les séquences

# Triggers

Exemple de trigger :

CREATE OR REPLACE TRIGGER verif_salaire
BEFORE INSERT OR UPDATE employes
FOR EACH ROW
WHEN (new.pourcentage_emp > 50)
BEGIN
   IF :NEW.salaire > 4000 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Le salaire ne peut pas dépasser 400.');
   END IF;
END verif_salaire;
/

Ordre des triggers pour déterminer quand oracle déclenche le trigger :

  • Before
  • After

La définition comprend le type d'instruction SQL qui déclenche le
trigger :

  • DELETE, INSERT ou UPDATE
  • On peut en avoir une, deux ou les trois.

La définition du trigger précise la table associée au trigger

# Triggers
# Triggers

Type de trigger :

  • Trigger de table ou global : Sans FOR EACH ROW
    • Déclenché une seule fois
  • Trigger de ligne : Avec FOR EACH ROW
    • Déclenché pour chaque ligne
  • Pour les triggers de lignes, on peut introduire une restriction
    sur les lignes à l'aide d'une expression logique grâce à WHEN
    • Exemple : WHEN (new.pourcentage_emp > 50)
CREATE OR REPLACE TRIGGER verif_salaire
BEFORE INSERT OR UPDATE employes
FOR EACH ROW
WHEN (new.pourcentage_emp > 50)
BEGIN
   IF :NEW.salaire > 4000 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Le salaire ne peut pas dépasser 400.');
   END IF;
END verif_salaire;
/

# Triggers

Noms de corrélation

  • Dans un trigger ligne, on doit pouvoir accéder aux anciennes et nouvelles valeurs de colonne de la ligne
  • Les noms de corrélation permettent de désigner ces deux
    valeurs : un nom pour l'ancienne et un pour la nouvelle.
    • :new.colonne
    • :old.colonne
  • Exemple : IF :new.salaire < :old.salaire
  • Si l'instruction de déclenchement du trigger est INSERT,
    seule la nouvelle valeur a un sens.
  • Si l'instruction de déclenchement du trigger est DELETE,
    seule l'ancienne valeur a un sens.

Exercice 5.2

Création d'un trigger pour enregistrer

la date de modification :

Introduction PL/SQL

# Triggers
# Triggers

Les prédicats conditionnels

  • Quand un trigger comporte plusieurs instructions de déclenchement (par exemple INSERT OR DELETE OR UPDATE), on peut utiliser des prédicats conditionnels
  • INSERTING, DELETING et UPDATING : pour exécuter des blocs de code spécifiques pour chaque instruction de déclenchement
  • Exemple :
CREATE TRIGGER ...
BEFORE INSERT OR UPDATE ON employe
BEGIN
  IF INSERTING THEN 
    ...
  END IF;
  
  IF UPDATING THEN
     ...
  END IF;
END;
/
# Triggers

Les prédicats conditionnels

  • UPDATING peut être suivi d'un nom de colonne :
CREATE TRIGGER ...
BEFORE INSERT OR UPDATE ON employe
BEGIN
  IF INSERTING THEN 
    ...
  END IF;
  
  IF UPDATING('salaire') THEN
     ...
  END IF;
END;
/
  • UPDATING('salaire') : Renvoie TRUE si la colonne salaire est modifiée dans l'instruction UPDATE 
# Triggers

Activation d'un trigger

  • Un trigger peut être activé ou désactivé
  • S'il est désactivé, ORACLE le stocke, mais l'ignore
  • Par défaut, un trigger est activé dès sa création
  • Pour désactiver un trigger :
ALTER TRIGGER nomtrigger DISABLE;
  • On peut désactiver tous les triggers associés à une table :
ALTER TABLE nomtable DISABLE ALL TRIGGERS;
  • A l'inverse, on peut les réactiver avec ENABLE :
ALTER TRIGGER nomtrigger ENABLE;
ALTER TABLE nomtable ENABLE ALL TRIGGERS;

Exercice 5.3

Trigger pour historisation et utilisation

des prédicats (INSERTING, etc) :

Introduction PL/SQL

# Triggers

Exercice 5.4

Exercice complet avec gestion de

stock sur le magasin :

Introduction PL/SQL

# Triggers
# Travail pour la prochaine fois

Travail pour la prochaine fois

  • Terminer les exercices sur les triggers (série 5)

Merci de votre attention 😊
Bonne fin de journée !