Oracle Tutoriel de déclenchement PL/SQL : au lieu de, composé [Exemple]

Qu’est-ce que Trigger en PL/SQL ?

DÉCLENCHEURS sont des programmes stockés qui sont lancés par Oracle moteur automatiquement lorsque des instructions DML telles que insert, update, delete sont exécutées sur la table ou lorsque certains événements se produisent. Le code à exécuter en cas de déclencheur peut être défini selon les besoins. Vous pouvez choisir l'événement sur lequel le déclencheur doit être déclenché et le moment de l'exécution. Le but du déclencheur est de maintenir l’intégrité des informations sur la base de données.

Avantages des déclencheurs

Voici les avantages des déclencheurs.

Génération automatique de certaines valeurs de colonnes dérivées
Application de l'intégrité référentielle
Journalisation des événements et stockage des informations sur l'accès aux tables
vérification des comptes
Syncréplication horaire des tables
Imposer des autorisations de sécurité
Empêcher les transactions invalides

Types de déclencheurs dans Oracle

Les déclencheurs peuvent être classés en fonction des paramètres suivants.

Classement basé sur le timing
BEFORE Trigger : il se déclenche avant que l'événement spécifié ne se produise.
AFTER Trigger : il se déclenche après que l'événement spécifié s'est produit.
AU LIEU DE Déclencheur : Un type spécial. Vous en apprendrez davantage sur les autres sujets. (uniquement pour DML)
Classement basé sur le niveau
Déclencheur de niveau STATEMENT : il se déclenche une fois pour l'instruction d'événement spécifiée.
Déclencheur au niveau ROW : il se déclenche pour chaque enregistrement affecté par l'événement spécifié. (uniquement pour DML)
Classement basé sur le Événement
Déclencheur DML : il se déclenche lorsque l'événement DML est spécifié (INSERT/UPDATE/DELETE)
Déclencheur DDL : il se déclenche lorsque l'événement DDL est spécifié (CREATE/ALTER)
Déclencheur DATABASE : il se déclenche lorsque l'événement de base de données est spécifié (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Chaque déclencheur est donc la combinaison des paramètres ci-dessus.

Comment créer un déclencheur

Vous trouverez ci-dessous la syntaxe de création d'un déclencheur.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Explication de la syntaxe :

La syntaxe ci-dessus montre les différentes instructions facultatives présentes lors de la création du déclencheur.
AVANT/APRÈS précisera les horaires de l’événement.
INSÉRER/MISE À JOUR/CONNEXION/CRÉER/etc. spécifiera l'événement pour lequel le déclencheur doit être déclenché.
La clause ON précisera sur quel objet l'événement mentionné ci-dessus est valide. Par exemple, ce sera le nom de la table sur laquelle l'événement DML pourra se produire dans le cas de DML Trigger.
La commande « POUR CHAQUE LIGNE » spécifiera le déclencheur de niveau ROW.
La clause WHEN spécifiera la condition supplémentaire dans laquelle le déclencheur doit se déclencher.
La partie déclaration, la partie exécution, la partie gestion des exceptions sont les mêmes que celles des autres Blocs PL/SQL. La partie déclaration et la partie gestion des exceptions sont facultatives.

Clauses :NOUVEAU et :ANCIENNE

Dans un déclencheur au niveau de la ligne, le déclencheur se déclenche pour chaque ligne associée. Et parfois, il est nécessaire de connaître la valeur avant et après l'instruction DML.

Oracle a fourni deux clauses dans le déclencheur de niveau RECORD pour contenir ces valeurs. Nous pouvons utiliser ces clauses pour faire référence aux anciennes et nouvelles valeurs à l'intérieur du corps du déclencheur.

:NEW – Il contient une nouvelle valeur pour les colonnes de la table/vue de base lors de l’exécution du déclencheur
:OLD – Il contient l’ancienne valeur des colonnes de la table/vue de base pendant l’exécution du déclencheur

Cette clause doit être utilisée en fonction de l'événement DML. Le tableau ci-dessous précisera quelle clause est valide pour quelle instruction DML (INSERT/UPDATE/DELETE).

	INSERT	MISE À JOUR	EFFACER
:NOUVEAU	VALIDE	VALIDE	INVALIDE. Il n’y a pas de nouvelle valeur en cas de suppression.
:VIEUX	INVALIDE. Il n'y a pas d'ancienne valeur dans le cas d'insertion	VALIDE	VALIDE

AU LIEU DE Déclencheur

« Déclencheur INSTEAD OF » est un type spécial de déclencheur. Il est utilisé uniquement dans les déclencheurs DML. Il est utilisé lorsqu'un événement DML va se produire sur la vue complexe.

Prenons un exemple dans lequel une vue est réalisée à partir de 3 tables de base. Lorsqu'un événement DML est émis sur cette vue, cela deviendra invalide car les données proviennent de 3 tables différentes. Donc, dans ce cas, le déclencheur INSTEAD OF est utilisé. Le déclencheur INSTEAD OF est utilisé pour modifier directement les tables de base au lieu de modifier la vue pour l'événement donné.

Exemple 1: Dans cet exemple, nous allons créer une vue complexe à partir de deux tables de base.

Table_1 est une table emp et

Table_2 est la table du département.

Nous allons ensuite voir comment le déclencheur INSTEAD OF est utilisé pour émettre UPDATE l'instruction de détail de localisation sur cette vue complexe. Nous allons également voir comment les :NEW et :OLD sont utiles dans les déclencheurs.

Étape 1 : Création des tables « emp » et « dept » avec les colonnes appropriées
Étape 2 : Remplir le tableau avec des exemples de valeurs
Étape 3 : Création d'une vue pour la table créée ci-dessus
Étape 4 : Mise à jour de la vue avant le déclencheur au lieu de
Étape 5 : Création du déclencheur au lieu de
Étape 6 : Mise à jour de la vue après le déclenchement au lieu du déclencheur

Étape 1) Création des tables 'emp' et 'dept' avec les colonnes appropriées

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept( 
Dept_no NUMBER, 
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Explication du code

Ligne de code 1-7: Création de la table 'emp'.
Ligne de code 8-12: Création de la table 'rayon'.

Sortie

Tableau créé

Étape 2) Maintenant que nous avons créé la table, nous allons remplir cette table avec des exemples de valeurs et la création de vues pour les tables ci-dessus.

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

Explication du code

Ligne de code 13-19: Insertion de données dans la table 'dept'.
Ligne de code 20 à 26 : Insertion de données dans la table 'emp'.

Sortie

Procédure PL/SQL terminé

Étape 3) Création d'une vue pour la table créée ci-dessus.

CREATE VIEW guru99_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Explication du code

Ligne de code 27 à 32 : Création de la vue 'guru99_emp_view'.

Ligne de code 33 : Interrogation de guru99_emp_view.

Sortie

Vue créée

NOM DE L'EMPLOYÉ	DEPT_NAME	EMPLACEMENT
ZZZ	HR	France
YYY	TRANSACTIONS	UK
XXX	FINANCIÈRE	JAPON

Étape 4) Mise à jour de la vue avant le déclencheur au lieu du déclencheur.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;
COMMIT;
END;
/

Explication du code

Ligne de code 34 à 38 : Mettez à jour l'emplacement de « XXX » en « FRANCE ». Il a soulevé l'exception parce que le Instructions DML ne sont pas autorisés dans la vue complexe.

Sortie

ORA-01779 : impossible de modifier une colonne qui correspond à une table dont la clé n'est pas préservée

ORA-06512: à la ligne 2

Étape 5)Pour éviter l'erreur lors de la mise à jour de la vue à l'étape précédente, dans cette étape, nous allons utiliser « au lieu du déclencheur ».

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Explication du code

Ligne de code 39 : Création du déclencheur INSTEAD OF pour l'événement 'UPDATE' sur la vue 'guru99_emp_view' au niveau ROW. Il contient l'instruction de mise à jour pour mettre à jour l'emplacement dans la table de base « dept ».
Ligne de code 44 : L'instruction de mise à jour utilise ':NEW' et ': OLD' pour trouver la valeur des colonnes avant et après la mise à jour.

Sortie

Déclencheur créé

Étape 6) Mise à jour de la vue après le déclenchement au lieu du déclencheur. Désormais, l'erreur ne se produira plus car le « au lieu du déclencheur » gérera l'opération de mise à jour de cette vue complexe. Et une fois le code exécuté, la localisation de l'employé XXX sera mise à jour vers « France » depuis « Japon ».

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Explication du code :

Ligne de code 49 à 53 : Mise à jour de la localisation de « XXX » vers « FRANCE ». L'opération réussit car le déclencheur 'INSTEAD OF' a arrêté l'instruction de mise à jour réelle affichée et effectué la mise à jour de la table de base.
Ligne de code 55 : Vérification de l'enregistrement mis à jour.

Sortie :

Procédure PL/SQL terminée avec succès

NOM DE L'EMPLOYÉ	DEPT_NAME	EMPLACEMENT
ZZZ	HR	France
YYY	TRANSACTIONS	UK
XXX	FINANCIÈRE	FRANCE

Déclencheur composé

Le déclencheur composé est un déclencheur qui vous permet de spécifier des actions pour chacun des quatre points de timing dans le corps du déclencheur unique. Les quatre points de synchronisation différents qu'il prend en charge sont les suivants.

AVANT DÉCLARATION – niveau
AVANT LA RANGÉE – niveau
APRÈS LA LIGNE – niveau
APRÈS DÉCLARATION – niveau

Il offre la possibilité de combiner les actions pour différents timings dans le même déclencheur.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Explication de la syntaxe :

La syntaxe ci-dessus montre la création du déclencheur 'COMPOUND'.
La section déclarative est commune à tous les blocs d’exécution du corps du déclencheur.
Ces 4 blocs de synchronisation peuvent être dans n'importe quel ordre. Il n’est pas obligatoire d’avoir tous ces 4 blocs de timing. Nous pouvons créer un déclencheur COMPOUND uniquement pour les timings requis.

Exemple 1: Dans cet exemple, nous allons créer un déclencheur pour remplir automatiquement la colonne salaire avec la valeur par défaut 5000.

CREATE TRIGGER emp_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Explication du code :

Ligne de code 2-10: Création de déclencheur composé. Il est créé pour le timing AVANT le niveau LIGNE afin de remplir le salaire avec la valeur par défaut 5000. Cela modifiera le salaire à la valeur par défaut « 5000 » avant d'insérer l'enregistrement dans la table.
Ligne de code 11-14: Insérez l'enregistrement dans la table 'emp'.
Ligne de code 16: Vérification de l'enregistrement inséré.

Sortie :

Déclencheur créé

Procédure PL / SQL terminée avec succès.

EMP_NAME	EMP_NON	LE SALAIRE	DE VENTE	DEPT_NON
CCC	1004	5000	AAA	30

Activation et désactivation des déclencheurs

Les déclencheurs peuvent être activés ou désactivés. Pour activer ou désactiver le déclencheur, une instruction ALTER (DDL) doit être donnée pour le déclencheur qui le désactive ou l'active.

Vous trouverez ci-dessous la syntaxe pour activer/désactiver les déclencheurs.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Explication de la syntaxe :

La première syntaxe montre comment activer/désactiver le déclencheur unique.
La deuxième instruction montre comment activer/désactiver tous les déclencheurs sur une table particulière.

Résumé

Dans ce chapitre, nous avons découvert les déclencheurs PL/SQL et leurs avantages. Nous avons également appris les différentes classifications et discuté du déclencheur INSTEAD OF et du déclencheur COMPOUND.

Tu pourrais aimer: