Tutoriel Oracle PL / SQL Trigger: au lieu de, composé (exemple)

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

Les TRIGGERS sont des programmes stockés qui sont déclenchés automatiquement par le moteur Oracle lorsque des instructions DML telles que l'insertion, la mise à jour, la suppression sont exécutées sur la table ou que certains événements se produisent. Le code à exécuter en cas de déclenchement peut être défini selon l'exigence. 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.

Dans ce didacticiel, vous apprendrez-

Avantages des déclencheurs
Types de déclencheurs dans Oracle
Comment créer un déclencheur
: NOUVEAU et: ANCIEN Clause
AU LIEU DE Trigger
Déclencheur composé

Avantages des déclencheurs

Voici les avantages des déclencheurs.

Générer automatiquement des valeurs de colonne dérivées
Faire respecter l'intégrité référentielle
Journalisation des événements et stockage des informations sur l'accès aux tables
Audit
Réplication synchrone 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.

Classification basée 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 Trigger: Un type spécial. Vous en apprendrez plus sur les autres sujets. (uniquement pour DML)
Classification basée 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 de niveau ROW: il se déclenche pour chaque enregistrement affecté dans l'événement spécifié. (uniquement pour DML)
Classement basé sur l' é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)
- DATABASE Trigger: il se déclenche lorsque l'événement de base de données est spécifié (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Ainsi, chaque déclencheur est la combinaison des paramètres ci-dessus.

Comment créer un déclencheur

Vous trouverez ci-dessous la syntaxe pour créer un déclencheur.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Explication de la syntaxe:

La syntaxe ci-dessus montre les différentes instructions facultatives présentes lors de la création du déclencheur.
BEFORE / AFTER spécifiera les horaires des événements.
INSERT / UPDATE / LOGON / CREATE / etc. spécifiera l'événement pour lequel le déclencheur doit être déclenché.
La clause ON spécifiera 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 peut se produire dans le cas de DML Trigger.
La commande "FOR EACH ROW" spécifie le déclencheur de niveau ROW.
La clause WHEN spécifie la condition supplémentaire dans laquelle le déclencheur doit se déclencher.
La partie déclaration, partie exécution, partie gestion des exceptions est la même que celle des autres blocs PL / SQL. La partie déclaration et la partie gestion des exceptions sont facultatives.

: NOUVEAU et: ANCIEN Clause

Dans un déclencheur de niveau 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 pendant 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 spécifie quelle clause est valide pour quelle instruction DML (INSERT / UPDATE / DELETE).

	INSÉRER	METTRE À JOUR	EFFACER
:NOUVEAU	VALIDE	VALIDE	INVALIDE. Il n'y a pas de nouvelle valeur dans le cas de suppression.
:VIEILLE	INVALIDE. Il n'y a pas d'ancienne valeur dans le cas d'insertion	VALIDE	VALIDE

AU LIEU DE Trigger

"INSTEAD OF trigger" est le type spécial de trigger. Il n'est utilisé que 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 faite à partir de 3 tables de base. Lorsqu'un événement DML est émis sur cette vue, cela deviendra invalide car les données sont extraites de 3 tables différentes. Donc, dans ce 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 une table de service.

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

Étape 1: Création de la table 'emp' et 'dept' avec les colonnes appropriées
Étape 2: remplir le tableau avec des exemples de valeurs
Étape 3: Création de la 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 de la table '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

Code ligne 1-7 : Création de la table 'emp'.
Code ligne 8-12 : Création de la table 'dept'.

Production

Table créée

É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.

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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'.

Production

Procédure PL / SQL terminée

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

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE 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.

Production

Vue créée

NOM DE L'EMPLOYÉ	DEPT_NAME	LIEU
ZZZ	HEURE	Etats-Unis
YYY	VENTES	Royaume-Uni
XXX	FINANCIER	JAPON

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

BEGINUPDATE 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» sur «FRANCE». Il a soulevé l'exception car les instructions DML ne sont pas autorisées dans la vue complexe.

Production

ORA-01779: impossible de modifier une colonne qui correspond à une table non conservée par clé

ORA-06512: à la ligne 2

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

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Explication du code

Ligne de code 39: Création du trigger 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.

Production

Déclencheur créé

Étape 6) Mise à jour de la vue après le déclenchement au lieu du déclencheur. Maintenant, l'erreur ne viendra pas car le "au lieu de déclencheur" gérera l'opération de mise à jour de cette vue complexe. Et une fois le code exécuté, l'emplacement de l'employé XXX sera mis à jour en «France» à partir de «Japon».

BEGINUPDATE 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 l'emplacement de "XXX" en "FRANCE". Il réussit car le déclencheur 'INSTEAD OF' a arrêté l'instruction de mise à jour réelle sur la vue et effectué la mise à jour de la table de base.
Ligne de code 55: vérification de l'enregistrement mis à jour.

Production:

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

NOM DE L'EMPLOYÉ	DEPT_NAME	LIEU
ZZZ	HEURE	Etats-Unis
YYY	VENTES	Royaume-Uni
XXX	FINANCIER	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 synchronisation 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 RANG - niveau
APRÈS RANG - niveau
APRÈS DÉCLARATION - niveau

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

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;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 quelle séquence. Il n'est pas obligatoire d'avoir tous ces 4 blocs de chronométrage. Nous pouvons créer un déclencheur COMPOUND uniquement pour les minutages requis.

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

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/

BEGININSERT 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 du déclencheur composé. Il est créé pour chronométrer le niveau AVANT LIGNE afin de renseigner le salaire avec la valeur par défaut 5000. Cela changera le salaire en 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é.

Production:

Déclencheur créé

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

EMP_NAME	EMP_NO	UN SALAIRE	DIRECTEUR	DEPT_NO
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ésactiver ou l'activer.

Voici la syntaxe pour activer / désactiver les déclencheurs.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [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é au lieu du déclencheur et du déclencheur COMPOUND.