9 Crer des dclencheurs de base de donnes

9 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 : • 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 9 -3 Copyright © Oracle Corporation, 2001. Tous droits réservés.

Rè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 9 -4 • 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 • • • 9 -6 é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 Copyright © Oracle Corporation, 2001. Tous droits réservés.

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 : • INSERT • • 9 -8 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 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 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 event 1 [OR event 2 OR event 3] 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, 'HH 24: 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, 'HH 24') 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; 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 event 1 [OR event 2 OR event 3] 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 TABLE 1 UPDATE TABLE 2 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 event 1 [OR event 2 OR event 3] 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 contient le code source dans USER_TRIGGERS le code source dans USER_SOURCE Appel implicite Appel explicite 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 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 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 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, 'dept 999', 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 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.