Déclencheurs (Triggers)

Déclencheurs (Triggers)
Esther Pacitti
Définition
• Programme stocké et invoqué implicitement sur
un événement affectant la BD
• Séquence de règles de production déclenchées à
l’apparition d’un événement prédéfini
• Règles de Production: Event-Condition-Action
Sur Evénement
Si Condition
Alors Action
Evènement
• Dépend des transitions de l’état de la base:
– Avant ou après Insertion de données
– Avant ou après Mise à jour de données
– Avant ou après Suppression de données
Evénement élémentaire ou composite
– Conjonction d’événements élémentaires
(Ensemble d’événements élémentaires reliés
par l’opérateur OR)
Condition et Action
• Condition (when):
– Prédicat optionnel
• Action
– Block PL/SQL: exécuté après l’apparition de
l’événement si la condition événtuelle est satisfaite.
– Peut contenir des procedures cataloguées
– Peut activer des déclencheurs en cascade: maximum 32
Caractéristiques
• Granulatité
– Déclencheur orienté par table (défaut)
– Déclencheur orienté par ligne: FOR EACH
• Condition autorisée uniquement pour le
déclencheur par ligne
– Prise en compte de l’état de la base
• Antérieur à l’événement: :OLD
• Utérieur à l’événement: :NEW
Utilisation
• Le déclencheur renforce des fonctionnalités std du SGB
pour:
– Les valeurs calculées: alimentation de colonnes dérivées, de
variables globales à la base, …
– Les règles de gestion complexes
– L’intégrité référentielle dans une architecture répartie
– La duplication de tables
– Une sécurité plus sophistiquée: accès à la base sur des critères
temporels (date, heure), sur le contenu de table
– Un audit plus sophistique: statistiques sur les accès aux tables
– Etc…
Déclencheur para table
• Syntaxe:
CREATE/REPLACE TRIGGER nom_trigger
BEFORE/AFTER événement1 [OR événement2[OR événement3 …]]
ON nom_table
Bloc PL/SQL
événement = INSERT, UPDATE [OF nomcoli,…] ou DELETE
Déclencheur par table
• Utilisé si le traitement porte globalement sur
l’ensemble des lignes de la table
– Un déclencheur BEFORE par table permet d’éviter
l’exécution d’un nombre important de traitements si
certaines conditions ne sont pas respectées.
– Un déclencheur AFTER par table permet de vérifier si les
opérations se sont exécutés correctement, de réaliser des
calculs, un audit,…
Exemple 1
• Interdire l’insertion, la mise à jour et la
suppression de données de la table emp après 18h
CREATE Trigger nmj_emp
Before insert or update or delete on emp
Begin
IF (TO_CHAR(sysdate,’HH24’)) > 18
Then
if inserting then Raise_application_error(-20501,’Insertion Interdite’);
Elseif updating then Raise_application_error(-20502,’Mise à jour Interdite’);
Else
Raise_application_error(-20502,’Suppresion Interdite’);
end if;
End if;
End;
Exemple2
• Nombre de requêtes de modification intervenues
sur la colonne salaire SAL de la table emp
CREATE TRIGGER nb_maj_sal
AFTER UPDATE OF SAL ON emp
BEGIN
UPADTE surv_emp
SET cpt_maj = cpt_maj + 1
where code_op = ‘MAJ’;
END,
Déclencheur par ligne
CREATE/REPLACE TRIGGER nom_trigger
BEFORE/AFTER événement1 [OR événement2[OR événement3 …]]
ON nom_table
FOR EACH Row [When condition]
Bloc PL/SQL
événement = INSERT, UPDATE [OF nomcoli,…] ou DELETE
condition: prédicat exécuté pour chaque ligne
Utilisation
• Utilisé si le traitement porte sur chacune des lignes
de la table
– Calcul d’une colonne à partir d’autre colonnes
– Duplication de données
– Audit sophistiqué, etc
• Exemple 1:
– Contrôle d’intégrité référentielle pour le numéro de département
(deptno) avant chaque insertion ou mise à jour sur la table Emp par
rapport au contenu de la table Dept
• Exemple 2:
– Comptabiliser le nombre de lignes ajoutées, mises à jour ou
supprimées dans la table emp.
Exemple 1
CREATE Trigger nmj_emp
AFTER insert or update or delete on emp
For each row
BEGIN
IF INSERTING THEN
UPADTE Surv_emp
SET cpt_maj = cpt_maj +1
where code_op = ‘AJOUT’;
ELSEIF UPDATING THEN
UPADTE Surv_emp
SET cpt_maj = cpt_maj +1
where code_op = ‘MAJ’;
ELSE
UPADTE Surv_emp
SET cpt_maj = cpt_maj +1
where code_op = ‘SUP’;
END IF;
END IF;
END;
Exemple 2
CREATE TRIGGER total_salary
AFTER DELETE OR INSERT or UPDATE OF deptno, sal, ON EMP
FOR EACH ROW
BEGIN
IF DELETING OR (UPDATING and :old.deptno != :new.deptno)
THEN UPDATE dept
SET total_sal = total_sal - :old.sal
WHERE deptno = :old.deptno;
END IF;
IF INSERTING OR (UPDATING and :old.deptno != :new.deptno)
THEN UPDATE dept
SET total_sal = total_sal -+:old.sal
WHERE deptno = :new.deptno;
ENF IF;
IF (UPDATING AND :old.deptno = :new.deptno and :old.sal != :new.sal)
THEN UPADTE dept
SET total_sal = total_sal -:old.sal + :new.sal
WHERE deptno = :new.deptno;
END IF
END;
Status d’un déclencheur
• Enable: actif (dès sa création)
– ALTER TRIGGER nom_trigger ENABLE
– ALTER TABLE nom_table ENABLE all triggers
• Disable
– ALTER TRIGGER nom_trigger DISABLE
– ALTER TABLE nom_table DISABLE all triggers
Déclencheurs en Cascade
Update T1 SET …
Before update on T1
For each row
Begin
…
insert into T2 values values (…)
…
End;
Before update on T2
For each row
Begin
…
insert into … values (…);
…
End;
ATTENTION !!!
• Une utilisation abusive de déclencheurs sur
une base de données peut déboucher sur:
– Des inter dépendances très complexes entre
tables et par conséquence, sur une application
difficilement maintenable.
– Des temps de réponse non raisonnables pour
utilisateurs.