Créer des déclencheurs de base de données

9Copyright © Oracle Corporation, 2001. Tous droits réservés.

Créer des déclencheurs de base de données

9-2 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Objectifs

A la fin de ce chapitre, vous pourrez :

• décrire différents types de déclencheur

• décrire les déclencheurs de base de données et leur utilisation

• créer des déclencheurs de base de données

• décrire les règles d'activation des déclencheurs de base de données

• supprimer des déclencheurs de base de données


Types de déclencheurTypes de déclencheur

Un déclencheur :

• 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

Un déclencheur :

• 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


Règles relatives à la conception de déclencheursRègles relatives à la conception de déclencheurs

• 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

• 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


Application

INSERT INTO EMPLOYEES . . .;

Table EMPLOYEES Déclencheur CHECK_SAL

Exemple de déclencheur de base de données

…


Créer des déclencheurs LMDCré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 ou DELETE

• 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

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 ou DELETE

• 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


Composants des déclencheurs LMDComposants 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

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


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 :

• INSERT

• UPDATE

• DELETE



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



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


Séquence d'exécution

Action de déclenchementDéclencheur sur

instruction BEFORE

Déclencheur sur ligne BEFOREDéclencheur sur ligne AFTER

Déclencheur sur instruction AFTER

Instruction LMD

Lorsque la manipulation concerne une seule ligne, utilisez la séquence d'exécution suivante pour un déclencheur sur une table :

INSERT INTO departments (department_id, department_name, location_id)VALUES (400, 'CONSULTING', 2400);

…


UPDATE employees SET salary = salary * 1.1 WHERE department_id = 30;

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 :

Déclencheur sur instructionDéclencheur sur instruction BEFOREBEFORE

Déclencheur sur ligneDéclencheur sur ligne BEFOREBEFORE

Déclencheur sur ligneDéclencheur sur ligne AFTERAFTER......Déclencheur sur ligneDéclencheur sur ligne BEFOREBEFORE

Déclencheur sur ligneDéclencheur sur ligne AFTERAFTER......Déclencheur sur instructionDéclencheur sur instruction AFTERAFTER


Syntaxe pour la création de déclencheurs sur instruction LMD

Syntaxe pour la création de déclencheurs sur instruction LMD

CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_nametrigger_body

Remarque : Les noms des déclencheurs doivent être uniques au sein d'un même schéma

Syntaxe :


Créer des déclencheurs sur instruction LMD

Créer des déclencheurs sur instruction LMD

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;/

Exemple :


Tester SECURE_EMPTester 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);


Utiliser des prédicats conditionnelsUtiliser des prédicats conditionnels

CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT OR UPDATE OR DELETE ON employeesBEGIN 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;


Créer un déclencheur sur ligne LMDCréer un déclencheur sur ligne LMD

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

Syntaxe :


Créer des déclencheurs sur ligne LMDCré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;/


Utiliser les qualificatifs OLD et NEWUtiliser 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;

/


Utiliser les qualificatifs OLD et NEW : exemple de la table Audit_Emp_Table

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


Restreindre l'action d'un déclencheur sur ligneRestreindre 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;/


Déclencheurs INSTEAD OF

Application

INSERT INTO my_view . . .;

MY_VIEW

Déclencheur INSTEAD OF

INSERT TABLE1

UPDATE TABLE2


Créer un déclencheur INSTEAD OFCréer un déclencheur INSTEAD OF

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

Syntaxe :


Créer un déclencheur INSTEAD OF

INSERT INTO emp_details(employee_id, ... )VALUES(9001,'ABBOTT',3000,10,'abbott.mail.com','HR_MAN');

Exécution d'une instruction INSERT dans la vue EMP_DETAILS basée sur les tables EMPLOYEES et DEPARTMENTS

Opération INSERT d'un

déclencheur INSTEAD OF dans EMP_DETAILS …

1


Créer un déclencheur INSTEAD OF

INSERT INTO emp_details(employee_id, ... )VALUES(9001,'ABBOTT',3000,10,'abbott.mail.com','HR_MAN');

Exécution d'une instruction INSERT dans la vue EMP_DETAILS basée sur les tables EMPLOYEES et DEPARTMENTS

Opération INSERT d'un déclencheur INSTEAD OF dans EMP_DETAILS

INSERT dansNEW_EMPS

UPDATENEW_DEPTS

…

……

1

2 3


Différences entre les déclencheurs de base de données et les procédures stockées

Différences entre les déclencheurs de base de données et les procédures stockées

Déclencheurs

Définis via la commande CREATE TRIGGER

Le dictionnaire de données

contient le code source dans USER_TRIGGERS

Appel implicite

Les instructions COMMIT, SAVEPOINT et ROLLBACK ne sont pas autorisées

Procédures

Définis via la commande CREATE PROCEDURE

Le dictionnaire de données contient

le code source dans USER_SOURCE

Appel explicite

Les instructions COMMIT,SAVEPOINT et ROLLBACK sontautorisées


Différences entre les déclencheurs de base de données et les déclencheurs Form Builder

INSERT INTO EMPLOYEES . . .;

Table EMPLOYEES Déclencheur CHECK_SAL

BEFOREINSERTligne

…


ALTER TRIGGER trigger_name DISABLE | ENABLE

Gérer les déclencheursGérer les déclencheurs

Désactiver ou réactiver un déclencheur de base de données :

ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

Désactiver ou réactiver tous les déclencheurs d'une table :

ALTER TRIGGER trigger_name COMPILE

Recompiler un déclencheur pour une table :


Syntaxe DROP TRIGGERSyntaxe DROP TRIGGER

Pour supprimer un déclencheur de la base de données, utiliser la syntaxe DROP TRIGGER :Pour supprimer un déclencheur de la base de données, utiliser la syntaxe DROP TRIGGER :

DROP TRIGGER trigger_name;

DROP TRIGGER secure_emp;

Exemple:Exemple:

Remarque : Lorsqu'une table est supprimée, tous ses déclencheurs sont également supprimésRemarque : Lorsqu'une table est supprimée, tous ses déclencheurs sont également supprimés


Tests des déclencheurs

• 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

• 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

• Tester l'impact du déclencheur sur les autres déclencheurs

• Tester l'impact des autres déclencheurs sur le déclencheur


Modèle d'exécution des déclencheurs et vérification des contraintes

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.

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.


Exemple de modèle d'exécution des déclencheurs et de vérification des contraintes

Exemple de modèle d'exécution des déclencheurs et de vérification des contraintes

CREATE OR REPLACE TRIGGER constr_emp_trig AFTER UPDATE ON employees FOR EACH ROWBEGIN INSERT INTO departments VALUES (999, 'dept999', 140, 2400);END;/

UPDATE employees SET department_id = 999

WHERE employee_id = 170;-- Successful after trigger is fired

UPDATE employees SET department_id = 999

WHERE employee_id = 170; -- Integrity constraint violation error


Package VAR_PACK

AUDIT_EMP_TRIGFOR EACH ROW Incrémentation des variables

2

Démonstration type de déclencheurs utilisant des structures de package

AUDIT_EMP_TABAFTER STATEMENTCopie, puis réinitialisation des variables

3

Instruction LMD dans la table EMPLOYEES

1

AUDIT_TABLE4


Déclencheurs AFTER sur ligne et sur instruction

CREATE OR REPLACE TRIGGER audit_emp_trigAFTER UPDATE or INSERT or DELETE on EMPLOYEESFOR EACH ROWBEGIN 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_tabAFTER UPDATE or INSERT or DELETE on employeesBEGIN audit_emp;END audit_emp_tab;/


Démonstration de spécification du package VAR_PACK

Démonstration de spécification du package VAR_PACK

CREATE OR REPLACE PACKAGE var_packIS-- 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;/

var_pack.sql


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;

Démonstration d'utilisation de la procédure AUDIT_EMP

Démonstration d'utilisation de la procédure AUDIT_EMP


Procédure Package Déclencheur

xxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxx

Déclaration de la procédure A

Définition de la procédure B

Synthèse

Définition de la procédure A

Variable locale


Présentation de l'exercice 9Présentation de l'exercice 9

Dans cet exercice, vous allez :

• 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

Dans cet exercice, vous allez :

• 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

Documents

Créer des déclencheurs de base de données