BASES DE DONNEES RELATIONNELLES
PL/SQL FOR ORACLE
R. OULAD HAJ THAMI
LES TRIGGERS
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SOMMAIRE GENERAL
MOTIVATIONS
STRUCTURE D’UN BLOC PL/SQL
LES VARIABLES
LES ENREGISTREMENTS
ASSIGNATION DES VARIABLES ET AFFECTATION
STRUCTURES DE CONTRÔLE
LES COLLECTIONS
LES TRANSACTIONS
INSERT-UPDATE-DELETE DANS UN BLOC PL/SQL
GESTION DES ERREURS ET DES EXCEPTIONS
LES CURSEURS
LES PROCEDURES ET LES FOCNTIONS STOCKEES
LES PACKAGES
LES TRIGGERS
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES
TRIGGERS
(DECLENCHEURS)
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Définition
Un déclencheur est un programme qui se déclenche automatiquement suite à un évènement
sur une table, une base, une application ou un évènement système
Utilisation des déclencheurs peuvent être utilisés pour :
la sécurité
l'audit
l'intégrité des données
l'intégrité référentielle
la réplication de table
le calcul automatique des données dérivées
la consignation des événements
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES
TRIGGERS LMD
(DECLENCHEURS)
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
INSERT INTO E_EMPLOYEES
Application
. . .;
EVENEMENT Déclencheur
CHECK_SAL
E_EMPLOYEES
QUESTIONS
EVENEMENT DECLENCHEUR??
TEMPS DE L’EXECUTION? APRES L’EVENEMENT DECLENCHEUR? AVANT?
SUR TOUTE LA TABLE? SUR CHAQUE LIGNE???
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Syntaxe: TRIGGER SUR TABLE
CREATE [OR REPLACE] TRIGGER nom_trigger
timing
event1 [OR event2 OR event3]
ON nom_table
Corps_de_trigger
timing ::= BEFORE | AFTER
Event ::= INSERT|DELETE|UPDATE
INSERT IF INSERTING THEN ….
DELETE IF DELETING THEN …
UPDATE IF UPDATING(‘ATTRIBUT’) THEN … (modification de l’attribut)
IF UPDATING THEN … (n’importe quelle modification sur la table)
LE TRIGGER SUR LA TABLE EST DECLENCHE UNE SEULE FOIS SUITE A UN EVENEMENT SUR
LA TABLE
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR TABLE: Exemple 1:
SQL> CREATE OR REPLACE TRIGGER ACCES_EMP
2 BEFORE INSERT ON E_EMPLOYE
3 BEGIN
4 IF (TO_CHAR (sysdate,'DY') IN (‘SAT',‘SUN'))
5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN
6 '08' AND '18'
7 THEN RAISE_APPLICATION_ERROR (-20500,
8 'Vous ne pouvez pas utiliser la table E_EMPLOYE
que pendant les heures normales.');
10 END IF;
11 END;
12 /
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR TABLE: Exemple 2:
CREATE OR REPLACE TRIGGER ACCES_EMP
BEFORE INSERT OR UPDATE OR DELETE ON E_EMPLOYES
BEGIN
IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')
THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR (-20502,‘Vous ne pouvez pas supprimer
dans la table E_EMPLOYE que pendant les heures normales.');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,'Vous ne pouvez pas ajouter dans
la table E_EMPLOYE que pendant les heures normales.');
ELSIF UPDATING ('SALAIRE') THEN
RAISE_APPLICATION_ERROR (-20503,'Vous ne pouvez pas modifier le
SALAIRE dans la table E_EMPLOYE que pendant les heures normales.');
ELSE
RAISE_APPLICATION_ERROR (-20504, ‘Vous ne pouvez pas
modifier la table E_EMPLOYE que pendant les
heures normales. ');
END IF;
END IF;
END;
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Syntaxe: TRIGGER SUR LIGNE
CREATE [OR REPLACE] TRIGGER nom_trigger
timing
event1 [OR event2 OR event3]
ON nom_table
FOR EACH ROW
Corps_de_trigger
LE TRIGGER SERA EXECUTE POUR CHAQUE LIGNE DE LA TABLE
CONCERNEE PAR L ‘EVENEMENT DECLENCHEUR
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
INSERT INTO E_EMPLOYEES
Application
. . .;
EVENEMENT Déclencheur
CHECK_SAL
E_EMPLOYEES
LES ANCIENS VALEURS DE LA LIGNE SONT REFERENCEES PAR :OLD
LES NOUVELLES VALEURS DE LA LIGNE SONT REFERENCEES PAR :NEW
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR LIGNE: Exemple 1:
SQL>
SQL> CREATE OR REPLACE TRIGGER CHECK_SAL
2 BEFORE UPDATE ON E_EMPLOYE
3 FOR EACH ROW
4 BEGIN
5 IF :NEW.SALAIRE<:OLD.SALAIRE
6 THEN RAISE_APPLICATION_ERROR(-20200,'ERR: LE SALAIRE DOIT SUPERIEUR A L
ANCIEN SALAIRE!!');
7 END IF;
8 END;
9 /
Déclencheur créé.
SQL>
REGLE DE GESTION: UN SALAIRE NE DOIT JAMAIS BAISSER
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SQL> select NO, SALAIRE FROM
E_EMPLOYE;
NO SALAIRE
---------- ----------
1 15189,2
2 4000,5
3 4500,5 SQL>
4 4300,98 SQL> --test
5 4350,5 SQL> UPDATE E_EMPLOYE SET SALAIRE=1000
6 3500,98 WHERE NO=1;
7 5400,5 UPDATE E_EMPLOYE SET SALAIRE=1000 WHERE
8 3000,5 NO=1
9 4000,5 *
10 6000,34 ERREUR à la ligne 1 :
ORA-20200: ERR: LE SALAIRE DOIT SUPERIEUR A
10 ligne(s) sélectionnée(s). L ANCIEN SALAIRE!!
ORA-06512: à "RACHID.CHECK_SAL", ligne 3
ORA-04088: erreur lors d'exécution du
déclencheur 'RACHID.CHECK_SAL'
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR LIGNE: Exemple 2: TRACABILITE DES SUPPRESSIONS SUR UNE TABLE
SQL> SELECT NO, NOM, PRENOM, DT_ENTREE, SALAIRE FROM E_EMPLOYE;
NO NOM PRENOM DT_ENTRE SALAIRE
--- -------- -------- -------- ----------
1 Alaoui Said 01/01/95 4500.50
2 Filali Mohammed 11/01/95 4021.87
3 Hayani Mourad 13/02/97 4500.50
4 Ansari Zouhair 25/01/96 4323.95
5 Naciri Abdallah 01/11/96 4373.74
6 Rabii Khalid 09/01/95 3519.69
7 Touzani Said 06/01/96 5400.50
8 ElBasri Samir 11/01/98 3000.50
9 Bahja Salah 01/01/03 4000.50
10 Bahja Brahim 19/01/95 6000.34
10 ligne(s) sélectionnée(s).
REGLE DE GESTION: CHAQUE EMPLOYE SUPPRIME DOIT ETRE ARCHIVE
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR LIGNE: Exemple 2: TRACABILITE DES SUPPRESSIONS SUR UNE TABLE
SQL> CREATE SEQUENCE SEQ_TR_EMP START WITH 1;
Séquence créée.
SQL>
SQL> CREATE TABLE TRACE_EMPS(
2 NUMOP NUMBER(7),
3 DT_OP DATE,
4 OPERAT VARCHAR2(20),
5 NOEMP NUMBER(7),
6 NOMEMP VARCHAR2(50),
7 PREEMP VARCHAR2(25),
8 DTENTREMP DATE,
9 TITREEMP VARCHAR2(25),
10 SER_NOEMP NUMBER(7),
11 COMMENTAIRE VARCHAR2(200),
12 SALEMP NUMBER(11,2),
13 OLD_PCT_COMM NUMBER(4,2)
14 );
Table créée.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR LIGNE: Exemple 2: TRACABILITE DES SUPPRESSIONS SUR UNE TABLE
SQL>
SQL> CREATE OR REPLACE TRIGGER TRACE_EMPS
2 AFTER DELETE ON E_EMPLOYE
3 FOR EACH ROW
4 BEGIN
5 IF DELETING THEN
6 INSERT INTO TRACE_EMPS VALUES(
7 SEQ_TR_EMP.NEXTVAL,
8 SYSDATE,
9 USER,
10 :OLD.NO,
11 :OLD.NOM,
12 :OLD.PRENOM,
13 :OLD.DT_ENTREE,
14 :OLD.TITRE,
15 :OLD.SERVICE_NO,
16 :OLD.COMMENTAIRE,
17 :OLD.SALAIRE,
18 :OLD.PCT_COMMISSION
19 );
20 END IF;
21 END;
22 /
Déclencheur créé.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR LIGNE: Exemple 2: TRACABILITE DES SUPPRESSIONS SUR UNE TABLE
SQL> --test
SQL> DELETE E_EMPLOYE WHERE NO IN (7, 8, 9,10);
4 ligne(s) supprimée(s).
SQL> COMMIT; --si on commite pas, les tuples ajoutés dans TRACE_EMPS seront vidés
2
SQL> SELECT NUMOP,DT_OP, OPERAT, NOEMP, NOMEMP, PREEMP FROM TRACE_EMPS;
NUMOP DT_OP OPERAT NOEMP NOMEMP PREEMP
----- -------- -------- ----- -------- --------
1 17/01/13 RACHID 7 Touzani Said
2 17/01/13 RACHID 8 ElBasri Samir
3 17/01/13 RACHID 9 Bahja Salah
4 17/01/13 RACHID 10 Bahja Brahim
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER CLAUSE WHEN: Exemple 1:
SQL> CREATE OR REPLACE TRIGGER INS_DT_EMPS
2 BEFORE INSERT ON E_EMPLOYE
3 FOR EACH ROW WHEN (NEW.DT_ENTREE IS NULL)
4 BEGIN
5 :NEW.DT_ENTREE:=SYSDATE;
6 END;
7 /
Déclencheur créé.
SQL>
PAS DE ‘:’ POUR LE OLD ET LE NEW DANS LA CLAUSE WHEN
REGLE DE GESTION: SI LA DATE N’EST DONNEE, ON PREND LA DATE DU JOUR
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER CLAUSE WHEN: Exemple 1:
SQL> --test
SQL> INSERT INTO E_EMPLOYE (NO, NOM, PRENOM, SALAIRE) VALUES
2 ( 555,
3 'toto',
4 'titi',
5 6000
6 );
1 ligne créée.
SQL> commit;
Validation effectuée.
SQL>
SQL> SELECT NO, NOM, PRENOM, SALAIRE, DT_ENTREE FROM E_EMPLOYE WHERE NO=555;
NO NOM PRENOM SALAIRE DT_ENTRE
---------- -------- -------- ---------- --------
555 toto titi 6000.00 18/01/13
SQL>
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES
TRIGGERS vs VIEW
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
LES VUES: DEFINITION
Table virtuelle calculée à partir d’autres tables ou vues par une requête
Pas d’existence physique mais recalculée chaque fois qu’elle est invoquée
Vue mono table
Vue multi-tables
Intérêts:
Indépendance application/données
Personnalisation des données selon les besoins des utilisateurs
Confidentialité
Rapidité des requêtes
Utilisation:
Pour les sélections, comme une table ordinaire
Pour les maj. (insert, update, delete), y a des restrictions
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Syntaxe de CREATE VIEW
CREATE OR REPLACE VIEW <nom vue> [(liste des attributs)]
AS <requête de sélection>
[WITH CHECK OPTION | WITH READ ONLY]
WITH CHECK OPTION
Permet de vérifier que les mises à jour ou les insertions faites à
travers la vue ne produisent que des lignes qui feront partie de la
sélection de la vue.
WITH READ ONLY
Aucune modification (INSERT, UPDATE) n’est possible
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Exemples:
CREATE VIEW MedecinsDeRabat AS
Select *
From DOC
Where villeDoc=‘Rabat’;
CREATE VIEW DocPat AS
Select NomDOc, NomPat
FROM DOC D, RDV R, PAT P
WHERE D.NumDoc=R. NumDoc and R.NumPat=P.NumPat;
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
RESTRICTION SUR L’UTILISATION DES VUES
Le SELECT principal de
UPDATE
SELECT
DELETE
la vue contient
INSERT
Plusieurs tables OUI NON NON NON
GROUP BY OUI NON NON NON
DISTINCT OUI NON NON NON
fonction de groupe OUI NON NON NON
Attribut calculé OUI NON OUI NON
Attribut NOT NULL pas dans le OUI OUI OUI NON
SELECT
UNION, INTERSETC, MINUS OUI NON NON NON
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGERS sur VIEW
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Utilisation des triggers pour un traitement complet des VUES
-INSERTION
-SUPPRESSION
-MODIFICATION
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
Syntaxe: TRIGGER SUR VUE
CREATE [OR REPLACE] TRIGGER nom_trigger
INSTEAD OF
event1 [OR event2 OR event3]
ON nom_table
[FOR EACH ROW]
Corps_de_trigger
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR VUE: Exemple 1:
SQL> --Exemple de trigger pour gérer les interdits sur une VUE
SQL>
SQL> create OR REPLACE view COM_CLIENT
2 (NUM_COM, NOM_CL, ADR_LIV, MONTANT_COM, DT_LIV_COM)
3 AS
4 SELECT
5 CO.NO AS NUM_COM,
6 CL.NOM AS NOM_CL,
7 CL.ADRESSE AS ADR_LIV,
8 CO.TOTAL AS MONTANT_COM,
9 CO.DATE_LIVRAISON AS DT_LIV_COM
10 FROM E_CLIENT CL, E_COMMANDE CO
11 WHERE CO.CLIENT_NO = CL.NO;
Vue créée.
SQL> CREATE SEQUENCE SEQ_ID_CLIENT START WITH 2000 ORDER;
Séquence créée.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRIGGER SUR VUE: Exemple 1:
SQL> --CREATE SEQUENCE SEQ_ID_COM START WITH 2000 ORDER;
SQL>
SQL> CREATE OR REPLACE TRIGGER TRIG_INS_VUE_COM_CLIENT
2 INSTEAD OF INSERT OR UPDATE OR DELETE ON COM_CLIENT
3 FOR EACH ROW
4 DECLARE
5 NEW_ID_CL NUMBER(5);
6 BEGIN
7 IF INSERTING THEN
8 SELECT last_number INTO NEW_ID_CL
9 FROM user_sequences WHERE sequence_name = 'SEQ_ID_CLIENT';
10
11 INSERT INTO E_CLIENT (NO, NOM, ADRESSE) VALUES (NEW_ID_CL,
:NEW.NOM_CL,:NEW.ADR_LIV );
12
13 INSERT INTO E_COMMANDE (NO, CLIENT_NO, TOTAL, DATE_LIVRAISON) VALUES
(:NEW.NUM_COM, NEW_ID_CL,:NEW.MONTANT_COM,:NEW.DT_LIV_COM);
14 END IF;
15 END;
16 /
Déclencheur créé.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
SQL> --test
SQL> INSERT INTO COM_CLIENT VALUES (
2 111,
3 'ouldbah',
4 'chez lui',
5 15000,
6 '25/01/2013'
7 ); SQL> SELECT * FROM E_COMMANDE WHERE NO=111;
1 ligne créée. NO CLIENT_NO DATE_COM DATE_LIV EMPLOYE_NO TOTAL
---------- ---------- -------- -------- ---------- ----------
SQL> commit; 111 2000 25/01/13 15000
Validation effectuée. SQL> SELECT * FROM E_CLIENT WHERE NOM='ouldbah';
SQL> NO NOM PRENOM TELEPHONE
---------- -------- -------- -------------------------
ADRESSE
--------------------------------------------------------------------------------
VILLE PAYS CP_PO COMMEN
------------------------------ ------------------------------ ----- ------
2000 ouldbah
chez lui
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
TRAVAUX
PRATIQUES
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
EXERCICE 1:
•Créer la table Trace_Salaire suivante :
Trace_Salaire( No_emp Number,
AncienSal Number(7,2),
NouveauSal Number(7,2),
Date_Modif Date,
Commentaire Varchar(30)
);
Le commentaire concerne le type d’opération (UPDATE, INSERT, DELETE)
1. Créer un trigger qui permet de constituer l’historique de toutes les modifications
apportées aux salaires des employés.
2. Créer un trigger qui met la date du jour de la création d’une ligne dans la colonne
Dt_Entree de la table E_employe, pour chaque ligne, si Dt_Entree est à NULL lors de
l’insertion.
3. Créer un trigger qui se déclenche sur la mise à jour de chaque ligne de la table
E_employe et qui renseigne la table E_augmentation.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
EXERCICE 2:
On considère le schéma suivant :
create table CAT_PROD(
NumCat NUMBER(3) PRIMARY KEY, --numéro de la catégorie du produit
PRIXACHATMAX NUMBER(5,2), --Prix maximum du produit à l’achat
MARGEMAX NUMBER(2) --Marge maximale à la vente
);
create table PRODS(
NumPROD Number(3) PRIMARY KEY, --numéro du produit
CatPROD Number(3) REFERENCES CAT_PROD(NumCat), --catégorie du produit
PrixACHAT Number(5,2), --Prix d’achat effectif du produit
PrixVENTE Number(5,2), --Prix de vente effective du produit
);
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
2.1. Ecrire un trigger qui vérifie lors de la mise à jour du prix de vente d’un produit que le prix
de vente ne dépasse pas la marge correspondant à la catégorie du produit, sinon, un message
d’erreur doit être retourné et la mise à jour avortée. De même, dans ce cas, on doit archiver
dans la table ARCH_PRODS les anciennes valeurs du produit et les nouvelles valeurs. Le trigger
doit aussi vérifier lors d’une promotion, que le prix de vente n’est pas inférieur au prix d’achat
et lors de l’achat du produit que le prix d’achat ne dépasse pas le prix maximum autorisé
(PRIXACHATMAX)
La table ARCH_PRODS est comme suit :
create table arch_emps(
NumPROD Number(3), -- numéro du produit concerné par la mise à jour
CatPROD Number(3), -- la catégorie du produit
PrixAchat Number(5,2), --le prix effectif d’achat du produit
OldPrixVente NUMBER(3), --l’ancien prix de vente
NewPrixVente NUMBER(3), --Nouveau prix de vente
);
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
2.2. Ecrire, un trigger qui, si le prix de vente n’est pas donnée lors de l’insertion, calcule
automatiquement ce prix selon la catégorie du produit et la marge maximale et l’insère dans le
tuple à insérer.
2.3. Créer une vue, contenant : NumCAT, PRIXACHATMAX, NumPROD, PrixAchat, PrixVente.
2.4.Ecrire un trigger qui autorise l’insertion et la suppression à travers la vue selon la
sémantique suivante :
a. Si NumCat=NULLERREUR OU PrixAchat=NULLERRUR SINON le prix de vente peut
être calculé comme précédemment et le numéro de produit par une séquence.
b. Lors d’une suppression, seul le produit sera supprimé et archivé.
ORACLE PL/SQL
BASES DE DONNEES RELATIONNELLES
FIN
ORACLE PL/SQL
