Remerciez-le!

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

Triggers et vues Patricia Serrano Alvarado Université de Nantes Laboratoire LS2

Triggers et vues Patricia Serrano Alvarado Université de Nantes Laboratoire LS2N Triggers, what for ? ■Les contraintes permettent d’assurer une certaine cohérence sémantique et structurelle d’un attribut ou d’une table ■Unicité, clés, clés référentielles, check ■Comment assurer une cohérence sémantique plus complexe ■Lorsqu’une table est modifiée, générer la modification d’une autre ■Lorsqu’une valeur est insérée la modifier avant de la stocker dans la BD ■Etc. 2 Triggers ■Règle Événement-Condition-Action (ECA) ■Événement ■Insert, delete, update sur une table ou vue ■Condition ■Test ou prédicat logique ■Action ■Si la condition est satisfaite, code PL/SQL à exécuter 3 4 Trigger ■Sont gérés au niveau du SGBD ■Se déclenchent quelque soit l’outil utilisée (sqlplus, formes, applications java, PHP , etc.) 5 Utilité des triggers ■Générer automatiquement des valeurs ■Prévenir de manipulations non valides ■Renforcer la sécurité ■Renforcer l’intégrité référentielle des nœuds dans une base de données répartie ■Fournir une journalisation transparente d’événements ■Fournir auditing ■Maintenir de manière synchrone de tables dupliquées ■Obtenir des statistiques sur l’accès aux tables ■Modifier une table lorsqu’une opération DML est réalisée sur une vue ■Etc. 6 Type de triggers (1) Les événements courants (DML): ■INSERT ■S'exécute à chaque opération d'insertion lancée par l'utilisateur ou par un programme ■UPDATE ■S'exécute à chaque opération de mise à jour lancée par l'utilisateur ou par un programme ■DELETE ■S'exécute à chaque opération de suppression lancée par l'utilisateur ou un programme 7 Type de triggers (2) ■On peut spécifier si le trigger se déclenche une fois pour tous les tuples d’une requête(statement- level) ou une fois par tuple (row-level) ■Un trigger peut être déclenché avant (BEFORE), après (AFTER) ou à la place (INSTEAD OF) d’une opération DML ■Les attributs des tables/vues sont accessibles à travers les variables NEW et OLD (utiliser avec row-level) 8 Syntaxe Les parties d’un trigger ■EVENT ■Trigger statement (CREATE AFTER|BEFORE…) ■CONDITION ■Trigger restriction (WHEN…) ■ACTION ■Triggered action (FOR EACH…/) 9 10 Action d’un trigger ■Comme dans les procédures stockées l’action d’un trigger peut ■Contenir de sentences SQL, PL/SQL ou Java ■Définir de constructeurs PL/SQL comme variables, constantes, curseurs, exceptions ■Définir constructeurs Java ■Faire appel à de procédures stockées Combinaison des triggers ■Les types de triggers peuvent être combinés dans un seul trigger ■Un « if » dans le block peut déterminer quelles commandes déclenchent le type de trigger ■Les colonnes concernées peuvent être spécifiées pour le type UPDATE 11 12 Maintenance de triggers (1) ■Création/suppression d’un trigger ■CREATE TRIGGER nom_déclencheur; ■REPLACE TRIGGER nom_déclencheur; ■DROP TRIGGER nom_déclencheur; ■Activation/désactivation d'un trigger ■ALTER TRIGGER nom_déclencheur DISABLE; ■ALTER TRIGGER nom_déclencheur ENABLE; ■Activer/désactiver tous les triggers définis sur une table ■ALTER TABLE nom_table DISABLE ALL TRIGGERS; ■ALTER TABLE nom_table ENABLE ALL TRIGGERS; 13 Maintenance de triggers (2) ■Les informations sur les triggers sont visibles à travers les vues du dictionnaire de données ■USER_TRIGGERS pour les triggers appartenant au schéma ■ALL_TRIGGERS pour les triggers appartenant aux schémas accessibles ■DBA_TRIGGERS pour les triggers appartenant à tous les schémas Conditions d’erreurs ■Dans un trigger, différentes conditions d’erreur peuvent être définies à travers la procédure RAISE_APPLICATION_ERROR ■Le numéro d’erreur peut varier de 20001 à 20999 ■Si une erreur est levée, l’événement du trigger ne se réalise pas 14 Exemple 15 16 Appel à procédures ■A partir d’un trigger de procédures peuvent être appelées 17 Triggers vs contraintes d’intégrité ■Possibilité d’utilisation dans les mêmes situations ■Utiliser les triggers lorsque l’utilisation de contraintes n’est pas possible ■Lorsqu’il est possible utiliser les contraintes ■NOT NULL, UNIQUE ■PRIMARY KEY ■FOREIGN KEY ■CHECK ■DELETE CASCADE ■DELETE SET NULL 18 Triggers en cascade ■Faire attention au déclenchement en cascade des triggers 19 Ordre dans le déclenchement ■Par défaut l’ordre suivant : 1.All BEFORE statement triggers 2.All BEFORE row triggers 3.All AFTER row triggers 4.All AFTER statement triggers ■Clause FOLLOWS pour triggers du même type : CREATE OR REPLACE TRIGGER <trigger_name> [FOLLOWS <schema.trigger_name>] 20 Misellaneous ■Les nouvelles valeurs peuvent être modifiées :new.UpperName:=UPPER(:new.name); ■Les fonctions UID, USER, USERENV, and SYSDATE peuvent être utilisées dans le block PL/SQL ■Il est conseillé que le nom d’un trigger indique la table sur laquelle il porte ainsi que le type de trigger 21 USERENV Function Related Links: Oracle SYS_CONTEXT Function Oracle Miscellaneous Functions: USERENV 22 Triggers utilisateur et système ■Depuis la version Oracle8i, il est possible d'utiliser des déclencheurs pour suivre les changements d'état du système ainsi que les connexions/déconnexions utilisateur et la surveillance des ordres DDL et DML ■Lors de l'écriture de ces déclencheurs, il est possible d'utiliser des attributs pour identifier précisément l'origine des évènements et adapter les traitements en conséquence 23 Attributs (1) ■ ora_client_ip_adress Adresse IP du poste client qui se connecte ■ ora_database_name Nom de la base de données ■ ora_des_encrypted_password Description codée du mot de passe de l'utilisateur créé ou modifé ■ ora_dict_obj_name Nom de l'objet visé par l'opération DDL ■ ora_dict_obj_name_list Liste de tous les noms d'objets modifiés ■ ora_dict_obj_owner Propriétaire de l'objet visé par l'opération DDL ■ ora_dict_obj_owner_list Liste de tous les propriétaires d'objets modifiés ■ ora_dict_obj_type Type de l'objet visé par l'opération DDL ■ ora_grantee Liste des utilisateurs disposant du privilège ■ ora_instance_num Numéro de l'instance ■ ora_is_alter_column Vrai si la colonne en paramètre a été modifiée 24 Attributs (2) ■ ora_is_creating_nested_table Création ou non d'une table de fusion ■ ora_is_drop_column Modification ou non de la colonne en paramètre ■ ora_is_servererror Vrai si le numéro erreur passé en paramètre se trouve dans la pile des erreurs ■ ora_login_user Nom de la connexion ■ ora_privileges Liste des privilèges accordés ou retirés par un utilisateur ■ ora_revokee Liste des utilisateurs à qui le privilège a été retiré ■ ora_server_error Numéro d'erreur dans la pile dont la position est passée en paramètre ■ ora_sysevent Nom de l'évènement système qui a activé le déclencheur ■ ora_with_grant_option Vrai si le privilège a été accordé avec option d'administration 25 Triggers système ■CREATE TRIGGER nom_déclencheur {BEFORE|AFTER} évènement_système ON{DATABASE|SCHEMA} bloc PL/ SQL ■Événements système ■STARTUP ■Évènement déclenché lors de l'ouverture de l'instance (type AFTER seulement) ■SHUTDOWN ■Évènement déclenché avant le processus d'arrêt de l'instance (non déclenché en cas d'arrêt brutal du serveur) (type BEFORE seulement) ■SERVERERROR ■Évènement déclenché lors d'une erreur Oracle (sauf ORA-1034, ORA-1403, ORA-1422, ORA-1423 et ORA-4030) (type AFTER seulement) 26 Trigger utilisateur (1) ■CREATE TRIGGER nom_déclencheur {BEFORE|AFTER} évènement_utilisateur ON{DATABASE|SCHEMA} bloc PL/SQL ■Événements utilisateur ■LOGON Après une connexion (AFTER seulement) ■LOGOFF Avant une déconnexion (BEFORE seulement) ■CREATE Lors de la création d'un objet ■ALTER Lors de la modification d'un objet ■DROP Lors de la suppression d'un objet ■ANALYZE Lors de l'analyse d'un objet ■ASSOCIATE STATISTICS Lors de l'association d'une statistique 27 Trigger utilisateur (2) ■AUDIT Lors de la mise en place d'un audit ■NOAUDIT Lors de l'annulation d'un audit ■COMMENT Lors de l'insertion d'un commentaire ■DDL Lors de l'exécution des ordres DDL (sauf ALTER DATABASE, CREATE CONTROLFILE et CREATE DATABASE) ■DISSOCIATE STATISTICS Lors de la dissociation d'une statistique ■GRANT Lors de l'exécution d'une commande GRANT ■RENAME Lors de l'exécution d'une commande RENAME ■REVOKE Lors de l'exécution d'une commande REVOKE ■TRUNCATE Lors d'une troncature de table Pour terminer qqs remarques ■Un déclencheur a un nom. ■Il est associé à une table et une seule. ■Il peut être actif ou inactif. ■Il est opérationnel jusqu’à sa suppression ou la suppression de la table à laquelle il est associé. ■Il n’est pas modifiable. 28 29 Inconvénients ■L’utilisation de triggers peut ralentir l’exécution des requêtes notamment lors de la manipulation massive de données 30 A vous de jouer ! Considérer la table nombres(a:int, b:int) et le trigger suivant Quel sera l’état de numbres après l’insertion suivante : INSERT INTO nombres VALUES (3,5); a b Les vues ■Une introduction 31 Les vues, what for? ■Indépendance entre la couche SGBD et la couche applicative ■Facilité d’usage de la BD ■Facilité et amélioration du contrôle de la confidentialité ■Amélioration des performances 32 Vue ■Une vue est une requête stockée qui est interrogée comme une table ■Les contenu (tuples) de la vue ne sont pas stockées par défaut ■La vue facilite la vie du programmeur, pas du SGBD… ■Pendant l’exécution des requêtes, les vues seront remplacées par les tables dont la vue dépend 33 Création de vues CREATE VIEW <nom-vue> AS <requête définissant le vue> ■Exemple Considérer la table : Movies(title, year, length, genre, studioName, producerC) CREATE VIEW ParamountMovies AS SELECT title, year FROM Movies WHERE studioName=‘Paramount’; 34 Interrogation de vues ■On interroge une vue comme on interroge une table ■Pour l’exécution, le SGBD enrichi la requête de l’utilisateur avec la définition de la vue ■2 techniques ■Ré-écriture de requêtes ■Concaténation d’arbres relationnels 35 Mise à jour de vues ■Est-il envisageable de « mettre à jour » une vue ? i.e., requêtes INSERT|DELETE|UPDATE ■Pour certaines vues oui ■Il est possible de « traduire » la mise à jour sur la vue vers les tables dont la vue dépend ■WITH CHECK OPTION: les mises à jour ou les insertions faites à travers la vue ne produisent que des lignes qui font partie de la sélection de la vue ■Pour la majorité des vues non ■Exemple ■INSERT INTO ParamountMovies values ("La môme",2007) 36 Règles SQL pour faire des vues modifiables ■En gros, dans la création des vues 1. uploads/Finance/ cm5-psa-triggers-vues.pdf