Oracle Déclencheur PL/SQL : au lieu de types composés

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

Les déclencheurs sont stockés PL / SQL programmes qui sont déclenchés par le Oracle moteur automatiquement lorsque Instructions DML Les opérations d'insertion, de mise à jour et de suppression sont exécutées sur la table, ou lors de la survenue de certains événements. Le code à exécuter dans le cas d'un déclencheur peut être défini selon les besoins. Vous pouvez choisir l'événement qui déclenche le déclencheur et le moment de son exécution. L'objectif d'un déclencheur est de garantir l'intégrité des informations dans 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.

Classification basée sur le moment

• AVANT Déclenchement : Il se déclenche avant que l'événement spécifié ne se produise.
• APRÈS Déclencheur : Il se déclenche après la survenue de l'événement spécifié.
• AU LIEU DE Déclencheur : Un type particulier. Vous en apprendrez davantage dans les sections suivantes. (Réservé aux utilisateurs de DML)

Classification basée sur le niveau

• Déclencheur de niveau ÉTAPE : Elle se déclenche une seule fois pour l'instruction d'événement spécifiée.
• Déclencheur au niveau de la LIGNE : Elle se déclenche pour chaque enregistrement affecté par l'événement spécifié. (uniquement pour les opérations 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é (CREATE/ALTER).
• Déclencheur de base de données : Il se déclenche lorsque l'événement de base de données est spécifié (CONNEXION/DÉCOUVERTE/DÉMARRAGE/ARRÊT).

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

Comment créer un déclencheur

Vous trouverez ci-dessous la syntaxe pour créer un déclencheur. La capture d'écran ci-dessous illustre cette syntaxe. Oracle.

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 le moment 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 spécifie l'objet sur lequel l'événement mentionné ci-dessus est valide. Par exemple, il s'agit du 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 au niveau de la LIGNE.
• La clause WHEN précisera la condition supplémentaire dans laquelle le déclencheur doit s'activer.
• La partie déclaration, la partie exécution et la partie gestion des exceptions sont identiques à celles des autres Blocs PL/SQLLa partie déclaration et la gestion des exceptions Certaines parties 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 Le déclencheur au niveau de la ligne comporte deux clauses permettant de stocker ces valeurs. Ces clauses permettent de faire référence aux anciennes et nouvelles valeurs dans le corps du déclencheur.

• :NOUVEAU – Elle contient une nouvelle valeur pour les colonnes de la table/vue de base lors de l'exécution du déclencheur.
• :VIEUX – Elle conserve 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 indique quelle clause est valide pour quelle instruction DML (INSERT/UPDATE/DELETE).

AU LIEU DE Déclencheur

Un déclencheur « INSTEAD OF » est un type particulier de déclencheur. Il est utilisé uniquement dans les déclencheurs DML. Il est utilisé lorsqu'un événement DML est sur le point de se produire sur une vue complexe.

Prenons l'exemple d'une vue construite à partir de trois tables de base. Toute opération DML effectuée sur cette vue sera invalide car les données proviennent de trois tables différentes. Dans ce cas, un déclencheur INSTEAD OF est utilisé. Ce déclencheur permet de 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, où Table_1 est la table des employés et Table_2 est la table des départements.

Nous allons ensuite voir comment le déclencheur INSTEAD OF est utilisé pour mettre à jour les détails de localisation dans cette vue complexe. Nous verrons également l'utilité des attributs :NEW et :OLD dans les déclencheurs. L'exemple se déroule en plusieurs étapes :

• Étape 1 : Création des tables « emp » et « dept » avec les colonnes appropriées
• Étape 2 : Remplissage des tableaux avec des valeurs d’exemple
• Étape 3 : Création d’une vue pour les tables créées ci-dessus
• Étape 4 : Mise à jour de la vue avant le déclencheur INSTEAD OF
• Étape 5 : Création du déclencheur INSTEAD OF
• Étape 6 : Mise à jour de la vue après le déclencheur INSTEAD OF

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

La capture d'écran ci-dessous montre la création des tables de base « emp » et « dept » dans Oracle.

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

Code Explication

• Code lignes 1-7 : Création de la table 'emp'.
• Code lignes 8-12 : Création de la table « département ».

Sortie :

Table Created

Étape 2) Maintenant que nous avons créé les tableaux, nous allons les remplir avec des exemples de valeurs.

La capture d'écran ci-dessous montre les lignes d'exemple insérées dans les tables « dept » et « emp ».

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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Explication

• Code lignes 13-19 : Insertion des données dans la table « département ».
• Code lignes 20-26 : Insertion des données dans la table 'emp'.

Sortie :

PL/SQL procedure completed

Étape 3) Création d'une vue pour les tables créées ci-dessus.

La capture d'écran ci-dessous montre la création puis l'interrogation de la vue complexe.

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;

Code Explication

• Code lignes 27-32 : Création de la vue 'guru99_emp_view'.
• Code ligne 33: Interrogation de guru99_emp_view.

Sortie :

View created

Étape 4) Mise à jour de la vue avant le déclencheur INSTEAD OF.

La capture d'écran ci-dessous illustre la tentative de mise à jour de la vue complexe et l'erreur qui en résulte.

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

Code Explication

• Code lignes 34-38 : Mettez à jour l'emplacement de « XXX » à « FRANCE ». Une exception a été levée car les instructions DML ne sont pas autorisées directement sur la vue complexe.

Sortie :

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Étape 5) Pour éviter l'erreur rencontrée lors de la mise à jour de la vue à l'étape précédente, nous allons utiliser un déclencheur « INSTEAD OF » dans cette étape.

La capture d'écran ci-dessous montre la création du déclencheur INSTEAD OF.

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

Code Explication

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

Sortie :

Trigger Created

Étape 6) Mise à jour de la vue après le déclenchement INSTEAD OF. L'erreur ne s'affichera plus, car le déclencheur INSTEAD OF gérera la mise à jour de cette vue complexe. Lors de l'exécution du code, la localisation de l'employé XXX passera de « Japon » à « France ».

La capture d'écran ci-dessous illustre la mise à jour réussie via le déclencheur INSTEAD OF et la vue actualisée.

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

SELECT * FROM guru99_emp_view;

Code Explication:

• Code lignes 49-53 : Mise à jour de l'emplacement de « XXX » vers « FRANCE ». Cette opération a réussi car le déclencheur « INSTEAD OF » a interrompu l'instruction de mise à jour de la vue et a effectué la mise à jour de la table de base.
• Code ligne 55: Vérification de l'enregistrement mis à jour.

Sortie :

PL/SQL procedure successfully completed

Déclencheur composé

Le déclencheur composé est un déclencheur qui permet de définir des actions pour chacun des quatre points de temporisation dans un seul corps de déclencheur. Les quatre points de temporisation différents qu'il prend en charge sont indiqués ci-dessous.

• 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 en un seul déclencheur.

La capture d'écran ci-dessous illustre la syntaxe du déclencheur composé avec ses quatre sections de temporisation.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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 illustre la création d'un déclencheur « COMPOUND ».
• La section déclarative est commune à tous les blocs d'exécution du corps du déclencheur.
• Ces quatre blocs de temporisation peuvent être disposés dans n'importe quel ordre. Il n'est pas obligatoire de les utiliser tous les quatre. On peut créer un déclencheur COMPOSÉ uniquement pour les temporisations nécessaires.

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.

La capture d'écran ci-dessous montre un exemple de déclencheur composé et son résultat.

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;

Code Explication:

• Code lignes 2-10 : Création du déclencheur composé. Il est créé pour le niveau de temporisation AVANT LIGNE afin de renseigner le salaire avec la valeur par défaut 5000. Cela modifiera le salaire à la valeur par défaut « 5000 » avant l'insertion de l'enregistrement dans la table.
• Code lignes 11-14 : Insérer l'enregistrement dans la table 'emp'.
• Code ligne 16: Vérification de l'enregistrement inséré.

Sortie :

Trigger created

PL/SQL procedure successfully completed.

Activation et désactivation des déclencheurs

Les déclencheurs peuvent être activés ou désactivés. Pour ce faire, une instruction ALTER (DDL) doit être fournie pour le déclencheur concerné.

Vous trouverez ci-dessous la syntaxe permettant d'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 un déclencheur unique.
• La deuxième instruction montre comment activer/désactiver tous les déclencheurs sur une table particulière.

FAQ