Le Langage de Contr

1
Le Langage de Contrôlede DonnéesTRIGGERS
2
Généralités sur les Triggers

• Programme évènementiel
• Bloc événement
• (UPDATE, DELETE, INSERT)
• Bloc action
• (bloc PL/SQL)
• Trois fonctions assurées
• Mise en place de contraintes complexes
• Mise à jour de colonnes dérivées
• Génération dévènements
• Associé à une table
• Suppression de la table ? suppression des triggers

SI évènement ALORS action SINON rien FINSI
3
12 types de Triggers

• "Row" Trigger ou "Statement" Trigger
• Row le trigger est exécuté pour chaque ligne
  touchée
• Statement le trigger est exécuté une fois
• Exécution avant ou après lévénement
• "before " le bloc action est levé avant que
  lévénement soit exécuté
• "after " le bloc action est levé après
  lexécution du bloc événement
• Trois évènements possibles
• UPDATE certaines colonnes
• INSERT
• DELETE

2
2
3
4
Syntaxe de création
CREATE OR REPLACE TRIGGER ltnom_triggergt BEFORE
AFTER INSERTDELETEUPDATE OF colonnes ON
ltnom_tablegt FOR EACH ROW DECLARE --
déclaration de variables, exceptions --
curseurs BEGIN -- bloc action -- ordres SQL et
PL/SQL END /
row trigger si présent
5
Anciennes et nouvelles valeurs

• Pour les row trigger (triggers lignes ) accès
  aux valeurs des colonnes pour chaque ligne
  modifiée
• Deux variables NEW.colonne et OLD.colonne

Ancienne valeur OLD.colonne Nouvelle valeur NEW.colonne
INSERT NULL Nouvelle valeur
DELETE Ancienne valeur NULL
UPDATE Ancienne valeur Nouvelle valeur
6
Trigger de contraintes lever une erreur

• Test de contraintes erreur ou pas
• Ordre RAISE_APPLICATION_ERROR
• Nerreur -20000 , -20999 ? SQLCODE
• Texte erreur message envoyé ? SQLERRM

RAISE_APPLICATION_ERROR(nerreur,texte erreur)
7
Exemples de row trigger prise de commande
produits
(1)
idProd NomProd QtStock Seuil
détail_commandes
p1 Produit 1 20 15
P2 Produit 2 50 12

IdCom idProd QtCom
1001 p1 10

INSERT
(2)
(3)
réapprovisionnement

• (1) Contrôle
• QtStock gt QtCom ?
• (2) Mise à jour
• QtStockQtStock-QtCom
• (3) génération évènements
• Ajout dans réappro si
• Seuil gt QtStock

idProd NomProd QtStock Seuil



8
Prise de commande (1) contrôle quantité en
stock ?
CREATE TRIGGER t_b_i_detail_commandes BEFORE
INSERT ON détail_commandes FOR EACH
ROW DECLARE v_qtstock NUMBER BEGIN SELECT
qtstock INTO v_qtstock FROM produits WHERE idprod
NEW.idprod IF v_qtstock lt NEW.qtcom
THEN RAISE_APPLICATION_ERROR(-20001,stock
insuffisant) END IF END /
9
Prise de commande (2) Mise à jour quantité en
stock
CREATE TRIGGER t_a_i_detail_commandes AFTER
INSERT ON detail_commandes FOR EACH
ROW BEGIN UPDATE produits p SET p.qtstock
p.qtstock - NEW.qtcom WHERE idprod
NEW.idprod END /
10
Prise de commande (3) génération dun
réapprovisionnement
CREATE TRIGGER t_a_u_produits AFTER UPDATE OF
qtstock ON produits FOR EACH ROW BEGIN IF
NEW.qtstock lt NEW.seuil THEN INSERT INTO
reapprovisionnement VALUES (NEW.idprod,NEW.nompr
od,NEW.qtstock, NEW.seuil) END IF END /
11
Les prédicats dans un trigger

• On peut regrouper tous les triggers dun même
  type BEFORE ou AFTER
• On précise lordre dans le BEGIN

CREATE TRIGGER ltnom_triggergt BEFOREAFTER INSERT
OR DELETE OR UPDATE OF colonnes ON
ltnom_tablegt FOR EACH ROW DECLARE --
déclaration de variables, exceptions BEGIN IF
UPDATING(colonne) THEN END IF IF DELETING
THEN END IF IF INSERTING THEN END
IF END /
12
Limitation des Trigger

• Impossible daccéder sur la table sur laquelle
  porte le trigger
• Attention aux effets de bords
• exemple  trigger x1 de la table T1 fait un
  insert dans la table T2 qui possède un trigger x2
  qui modifie T3 qui possède un trigger x3 qui
  modifie T1

T1
T2
T3
X1
X2
X3
13
Les triggers détatou Statement trigger

• Raisonnement sur la globalité de la table et non
  sur un enregistrement particulier
• TRIGGER BEFORE 1 action avant un ordre UPDATE
  de plusieurs lignes
• TRIGGER AFTER 1 action après un ordre UPDATE
  touchant plusieurs lignes

14
Exemple de Statement Trigger

• Interdiction demprunter un ouvrage pendant le
  week-end

CREATE TRIGGER controle_date_emp BEFORE UPDATE
OR INSERT OR DELETE ON emprunt BEGIN IF
TO_CHAR(SYSDATE,DY) IN (SAT,SUN)
THEN  RAISE_APPLICATION_ERROR (-20102,Désole les
emprunts sont interdits le week-end...)  END
IF END /
15
Les Triggers INSTEAD OF

• Trigger faisant le travail à la place de ..
• Posé sur une vue multi-table pour autoriser les
  modifications sur ces objets virtuels
• Utilisé dans les bases de données réparties pour
  permettre les modifications sur le objets
  virtuels fragmentés (cours BD Réparties)
• Permet dassurer un niveau dabstraction élevé
  pour les utilisateurs ou développeurs clients
  les vraies mises à jour sont faites à leur insu
  .

16
Exemple de trigger instead of

• Vue étudiant résultant de 4 tables

S T A G E
ETUDIANT_LICENCE
ETUDIANT
ETUDIANT_MASTERE
ETUDIANT_DOCTORAT
17
Exemple de trigger instead ofConstruction de
la vue ETUDIANT
Colonne virtuelle
CREATE VIEW etudiant (ine,nom,adresse,cycle,noms
tage,adstage) AS SELECT el.ine,el.nom,el.adr,L,s
.noms,s.ads FROM etudiant_licence el, stage
s WHERE el.ines.ine UNION SELECT
em.ine,em.nom,em.adr,M,s.noms,s.ads FROM
etudiant_mastere em, stage s WHERE
em.ines.ine UNION SELECT ed.ine,ed.nom,ed.adr,D
,s.noms,s.ads FROM etudiant_doctorat ed, stage
s WHERE ed.ines.ine
18
Exemple de trigger instead ofutilisation de la
vue INSERT
INSERT INTO etudiant VALUES (100,Michel,Toulous
e,M,Oracle,CICT)
ETUDIANT
100 Michel Toulouse
100 Oracle CICT
19
Exemple de trigger instead oftrigger pour
INSERT
CREATE TRIGGER insert_etudiant INSTEAD OF INSERT
ON etudiant FOR EACH ROW BEGIN IF NEW.cycleL
THEN INSERT INTO etudiant_licence
VALUES (NEW.ine,NEW.nom,NEW.adresse) INSERT
INTO stage VALUES (NEW.ine,NEW.nomstage,NEW.ads
tage) ELSIF NEW.cycleM THEN ....... Idem
pour M et D ........ ELSE RAISE_APPLICATION_ERROR
(-20455,Entrer M, L ou D) END IF END /
20
Manipulation des Triggers

• Suppression dun trigger
• Désactivation dun trigger
• Réactivation dun trigger
• Tous les triggers dune table

DROP TRIGGER ltnomtriggergt 
ALTER TRIGGER ltnomtriggergt DISABLE
ALTER TRIGGER ltnomtriggergt ENABLE
ALTER TABLE ltnomtablegt ENABLEDISABLE ALL
TRIGGERS
21
Dictionnaire des Données

• USER_TRIGGERS

TRIGGER_NAME VARCHAR2(30) TRIGGER_TYPE
VARCHAR2(16) TRIGGERING_EVENT
VARCHAR2(227) TABLE_OWNER
VARCHAR2(30) BASE_OBJECT_TYPE
VARCHAR2(16) TABLE_NAME
VARCHAR2(30) COLUMN_NAME
VARCHAR2(4000) REFERENCING_NAMES
VARCHAR2(128) WHEN_CLAUSE
VARCHAR2(4000) STATUS
VARCHAR2(8) DESCRIPTION
VARCHAR2(4000) ACTION_TYPE
VARCHAR2(11) TRIGGER_BODY LONG
22
Dictionnaire des Données (suite)

• USER_TRIGGER_COLS

TRIGGER_OWNER VARCHAR2(30) TRIGGER_NAME
VARCHAR2(30) TABLE_OWNER VARCHAR2(30) TABLE_
NAME VARCHAR2(30) COLUMN_NAME
VARCHAR2(4000) COLUMN_LIST
VARCHAR2(3) COLUMN_USAGE VARCHAR2(17)