Tutoriel de déclenchement Oracle PL /SQL: Au lieu de, Composé [Exemple]

Dans ce tutoriel, vous apprendrez –

• Avantages des Déclencheurs
• Types de Déclencheurs dans Oracle
• Comment Créer un Déclencheur
• : Clause NEW and:OLD
• AU LIEU DE Trigger
• Déclencheur composé

Avantages des Déclencheurs

Voici les avantages des déclencheurs.

• Génération automatique de certaines valeurs de colonne dérivées
• Application de l’intégrité référentielle
• Enregistrement des événements et stockage des informations sur l’accès aux tables
• Audit
• Réplication synchrone des tables
• Imposition d’autorisations de sécurité
• Prévention des transactions invalides

Les 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
  • AVANT le déclenchement: Il se déclenche avant que l’événement spécifié ne se produise.
  • APRÈS le déclencheur : 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 déclencheur de niveau d’INSTRUCTION level
  • : Il se déclenche une fois pour l’instruction d’événement spécifiée.
  • Déclencheur de niveau de LIGNE: Il se déclenche pour chaque enregistrement affecté dans l’événement spécifié. (uniquement pour DML)
• Classification basée 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é (CRÉER /MODIFIER)
  • Déclencheur DE BASE DE DONNÉES : Il se déclenche lorsque l’événement de base de données est spécifié (OUVERTURE de SESSION /FERMETURE de SESSION / DÉMARRAGE / ARRÊT)

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

Comment créer un déclencheur

Voici la syntaxe pour créer un déclencheur.

CREATE TRIGGER <trigger_name> ON<name of underlying object> 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 dans la création de déclencheurs.
• AVANT / APRÈS spécifiera les horaires de l’événement.
• INSÉRER / METTRE À JOUR / OUVRIR UNE SESSION / CRÉER /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 d’un déclencheur DML.La commande
• « POUR CHAQUE LIGNE » spécifiera le déclencheur de niveau de LIGNE.
• 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 identiques à celles des autres blocs PL/SQL. La partie déclaration et la partie gestion des exceptions sont facultatives.

: Clause NEW and:OLD

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 au niveau de l’ENREGISTREMENT 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écifiera quelle clause est valide pour quelle instruction DML (INSERT /UPDATE /DELETE).

	INSÉRER	METTRE À JOUR	SUPPRIMER
: NOUVEAU	VALIDE	VALIDE	INVALIDE. Il n’y a pas de nouvelle valeur dans la casse delete.
:ANCIEN	INVALIDE. Il n’y a pas d’ancienne valeur dans insert case	VALID	VALID

AU LIEU DE Trigger

« AU LIEU DE trigger » est le 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.

Considérons 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 devient invalide car les données proviennent de 3 tables différentes. Donc, dans ce CAS, AU LIEU DE déclencheur 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 la table emp et

• Table_2 est la table de département.

Ensuite, nous allons voir comment le déclencheur INSTEAD OF est utilisé pour mettre À JOUR l’instruction de détail de l’emplacement sur cette vue complexe. Nous allons également voir comment les éléments :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: Remplissage de la table avec des valeurs d’exemple
• É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éclencheur au lieu de

Étape 1) Création de la table ’emp’ et ‘dept ‘avec des 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 du tableau ’emp’.
• Ligne de code 8-12 : Création du tableau ‘dept’.

Sortie

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

Sortie

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.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of « XXX » to ‘FRANCE’. Il a soulevé l’exception car les instructions DML ne sont pas autorisées dans la vue complexe.

Sortie

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 la rencontre d’erreur lors de la mise à jour de la vue à l’étape précédente, dans cette étape, nous allons utiliser « au lieu de trigger. »

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 déclencheur INSTEAD OF pour l’événement ‘UPDATE’ sur la vue ‘guru99_emp_view’ au niveau de la LIGNE. Il contient l’instruction update pour mettre à jour l’emplacement dans la table de base ‘dept’.
• Ligne de code 44 : l’instruction Update 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 au lieu du déclencheur. Maintenant, l’erreur ne viendra pas car le « au lieu du déclencheur » gérera l’opération de mise à jour de cette vue complexe. Et lorsque le code est exécuté, l’emplacement de l’employé XXX sera mis à jour en « France » 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  » AU LIEU DE  » a arrêté l’instruction de mise à jour réelle en vue et a 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

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
AAAA	SALES	UK
XXX	FINANCIAL	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 de déclencheur unique. Les quatre points de synchronisation différents qu’il prend en charge sont les suivants.

• AVANT le niveau de l’INSTRUCTION
• AVANT le niveau de la LIGNE
• APRÈS le niveau de la LIGNE
• APRÈS le niveau de l’INSTRUCTION

Il permet de combiner les actions pour différents moments dans le même déclencheur.

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

Explication de la syntaxe:

• La syntaxe ci-dessus montre la création d’un déclencheur ‘COMPOSÉ’.
• 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 synchronisation. Nous pouvons créer un déclencheur COMPOSÉ uniquement pour les timings 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_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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 la synchronisation AVANT le niveau de LIGNE pour remplir le salaire avec la valeur par défaut 5000. Cela changera 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.

NOM_EMP	EMP_NO	SALAIRE	GESTIONNAIRE	DEPT_NO
CCC	1004	5000	AAA	30

Déclencheurs d’activation et de désactivation

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.

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

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> 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 d’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 DE trigger et de trigger COMPOSÉ.