Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL PL/SQL FOR ORACLE R. OULAD HAJ TH

BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL PL/SQL FOR ORACLE R. OULAD HAJ THAMI LES TRIGGERS BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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 BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL LES TRIGGERS (DECLENCHEURS) BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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 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 Utilisation des déclencheurs peuvent être utilisés pour : Définition BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL LES TRIGGERS LMD (DECLENCHEURS) BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Application INSERT INTO E_EMPLOYEES . . .; E_EMPLOYEES Déclencheur CHECK_SAL EVENEMENT EVENEMENT DECLENCHEUR?? TEMPS DE L’EXECUTION? APRES L’EVENEMENT DECLENCHEUR? AVANT? SUR TOUTE LA TABLE? SUR CHAQUE LIGNE??? QUESTIONS BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL CREATE [OR REPLACE] TRIGGER nom_trigger timing event1 [OR event2 OR event3] ON nom_table Corps_de_trigger Syntaxe: TRIGGER SUR TABLE 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 BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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 / BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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; BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL CREATE [OR REPLACE] TRIGGER nom_trigger timing event1 [OR event2 OR event3] ON nom_table FOR EACH ROW Corps_de_trigger Syntaxe: TRIGGER SUR LIGNE LE TRIGGER SERA EXECUTE POUR CHAQUE LIGNE DE LA TABLE CONCERNEE PAR L ‘EVENEMENT DECLENCHEUR BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Application INSERT INTO E_EMPLOYEES . . .; E_EMPLOYEES Déclencheur CHECK_SAL EVENEMENT LES ANCIENS VALEURS DE LA LIGNE SONT REFERENCEES PAR :OLD LES NOUVELLES VALEURS DE LA LIGNE SONT REFERENCEES PAR :NEW BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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 BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL SQL> select NO, SALAIRE FROM E_EMPLOYE; NO SALAIRE ---------- ---------- 1 15189,2 2 4000,5 3 4500,5 4 4300,98 5 4350,5 6 3500,98 7 5400,5 8 3000,5 9 4000,5 10 6000,34 10 ligne(s) sélectionnée(s). SQL> SQL> --test SQL> UPDATE E_EMPLOYE SET SALAIRE=1000 WHERE NO=1; UPDATE E_EMPLOYE SET SALAIRE=1000 WHERE NO=1 * ERREUR à la ligne 1 : ORA-20200: ERR: LE SALAIRE DOIT SUPERIEUR A L ANCIEN SALAIRE!! ORA-06512: à "RACHID.CHECK_SAL", ligne 3 ORA-04088: erreur lors d'exécution du déclencheur 'RACHID.CHECK_SAL' BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL TRIGGER SUR LIGNE: Exemple 2: TRACABILITE DES SUPPRESSIONS SUR UNE TABLE REGLE DE GESTION: CHAQUE EMPLOYE SUPPRIME DOIT ETRE ARCHIVE 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). BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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. BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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éé. BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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 BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL TRIGGER CLAUSE WHEN: Exemple 1: REGLE DE GESTION: SI LA DATE N’EST DONNEE, ON PREND LA DATE DU JOUR 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 BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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> BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL LES TRIGGERS vs VIEW BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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 BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL CREATE OR REPLACE VIEW <nom vue> [(liste des attributs)] AS <requête de sélection> [WITH CHECK OPTION | WITH READ ONLY] Syntaxe de CREATE VIEW 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 CHECK OPTION Aucune modification (INSERT, UPDATE) n’est possible WITH READ ONLY BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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; BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL Le SELECT principal de la vue contient 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 SELECT OUI OUI OUI NON UNION, INTERSETC, MINUS OUI NON NON NON SELECT UPDATE DELETE INSERT RESTRICTION SUR L’UTILISATION DES VUES BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL TRIGGERS sur VIEW BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL -INSERTION -SUPPRESSION -MODIFICATION Utilisation des triggers pour un traitement complet des VUES BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL CREATE [OR REPLACE] TRIGGER nom_trigger INSTEAD OF event1 [OR event2 OR event3] ON nom_table [FOR EACH ROW] Corps_de_trigger Syntaxe: TRIGGER SUR VUE BASES DE DONNEES RELATIONNELLES ORACLE PL/SQL 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 uploads/Management/ pl-sql-global-2018-vrai-pdf.pdf