Transcript 9 - Free

Créer des déclencheurs
de base de données
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Objectifs
A la fin de ce chapitre, vous pourrez :
9-2
•
•
décrire différents types de déclencheur
•
•
créer des déclencheurs de base de données
•
supprimer des déclencheurs de base de données
décrire les déclencheurs de base de données et
leur utilisation
décrire les règles d'activation des déclencheurs de
base de données
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Types de déclencheur
Un déclencheur :
9-3
•
est une procédure ou un bloc PL/SQL associé à la
base de données, à une table, à une vue ou à un
schéma
•
s'exécute de façon implicite lorsqu'un événement
donné se produit
•
il peut s'agir d'un :
–
déclencheur applicatif, qui s'exécute lorsqu'un
événement se produit dans une application donnée
–
déclencheur de base de données, qui s'exécute
lorsqu'un événement de type données (LMD) ou
système (connexion ou arrêt) se produit dans un
schéma ou une base de données
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Règles relatives à la conception de déclencheurs
•
•
9-4
Il est conseillé de concevoir des déclencheurs pour :
–
exécuter des actions associées
–
centraliser des opérations globales
Leur conception est à proscrire :
–
lorsque la fonctionnalité est déjà intégrée au serveur
Oracle
–
lorsqu'ils constituent des doublons d'autres
déclencheurs
•
Si le code PL/SQL est très long, créer des procédures
stockées et les appeler dans un déclencheur
•
L'utilisation excessive de déclencheurs peut entraîner
des interdépendances complexes dont la gestion peut
s'avérer difficile dans les applications volumineuses
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Exemple de déclencheur
de base de données
Application
INSERT INTO EMPLOYEES
. . .;
Table EMPLOYEES
Déclencheur CHECK_SAL
…
9-5
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Créer des déclencheurs LMD
Une instruction de déclenchement comporte les
éléments suivants :
•
moment du déclenchement
–
pour une table : BEFORE, AFTER
–
pour une vue : INSTEAD OF
• événement déclencheur : INSERT, UPDATE
• nom de la table : sur la table ou la vue
• type de déclencheur : ligne ou instruction
• clause WHEN : condition restrictive
• corps du déclencheur : bloc PL/SQL
9-6
Copyright © Oracle Corporation, 2001. Tous droits réservés.
ou DELETE
Composants des déclencheurs LMD
Moment du déclenchement : à quel moment le
déclencheur doit-il s'exécuter ?
•
BEFORE : exécution du corps du déclencheur avant le
déclenchement de l'événement LMD sur une table
•
AFTER : exécution du corps du déclencheur après le
déclenchement de l'événement LMD sur une table
•
INSTEAD OF : exécution du corps du déclencheur au
lieu de l'instruction de déclenchement. Ce
déclencheur est utilisé pour les vues qui ne peuvent
pas être modifiées autrement
9-7
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Composants des déclencheurs LMD
Evénement utilisateur déclencheur : quelle
instruction LMD entraîne l'exécution du
déclencheur ? Vous pouvez utiliser les instructions
suivantes :
•
•
•
9-8
INSERT
UPDATE
DELETE
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Composants des déclencheurs LMD
Type de déclencheur : le corps du déclencheur doit-il
s'exécuter une seule fois ou pour chaque ligne
concernée par l'instruction ?
•
Instruction : le corps du déclencheur s'exécute une
seule fois pour l'événement déclencheur. Il s'agit du
comportement par défaut. Un déclencheur sur
instruction s'exécute une fois, même si aucune ligne
n'est affectée
•
Ligne : le corps du déclencheur s'exécute une fois pour
chaque ligne concernée par l'événement déclencheur.
Un déclencheur sur ligne ne s'exécute pas si
l'événement déclencheur n'affecte aucune ligne
9-9
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Composants des déclencheurs LMD
Corps du déclencheur : quelle action le déclencheur
doit-il effectuer ?
Le corps du déclencheur est un bloc PL/SQL ou un
appel de procédure
9-10
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Séquence d'exécution
Lorsque la manipulation concerne une seule ligne, utilisez la
séquence d'exécution suivante pour un déclencheur sur une table :
Instruction LMD
INSERT INTO departments (department_id,
department_name, location_id)
VALUES (400, 'CONSULTING', 2400);
Action de déclenchement
…
Déclencheur sur
instruction BEFORE
Déclencheur sur ligne BEFORE
Déclencheur sur ligne AFTER
Déclencheur sur instruction
AFTER
9-11
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Séquence d'exécution
Lorsque la manipulation concerne plusieurs lignes, utilisez la
séquence d'exécution suivante pour un déclencheur sur une table :
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;
Déclencheur sur instruction BEFORE
Déclencheur sur ligne BEFORE
Déclencheur sur ligne AFTER
...
Déclencheur sur ligne BEFORE
Déclencheur
sur ligne AFTER
...
Déclencheur sur instruction AFTER
9-12
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Syntaxe pour la création de déclencheurs
sur instruction LMD
Syntaxe :
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
trigger_body
Remarque : Les noms des déclencheurs doivent être
uniques au sein d'un même schéma
9-13
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Créer des déclencheurs
sur instruction LMD
Exemple :
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND '18:00')
THEN RAISE_APPLICATION_ERROR (-20500,'You may
insert into EMPLOYEES table only
during business hours.');
END IF;
END;
/
9-14
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Tester SECURE_EMP
INSERT INTO employees (employee_id, last_name,
first_name, email, hire_date,
job_id, salary, department_id)
VALUES (300, 'Smith', 'Rob', 'RSMITH', SYSDATE,
'IT_PROG', 4500, 60);
9-15
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Utiliser des prédicats conditionnels
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
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,'You may delete from
EMPLOYEES table only during business hours.');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,'You may insert into
EMPLOYEES table only during business hours.');
ELSIF
UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR (-20503,'You may update
SALARY only during business hours.');
ELSE
RAISE_APPLICATION_ERROR (-20504,'You may update
EMPLOYEES table only during normal hours.');
END IF;
END IF;
END;
9-16
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Créer un déclencheur sur ligne LMD
Syntaxe :
CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN (condition)]
trigger_body
9-17
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Créer des déclencheurs sur ligne LMD
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))
AND :NEW.salary > 15000
THEN
RAISE_APPLICATION_ERROR (-20202,'Employee
cannot earn this amount');
END IF;
END;
/
9-18
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Utiliser les qualificatifs OLD et NEW
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_table (user_name, timestamp,
id, old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary );
END;
/
9-19
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Utiliser les qualificatifs OLD et NEW :
exemple de la table Audit_Emp_Table
INSERT INTO employees
(employee_id, last_name, job_id, salary, ...)
VALUES (999, 'Temp emp', 'SA_REP', 1000, ...);
UPDATE employees
SET salary = 2000, last_name = 'Smith'
WHERE employee_id = 999;
SELECT user_name, timestamp, ... FROM audit_emp_table
9-20
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Restreindre l'action d'un déclencheur sur ligne
CREATE OR REPLACE TRIGGER derive_commission_pct
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING
THEN :NEW.commission_pct := 0;
ELSIF :OLD.commission_pct IS NULL
THEN :NEW.commission_pct := 0;
ELSE
:NEW.commission_pct := :OLD.commission_pct + 0.05;
END IF;
END;
/
9-21
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Déclencheurs INSTEAD OF
Application
INSERT INTO my_view
. . .;
Déclencheur
INSTEAD OF
MY_VIEW
9-22
INSERT
TABLE1
UPDATE
TABLE2
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Créer un déclencheur INSTEAD OF
Syntaxe :
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
event1 [OR event2 OR event3]
ON view_name
[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
trigger_body
9-23
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Créer un déclencheur INSTEAD OF
Exécution d'une instruction INSERT dans la vue EMP_DETAILS
basée sur les tables EMPLOYEES et DEPARTMENTS
1
INSERT INTO emp_details(employee_id, ... )
VALUES(9001,'ABBOTT',3000,10,'abbott.mail.com','HR_MAN');
Opération INSERT d'un
déclencheur INSTEAD
OF dans EMP_DETAILS
9-26
…
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Créer un déclencheur INSTEAD OF
Exécution d'une instruction INSERT dans la vue EMP_DETAILS
basée sur les tables EMPLOYEES et DEPARTMENTS
1
INSERT INTO emp_details(employee_id, ... )
VALUES(9001,'ABBOTT',3000,10,'abbott.mail.com','HR_MAN');
Opération INSERT d'un
déclencheur INSTEAD
OF dans EMP_DETAILS
2
…
9-27
INSERT dans
NEW_EMPS
…
3
UPDATE
NEW_DEPTS
…
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Différences entre les déclencheurs de base
de données et les procédures stockées
Déclencheurs
Procédures
Définis via la commande
CREATE TRIGGER
Définis via la commande
CREATE PROCEDURE
Le dictionnaire de données
Le dictionnaire de données contient
contient le code source dans
USER_TRIGGERS
le code source dans
USER_SOURCE
Appel implicite
Appel explicite
Les instructions COMMIT,
Les instructions COMMIT,
SAVEPOINT et ROLLBACK ne
sont pas autorisées
SAVEPOINT et ROLLBACK sont
autorisées
9-28
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Différences entre les déclencheurs de base de
données et les déclencheurs Form Builder
INSERT INTO EMPLOYEES
. . .;
Table EMPLOYEES
…
9-29
Déclencheur CHECK_SAL
BEFORE
INSERT
ligne
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Gérer les déclencheurs
Désactiver ou réactiver un déclencheur de base de données :
ALTER TRIGGER trigger_name
DISABLE | ENABLE
Désactiver ou réactiver tous les déclencheurs d'une table :
ALTER TABLE table_name
DISABLE | ENABLE
Recompiler un déclencheur pour une table :
ALTER TRIGGER trigger_name COMPILE
9-30
Copyright © Oracle Corporation, 2001. Tous droits réservés.
ALL TRIGGERS
Syntaxe DROP TRIGGER
Pour supprimer un déclencheur de la base de
données, utiliser la syntaxe DROP TRIGGER :
DROP TRIGGER trigger_name;
Exemple:
DROP TRIGGER secure_emp;
Remarque : Lorsqu'une table est supprimée, tous ses
déclencheurs sont également supprimés
9-31
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Tests des déclencheurs
9-32
•
Tester toutes les opérations sur les données qui
provoquent un déclenchement, ainsi que celles
n'en produisent pas
•
•
Tester chaque cas de la clause WHEN
•
Tester l'impact du déclencheur sur les autres
déclencheurs
•
Tester l'impact des autres déclencheurs sur le
déclencheur
Provoquer une exécution directe du déclencheur
via une opération de base sur les données, et une
exécution indirecte via une procédure
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Modèle d'exécution des déclencheurs et
vérification des contraintes
1. Exécuter tous les déclencheurs BEFORE
STATEMENT.
2. Effectuer une boucle pour toutes les lignes
affectées :
a. exécuter tous les déclencheurs BEFORE ROW
b. exécuter tous les déclencheurs AFTER ROW
3. Exécuter l'instruction LMD et vérifier les
contraintes d'intégrité.
4. Exécuter tous les déclencheurs AFTER
STATEMENT.
9-33
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Exemple de modèle d'exécution des
déclencheurs et de vérification des contraintes
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Integrity constraint violation error
CREATE OR REPLACE TRIGGER constr_emp_trig
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO departments
VALUES (999, 'dept999', 140, 2400);
END;
/
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Successful after trigger is fired
9-34
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Démonstration type de déclencheurs
utilisant des structures de package
AUDIT_EMP_TRIG
Instruction LMD dans FOR EACH ROW
la table EMPLOYEES Incrémentation des
variables
1
Package
VAR_PACK
2
AUDIT_EMP_TAB
AFTER STATEMENT
Copie, puis réinitialisation
des variables
3
4
AUDIT_TABLE
9-35
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Déclencheurs AFTER sur ligne
et sur instruction
CREATE OR REPLACE TRIGGER audit_emp_trig
AFTER
UPDATE or INSERT or DELETE on EMPLOYEES
FOR EACH ROW
BEGIN
IF
DELETING
THEN var_pack.set_g_del(1);
ELSIF
INSERTING
THEN var_pack.set_g_ins(1);
ELSIF
UPDATING ('SALARY')
THEN var_pack.set_g_up_sal(1);
ELSE
var_pack.set_g_upd(1);
END IF;
END audit_emp_trig;
/
CREATE OR REPLACE TRIGGER audit_emp_tab
AFTER
UPDATE or INSERT or DELETE on employees
BEGIN
audit_emp;
END audit_emp_tab;
/
9-36
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Démonstration de spécification du
package VAR_PACK
var_pack.sql
CREATE OR REPLACE PACKAGE var_pack
IS
-- these functions are used to return the
-- values of package variables
FUNCTION g_del RETURN NUMBER;
FUNCTION g_ins RETURN NUMBER;
FUNCTION g_upd RETURN NUMBER;
FUNCTION g_up_sal RETURN NUMBER;
-- these procedures are used to modify the
-- values of the package variables
PROCEDURE set_g_del
(p_val IN NUMBER);
PROCEDURE set_g_ins
(p_val IN NUMBER);
PROCEDURE set_g_upd
(p_val IN NUMBER);
PROCEDURE set_g_up_sal (p_val IN NUMBER);
END var_pack;
/
9-37
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Démonstration d'utilisation de la
procédure AUDIT_EMP
CREATE OR REPLACE PROCEDURE audit_emp IS
v_del
NUMBER
:= var_pack.g_del;
v_ins
NUMBER
:= var_pack.g_ins;
v_upd
NUMBER
:= var_pack.g_upd;
v_up_sal NUMBER
:= var_pack.g_up_sal;
BEGIN
IF v_del + v_ins + v_upd != 0 THEN
UPDATE audit_table SET
del = del + v_del, ins = ins + v_ins,
upd = upd + v_upd
WHERE user_name=USER AND tablename='EMPLOYEES'
AND
column_name IS NULL;
END IF;
IF v_up_sal != 0
THEN
UPDATE audit_table SET upd = upd + v_up_sal
WHERE user_name=USER AND tablename='EMPLOYEES'
AND
column_name = 'SALARY';
END IF;
-- resetting global variables in package VAR_PACK
var_pack.set_g_del (0); var_pack.set_g_ins (0);
var_pack.set_g_upd (0); var_pack.set_g_up_sal (0);
END audit_emp;
9-39
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Synthèse
Procédure
xxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxx
Package
Déclencheur
Déclaration de
la procédure A
Définition de
la procédure B
Définition de
la procédure A
Variable
locale
9-40
Copyright © Oracle Corporation, 2001. Tous droits réservés.
Présentation de l'exercice 9
Dans cet exercice, vous allez :
•
•
9-41
créer des déclencheurs sur instruction et sur ligne
créer des déclencheurs avancés afin d'accroître
les fonctionnalités de la base de données Oracle
Copyright © Oracle Corporation, 2001. Tous droits réservés.