Oracle Trigger : Ajouter une valeur dans une colonne lors d'une insertion

Un trigger (ou déclencheur) est un bloc PL/SQL associé à une table permettant de déclencher une action avant ou après un INSERT, UPDATE ou DELETE sur cette table. Le trigger Oracle SQL est l’un des objets les plus puissants de la base de données Oracle. Il permet d’automatiser des actions en réponse à des événements précis, sans intervention manuelle.

Pourquoi utiliser un trigger pour remplir une colonne lors d'une insertion ?

Ils permettent de calculer des valeurs dérivées pour d'autres colonnes de la table. Ils constituent ainsi une des solutions pour l'implémentation des attributs dérivés. Ils permettent aussi d'auditer des actions sur une table, de renforcer l'intégrité des données (mais on préférera des contraintes "check", "unique" ou "foreign key" quand c'est possible) et d'exécuter une certaine tâche (e.g. un test) automatiquement lorsqu'un événement arrive.

Principes des triggers

Définition : Un trigger est un bloc PL/SQL qui s’exécute automatiquement en réponse à un événement particulier survenant sur une table, une vue, un schéma ou la base de données elle-même. Le trigger est exécuté une seule fois pour l'instruction insert, update ou delete, même si elle traite plusieurs lignes d'un coup.

Paramètres essentiels :

OR REPLACE : recrée le trigger s’il existe déjà, sans erreur.
BEFORE / AFTER : définit si le trigger s’exécute avant ou après l’événement DML.
INSTEAD OF : remplace l’action DML (uniquement sur les vues).
FOR EACH ROW : trigger de ligne : s’exécute pour chaque ligne affectée.

Avant ou après ?

En général les triggers sont de type "before", en particulier pour les triggers sur ligne, c'est à dire qu'ils s'exécutent avant que l'action considérée soit exécutée, ce qui permet d'infléchir le résultat de cette action. Alors qu'un trigger "after" ne pourra plus modifier le tuple considéré et agira seulement sur d'autres tuples. NB. Vérifier que le résultat est indifférent à before ou after lorsqu'une logique particulière est requise.

Syntaxe générale d'un trigger

CREATE [OR REPLACE] TRIGGER nom_trigger {BEFORE|AFTER}
[INSERT OR][UPDATE [OF nom_colonne] OR][DELETE]
[FOR EACH ROW [WHEN (condition)]]

Accéder et modifier les valeurs de colonne lors d'un INSERT

Pour remplir ou modifier une colonne lors d'une insertion, on utilise un trigger de type ligne (FOR EACH ROW) et la pseudo-variable :NEW pour accéder aux valeurs qui seront insérées. Le trigger s’exécute avant l’insertion si vous voulez changer une valeur de la ligne insérée. Par exemple, pour affecter automatiquement un numéro ou une valeur dérivée :

SQL> insert into employee values ('E14', 'Aziza', 6700, 'Rabat', 'D1');
Numéro nouveau: E141
row created.

Dans ce cas le trigger a montré en console "Numéro nouveau: E141" et la ligne a été insérée. Les "alertes console" peuvent provenir d'appels à dbms_output.put_line pour afficher des informations pendant l'exécution du trigger.

Exemples pratiques et comportements observés

SQL> set serveroutput on /* pour la sortie console */
SQL> insert into employee (enum) values ('E11');
OK <-- affiché par le trigger
1 row created. row created
SQL> select * from employee;
ENUM ENAME SALARY ADDRESS DEPT----- -------------------- ---------- ---------- -----
E14 Aziza 6700 Rabat D1...9 rows selected.
SQL> insert into employee values ('E14', 'Aziza', 6700, 'Rabat', 'D1');
Un nouveau au département: D11 row created.

Ces sorties montrent que le trigger peut afficher des messages et/ou modifier des valeurs insérées. Le trigger peut aussi contrôler l'intégrité métier et lever des erreurs personnalisées via RAISE_APPLICATION_ERROR.

eCognition Deconstructed: NDSM Layer Calculation

Lever une erreur métier depuis un trigger

La procédure RAISE_APPLICATION_ERROR est la méthode Oracle standard pour lever une erreur métier personnalisée et annuler l'opération en cours. Exemple d'utilisation pour empêcher une mise à jour invalidante :

begin
update employee set salary = salary -1 where enum = 'E1';
if SQLCODE = -20001 then dbms_output.put_line(SQLERRM); rollback; -- normalement automatique avec raise_error
end if;
end;

Si le trigger lève l'erreur -20001 avec un message associé, l'opération est annulée :
ERROR at line 1:ORA-20001: new salary < old salary... dans ce cas il soulève l'erreur -20001 et le message associé.

Erreur fréquente : ORA-04091 table en mutation

Description : L’erreur ORA-04091: table [nom] is mutating survient lorsqu’un trigger de ligne (FOR EACH ROW) tente de lire ou de modifier la table sur laquelle il est lui-même défini. Solution recommandée : utiliser un trigger d'instruction (sans FOR EACH ROW) pour les opérations nécessitant une lecture globale de la table, ou recourir au pattern compound trigger (disponible depuis Oracle 11g) qui permet de combiner plusieurs temporalités dans un seul trigger en évitant la mutation.

Bonnes pratiques

Nommez vos triggers de façon explicite : adoptez une convention de nommage claire, par exemple trg_[action]_[table] (ex. trg_audit_employes). Cela facilite la maintenance et le débogage dans les grands schémas Oracle.
Gardez la logique du trigger légère : un trigger ne doit pas contenir des traitements lourds ou des appels à des procédures complexes. Si la logique est volumineuse, externalisez-la dans un package PL/SQL et appelez-le depuis le trigger.
Si vous devez effectuer une certaine tâche (e.g. un test) sans le déclencher, désactivez temporairement le trigger : ALTER TRIGGER nom_trigger DISABLE; puis ALTER TRIGGER nom_trigger ENABLE; pour le réactiver.

Exemples de cas d'usage

Remplir automatiquement une colonne "numéro interne" lors de l'insertion d'un employé.
Tenir un journal d'audit qui enregistre qui a lancé le trigger, l'utilisateur et l'événement.
Vérifier et contrôler des règles métiers (par exemple empêcher une insertion si une condition référentielle n'est pas respectée) et utiliser RAISE_APPLICATION_ERROR pour notifier l'utilisateur.
Traiter de la suppression d'un département en cascade ou en interdisant la suppression si des employés existent.

Différence entre trigger d'instruction et trigger de ligne

Ce trigger se déclenche après chaque mise à jour du champ salaire s'il est de type instruction (sans FOR EACH ROW) : il s’exécute une seule fois par ordre SQL. Alors qu'un trigger FOR EACH ROW s'exécute pour chaque tuple touché par la mise à jour et peut accéder aux valeurs des champs touchés par la mise à jours via :OLD et :NEW.

Résumé - Points clés

Point clé	Détail
Déclenchement	Automatique sur événement DML, DDL ou système
Types principaux	BEFORE, AFTER, INSTEAD OF
Granularité	Par ligne (FOR EACH ROW) ou par instruction
Pseudoenregistrements	:OLD (avant) et :NEW (après) - uniquement FOR EACH ROW
Erreur fréquente	ORA-04091 : table en mutation

balises: