Lors de l’écriture de ces triggers, il est possible d’utiliser des attributs pour identifier
précisément l’origine de l’événement et adapter les traitements en conséquence.
· 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 modifié
· ora_dict_obj_name
Nom de l'objet visé par l'opération LDD
· 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 LDD
· ora_dict_obj_owner_list
Liste de tous les propriétaires d'objets modifiés
145
· ora_dict_obj_type
Type de l'objet visé par l'opération LDD
· 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
· 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ée 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
Les évènements système
· STARTUP Evènement déclenché lors de l'ouverture de l'instance (AFTER seulement)
· SHUTDOWN Evènement déclenché avant le processus d'arrêt de l'instance (non déclenché
en cas d'arrêt brutal du serveur) (BEFORE seulement)
· SERVERERROR Evènement déclenché lors d'une erreur Oracle (sauf ORA-1034, ORA-1403,
ORA-1422, ORA-1423 et ORA-4030) (AFTER seulement)
Les évènements utilisateur
· LOGON Après une connexion (AFTER seulement)
· LOGOFF Avant une déconnexion (BEFORE seulement)
146
· 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
· 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
· LDD Lors de l'exécution des ordres LDD (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
Déclencheurs LMD
Un déclencheur LMD (Data Manipulation Language) est un type spécial de procédure stockée
qui présente trois caractéristiques :
· Il est associé à une table
· Il s’exécute automatiquement lorsqu’un utilisateur essaie de modifier des données par une
instruction LMD (UPDATE, DELETE, INSERT) sur la table ou il est défini.
· Il ne peut être appelé directement.
Le déclencheur et l’instruction qui a provoqué son exécution sont traités comme une seule
transaction. Le bloc PL/SQL qui constitue le déclencheur (trigger) peut être exécuté avant ou
après vérification des contraintes d’intégrité. Les triggers offrent une solution procédurale
pour définir des contraintes complexes non exprimables avec le CREATE TABLE ou qui
prennent en compte des données issues de plusieurs lignes ou de plusieurs tables.
Exemple : Garantir qu’un client ne peut avoir que deux factures non payées.
Lorsque la contrainte d’intégrité peut être exprimée au niveau de la définition de la table,
c’est cette solution qui sera privilégiée, car la vérification est plus rapide que l’exécution du
trigger. De plus, les contraintes d’intégrités garantissent que toutes les lignes de la table
respectent ces contraintes, tandis que les triggers ne prennent pas en compte les données
déjà présentes dans la table lorsqu’ils sont définis.
Utilisation des déclencheurs LMD
Les déclencheurs servent à maintenir une intégrité référentielle de bas niveau et non à
envoyer des résultats de requête. Leur avantage principal réside dans le fait qu’ils peuvent
contenir une logique de traitement complexe. Ils doivent être employés lorsque les
contraintes n’offrent pas les fonctionnalités nécessaires. On pourra utiliser les déclencheurs,
par exemple pour :
· Modifier en cascade les tables liées dans une base de données
147
· Mettre en œuvre une intégrité des données plus complexe qu’une contrainte CHECK. À la
différence des contraintes CHECK, les déclencheurs peuvent référencer des colonnes d’autres
tables. Par exemple, dans une gestion commerciale, lorsque la commande d’un article est
passée, une ligne est insérée dans la table Lignes de Commandes. Un déclencheur INSERT
sur cette table pourra déterminer si la commande peut être livrée ou non, en examinant la
colonne quantité en stock dans la table Stock. Si cette valeur est insuffisante, il pourra
générer automatiquement un ordre de commande fournisseur et avertir le gestionnaire.
· Renvoyer des messages d’erreur personnalisés les règles, les contraintes et les valeurs par
défaut ne peuvent communiquer des erreurs que par l’intermédiaire des messages d’erreur
système standards.
Règles lors de l’utilisation de déclencheurs :
· Les déclencheurs sont réactifs alors que les contraintes ont un caractère préventif. Les
contraintes sont contrôlées en premier, les déclencheurs sont exécutés en réponse à une
instruction INSERT, UPDATE ou DELETE.
· Il est possible de créer des déclencheurs sur des vues, ceux-ci sont de type INSTEAD OF.
· Les déclencheurs ne doivent pas renvoyer de jeux de résultats.
· Le propriétaire de la table et les rôles admin peuvent créer et supprimer des déclencheurs
sur une table. De plus le créateur du déclencheur doit avoir la permission d’exécuter toutes
les instructions sur toutes les tables. Si l’une des permissions est refusée, la transaction est
annulée totalement.
· Les données de la table à laquelle est associé le TRIGGER sont inaccessibles depuis les
instructions du bloc. Seule la ligne en cours de modification est accessible à l’aide de deux
variables de type RECORD, OLD et NEW, qui reprennent la structure de la table ou de la vue
associée.
Ces variables peuvent être utilisées dans la clause WHEN du trigger ou dans le bloc
d’instruction. Dans ce dernier cas, elles sont référencées comme des variables hôtes avec le
préfixe « : ».
Exemple : :OLD.nom_champ, : NEW.nom_champ
Le terme OLD permet de connaître la ligne en cours de suppression dans un trigger DELETE
ou la ligne avant modification dans un trigger UPDATE. Le terme NEW permet de connaître
la nouvelle ligne insérée dans un trigger INSERT ou la ligne après modification dans le trigger
UPDATE.
· Les prédicats INSERTING, UDATING et DELETING permettent au sein d’un déclencheur
commun pour les instructions INSERT, UPDATE et DELETE de savoir si le bloc PL/SQL est
exécuté à la suite d’une insertion une modification ou d’une suppression. Ces prédicats
retournent une valeur booléenne et sont utilisés dans la condition de test d’une instruction IF.
Il est ainsi possible d’écrire un déclencheur commun aux instructions INSERT et UPDATE,
par exemple, tout en conservant l’exécution conditionnelle de certaines instructions.
148
Création de déclencheurs
La création de déclencheurs s’effectue à l’aide de l’instruction CREATE TRIGGER. Cette
instruction spécifie la table sur laquelle le déclencheur est défini, l’événement provoquant son
exécution et les instructions qu’il contient.
CREATE [or REPLACE] TRIGGER nom_trigger
[BEFORE/AFTER/INSTEAD OF]
{INSERT/UPDATE[OF col,…]/DELETE}
ON Nom_Table [FOR EACH ROW]
[WHEN (condition)] Bloc PL/SQL;
OR REPLACE, Remplace la description du TRIGGER s’il existe déjà.
BEFORE : Le bloc PL/SQL est exécuté AVANT la vérification des contraintes de la table et la
mise à jour des données.
AFTER : Le bloc PL/SQL est exécuté APRES la mise à jour des données dans la table.
INSTEAD OF : Le bloc PL/SQL qui suit remplace le traitement standard associé à l’instruction
qui a déclenché le trigger.
INSERT/UPDATE …/DELETE : Instruction associée au déclenchement du trigger. Plusieurs
instructions peuvent déclencher le même trigger. Elles sont combinées par l’opérateur OR.
FOR EACH ROW : Le trigger s’exécute pour chaque ligne traitée par l’instruction associée.
WHEN (condition) : La condition donnée doit être vérifiée pour que le code s’exécute.
Les déclencheurs
Pour créer un déclencheur sous « Oracle SQL Developer », cliquez droit sur
« Déclencheurs » puis « Nouveau déclencheur… ».
149
Définissez les critères qui vous semblent appropriés au déclencheur que vous souhaitez
réaliser.
Il ne vous reste plus qu’à écrire votre déclencheur à partir du modèle.
Pour les besoins de l’exemple suivant, nous créons les tables suivantes :
create table produitnouveau (idp integer, nomp char(20), qte number);
create table produitSave (ids integer, noms char(20));
Écrivons le déclencheur suivant : Le trigger se déclenche à chaque insertion de produit dans la
table « produitnouveau » et enregistre dans la table « produitSave » les produits insérer
dont la quantité sont supérieur à 100.
create or replace trigger tr_log
150
after insert on produitnouveau
for each row when (new.qte > 100)
BEGIN
Insert into produitSave values (:new.idp, :new.nomp);
END tr_log;
Puis enregistrez votre déclencheur.
Pour tester notre déclencheur, alimentons la table « produitnouveau ».
insert into produitnouveau values (1, 'produit 1', 50);
insert into produitnouveau values (2, 'produit 2', 120);
insert into produitnouveau values (3, 'produit 3', 70);
Et interrogeons nos tables :
Et notre table « produitsave » ou notre déclencheur à enregistré les produits, dont la
quantité et supérieur à 100. Donc uniquement le produit 2.
151
