Les étapes suivantes montrent comment un déclencheur AFTER est lancé lors d’un
évènement sur la table ou il est défini.
Cas 1 : évènement INSERT
· Une instruction INSERT est exécutée sur une table comportant un déclencheur INSERT.
· L’instruction INSERT est journalisée dans le journal des transactions le record NEW reçoit la
copie de la ligne ajoutée à la table.
· Le déclencheur est lancé et ses instructions s’exécutent.
Cas 2 : évènement DELETE
· Une instruction DELETE est exécutée sur une table comportant un déclencheur DELETE.
· L’instruction DELETE est journalisée dans le journal des transactions le record OLD reçoit la
copie de la ligne supprimée de la table.
· Le déclencheur est lancé et ses instructions s’exécutent.
Cas 3 : évènement UPDATE
· Une instruction UPDATE est exécutée sur une table comportant un déclencheur UPDATE.
· L’instruction UPDATE est journalisée dans le journal des transactions sous la forme INSERT
et DELETE le record OLD reçoit la copie de la ligne de la table représentant l’image avant la
modification. Le record NEW reçoit la copie de la ligne de la table représentant l’image après
la modification.
· Le déclencheur est lancé et ses instructions s’exécutent.
Exemple : Création d’un déclencheur AFTER Vente_Insert sur l’instruction
INSERT de la table Ventes de structure
VENTES (vnt_art, vnt_cli, vnt_qte, vnt_prix)
Lorsqu’une ligne est insérée dans la table Ventes, le déclencheur décrémente la colonne
quantité en stock dans la table ARTICLES de la quantité vendue ;
ARTICLES (art_num, art_nom, art_coul, art_pa, art_pv, art_qte, art_frs)
CREATE TRIGGER Vente_Insert
AFTER INSERT
ON Ventes
For each row
152
BEGIN
UPDATE Article SET Art_Qte = Art_Qte – :new.Vnt_Qte
Where article.Art_Num. = :new.Vnt_Art
END ;
Dans l’exemple ci-dessus, le record new contient la ligne de Ventes qui vient d’être ajoutée,
et les colonnes de la table Vente sont manipulables à travers le record news. Un déclencheur
peut être défini avec l’instruction IF UPDATE, qui contrôle la mise à jour d’une colonne
donnée.
Les déclencheurs INSTEAD OF
Le principe de fonctionnement des déclencheurs INSTEAD OF est simple : l’instruction
appelante est interceptée, donc non réellement exécutée, et le code du déclencheur la
remplace : Il est ainsi possible de tester les valeurs insérées, mises à jour ou supprimées
pour décider de la suite des opérations.
Les déclencheurs INSTEAD OF peuvent être définis sur des vues : Un déclencheur sur une
vue permet d’étendre la possibilité de mie à jour de vues multi tables. Un seul déclencheur
INSTEAD OF par instruction est autorisé sur une table ou vue.
Exemple 1 : Trigger sur un événement de suppression (insteadOf). Pour les besoins de notre
exemple, on effectue les requêtes suivantes :
-- Test de suppression logique
create table personne (idp number, nom varchar(50), suppression number);
create view personneView as select * from personne;
insert into personne values (1,'pierre',0);
insert into personne values (2,'paul',0);
insert into personne values (3,'jacques',0);
Créons maintenant le trigger « supPersonne ».
153
Voici le code complet de notre trigger.
CREATE OR REPLACE TRIGGER supPersonne
INSTEAD OF DELETE
ON PersonneView
FOR EACH ROW
BEGIN
UPDATE personne
SET suppression = 1
WHERE idp = :old.idp;
END supPersonne;
Pour le tester, effectuons la requête suivante :
-- Une fois le trigger mis en place, le delete sera fait sur la vue
delete personneView where idp = 1;
Maintenant, si on affiche les données dans la vue, on constate avoir effectué une suppression
sur la personne ayant un id à 1.
154
Exemple 2 : Création d’un déclencheur INSTEAD OF Insert_Multiple sur l’instruction
INSERT de la vue multi tables, Vue_TousClients qui regroupent les clients français et
étrangers. Lorsqu’une ligne est insérée, le déclencheur met à jour les tables concernées.
ClientsF et ClientsE.
CREATE TRIGGER Insert_Multiple
INSTEAD OF INSERT
ON Vue_TousClients
FOR EACH ROW
BEGIN
If (select payC from new) = 'F'
Insert ClientsF select * from new
ELSE
Insert ClientsE select * from new
END
Trigger sur le schéma
On veut garder la trace des événements qui se sont produits sur le schéma (create, alter ...).
On crée la table qui stockera l’information :
create table auditDDL (
d date,
osUser varchar2(255),
curUser varchar2(255),
host varchar2(255),
owner varchar2(30),
typ varchar2(30),
nom varchar2(30),
sysevent varchar2(30));
Et on crée le trigger « trDDL ».
create or replace trigger trDDL
after ddl on schema
begin
if (ora_sysevent='TRUNCATE') or (ora_sysevent='DROP')
then null;
else
insert into auditDDL(d, osUser,curUser,host,owner,typ,nom,sysevent)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent );
end if;
155
end;
L’action suivante revient à la même que si vous cliquez sur la petite disquette pour
enregistrer. À noter que la compilation permet de détecter les éventuelles erreurs. Cliquez
droit et « Compiler » sur le code.
Pour tester notre trigger, exécutons les requêtes suivantes :
-- les requêtes create et alter sont stockées dans auditDDL
create table produit2 (idProd number, nomProd varchar(50));
alter table produit2 add constraint PKProd primary key (idProd);
-- la requête drop n'est pas stockée
drop table produit2;
Enfin, vérifions notre table « auditDLL » qui a dû lister les opérations effectuées :
156
select * from auditDDL;
Le débogage dans « Oracle SQL Developer »
« Oracle SQL Developer » prend également en charge le débogage de PL / SQL des bases
de données Oracle. Nous allons effectuer un exemple de débogage à partir d’un déclencheur
précédemment créé. Cliquez droit sur le déclencheur « LST_COMMANDES » puis
« Modifier ».
Pour aider au débogage, les numéros de ligne peuvent être ajoutés à la fenêtre Code. Cliquez
droit sur la marge et « Activer / désactiver les numéros de ligne ».
Les numéros de lignes s’affichent alors :
157
Vous pouvez définir un point d’arrêt en cliquant dans la marge, au niveau de la ligne ou vous
souhaitez positionner un point d’arrêt. Pour cela, cliquer droit sur la ligne concernée puis
« Activer / désactiver le point d’interruption ».
Après avoir sélectionné l’option, votre point d’arrêt s’affiche.
Cliquez sur l'icône « Debug ».
158
Un écran de débug s’affiche alors. Cliquez sur « Ok ».
Le débogueur doit s'arrêter à la ligne où vous avez placé le point d'arrêt. Vous pouvez
maintenant contrôler le flux d'exécution, modifier les valeurs des variables et des fonctions de
débogage…
159
Pour aller à la ligne suivante, cliquez sur le bouton suivant :
D’autres options sont disponibles comme « Exécuter jusqu’au curseur » en cliquant droit
sur la ligne correspondante.
Vous voyez les valeurs des champs dans l’onglet « Données ».
160
Cliquez sur « reprise » pour poursuivre.
161
FAQ
Comment compter le nombre d'occurrences dans une chaîne sans l'aide d'une
boucle itérative ? En utilisant les fonctions LENGTH() et REPLACE() de la façon suivante :
CREATE OR REPLACE FUNCTION CPT_OCCURRENCES
(
PC$Entree IN VARCHAR2, -- chaîne en entrée
PC$Recherche IN VARCHAR2 -- Chaîne à rechercher
) RETURN PLS_INTEGER IS
BEGIN
RETURN ( (LENGTH(PC$Entree) -
LENGTH(REPLACE(PC$Entree,PC$Recherche,NULL)) ) /
NVL(LENGTH(PC$Recherche),1) ) ;
END;
Testons notre fonction :
SELECT CPT_OCCURRENCES( 'le et le et le', 'le' ) "Nombre d'occurrences"
FROM DUAL ;
Comment savoir si une chaîne de caractères correspond à un nombre ? Plutôt que de
coder une fonction qui traite tous les cas de figure correspondant aux différents formats que
peut prendre un nombre (entier, nombre à virgule, nombre avec exposant...), il vaut mieux
laisser Oracle gérer la conversion et nous signaler s'il a rencontré une erreur comme dans la
fonction suivante :
create or replace function isNumeric(x in varchar2) return number as
-- renvoie 1 si le paramètre correspond à un nombre
-- 0 sinon
nb number;
begin
nb := to_number(x);
return 1;
exception
when others then
return 0;
end;
Exemple d'utilisation : Si la colonne X correspond à un nombre, on veut avoir ce nombre
multiplié par 1000 :
SELECT x,
isNumeric(x),
CASE
WHEN isNumeric(x) = 1 THEN 1000 * TO_NUMBER(x)
END x_fois_1000
FROM TEST;
162
Comment vérifier si une chaîne vérifie un certain format ? Pour vérifier qu'une chaîne
de caractère vérifie un format donné, on peut utiliser, à partir d'Oracle 10g, la fonction
MATCH du OWA_PATTERN qui permet de manipuler des expressions régulières. Cette
fonction retourne un booléen indiquant si le format est vérifié ou pas; elle ne peut donc être
appelée qu'en PL/SQL. Par exemple, pour vérifier que les numéros de téléphone en base
suivent le format français sur 10 chiffres avec comme séparateur des tirets, on peut faire :
Les expressions régulières peuvent utiliser les symboles suivants :
•^ : Début de la ligne
•$ : Saut de ligne ou fin de ligne
•\n : Saut de ligne
•. : Tout caractères sauf le saut de ligne
•\t : Tabulation
•\d : Chiffre (équivalent à [0-9])
•\D : Tout caractère sauf un chiffre (équivalent à [not 0-9])
•\w : Tout caractère alphanumérique (chiffres, lettres, _)
•\W : Tout caractère sauf un caractère alphanumérique
•\s : Tout espace (espace, tabulation, saut de ligne)
•\S : Tout caractère sauf un espace
•\b : Délimiteur de mots (entre un caractère qui vérifie \w et un autre qui vérifie \W)
•\xnn : Caractère dont le code ascii est en hexadécimal nn
•\nnn : Caractère dont le code ascii est en octal nnn
Les éléments ci-dessus peuvent être suivis par les indicateurs de cardinalité suivants :
•? : 0 ou 1 occurrence
•* : 0 ou plus occurrences
•+ : 1 ou plus occurrences
•{n} : Exactement n occurences
•(n,} : Au moins n occurences
•{n,m} : Entre n et m occurrences
163
L'expression régulière qu'on a utilisée pour décrire notre format de numéro de téléphone
^\d{2}-\d{2}-\d{2}-\d{2}-\d{2}$ se décrypte donc de la manière suivante :
•\d{2} : On cherche deux chiffres
•- : Suivis d'un tiret
•\d{2}- : Suivi de deux chiffres et d'un tiret
•\d{2}- : Suivi de deux chiffres et d'un tiret
•\d{2}- : Suivi de deux chiffres et d'un tiret
•\d{2} : Suivi de deux chiffres
Comme notre expression régulière commence par ^, elle décrit le début de la chaîne; comme
elle se termine par $, elle décrit aussi sa fin.
Comment générer un fichier de trace pour une session en cours ? Quand une
procédure est en cours d'exécution, il est possible de générer un fichier de trace (pour
exploitation par TkProf par exemple) en utilisant la routine set_sql_trace_in_session du
package DBMS_SYSTEM. Les paramètres de cette routine sont les suivants :
• SID : Identifiant de la session en provenance de V$SESSION
• SERIAL# : Second identifiant de la session en provenance de V$SESSION
• TRACE : Booléen. Il faut entrer TRUE pour demander à Oracle de générer le fichier de trace,
et FALSE pour arrêter sa génération.
Comment convertir une valeur hexadécimale en valeur décimale ? Simplement grâce à
la fonction TO_NUMBER : En voici un exemple d'utilisation.
SELECT
TO_NUMBER('ff','XXXXXXXX'),TO_NUMBER('fff','XXXXXXXX'),TO_NUMBER('ffff','XX
XXXXXX') from dual;
Comment crypter mon code ? Il est parfois nécessaire, lors du déploiement de vos
applicatifs, de crypter votre code afin de le protéger : il suffit pour cela de sauvegarder votre
code sous forme de fichiers sql et d'utiliser le programme « wrap.exe », se trouvant dans le
répertoire {Oracle_Home}\Bin\.
Le programme wrap accepte les 2 arguments suivants :
• iname : Nom du fichier en entrée
• oname : Nom du fichier de sortie
Voici un exemple avec le fichier suivant :
CREATE OR REPLACE FUNCTION debut
(PC$Chaine IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN SUBSTR( PC$Chaine, 1, 5 ) ;
END;
Encryptons-le :
...\BIN> wrap iname=c:\temp\debut.pls oname=c:\temp\debut.plb
164
PL/SQL Wrapper: Release 10.2.0.1.0- Production on Dim. Oct. 15 23:52:50
2006
Copyright (c) 1993, 2004, Oracle. All rights reserved.
Processing c:\temp\debut.pls to c:\temp\debut.plb
Vous pouvez ouvrir le nouveau fichier pour vérifier son contenu.
Attention, prenez évidemment soin de sauvegarder vos sources, cette procédure est bien
entendu irréversible ! Vous pouvez (devez) également vérifier que la compilation s'effectue
correctement :
@c:\temp\debut.plb
Attention, ceci ne fonctionne pas sur les triggers ou les blocs PL/SQL anonymes.
165
