[SQL] Créer des triggers basiques sur DB2/400

Publié le par Vincent L

Il est toujours intéressant de savoir comment fonctionne une base de données s’exécutant sur un système que l’on connait peu, voire pas du tout. C’est la raison pour laquelle aujourd’hui, nous allons voir ensemble comment créer un trigger, ou en français, un déclencheur.

Un trigger est une sorte de programme, qui permet d’effectuer une ou plusieurs actions spécifiques, en fonction des évènements qui se produisent. Il diffère d’une procédure stockée par le fait qu’on ne l’appelle pas explicitement. On le retrouve dans de nombreux SGBD (Système de Gestion de Base de Données) tels que Oracle ou MySQL, mais aussi dans DB2 (Linux, OS/400). Cependant la syntaxe peut varier en fonction de la base de données utilisée.

Dans notre exemple, nous allons utiliser :

  • Une table de base, contenant deux champs, le code d’un article (10 caractères), ainsi qu’un libellé (50 caractères). Elle s’appellera “VLTEST”.
  • Une seconde table d’historique, contenant les deux champs de la table précédente, ainsi qu’un champ supplémentaire “date” (numérique de 8 dont 0) et un champ de 2 caractères pour indiquer le type de trigger (exemple : si c’est un déclencheur “After Insert”, on inscrira “AI” dans le champ). La table en question s’appellera “VLTESTHI”.

Des requêtes seront exécutées, aussi bien avec l’interpréteur SQL fourni dans le système i qu’avec un interpréteur basique écrit en WinDev, mais aussi par l’intermédiaire de fonctions natives WinDev (HAjoute, HModifie), et via des instructions en Java exploitant le pilote JDBC adéquat.Nous supposons que le schéma de la base de données utilisées sera la bibliothèque “VLECOMTE”, spécialement créée pour tous les tests de ce genre. La norme d’écriture des requêtes reste liée au système, donc la notation “schéma + table” utilisée ressemblera à “nom_schema/nom_table”.

Les triggers sur AS/400

Sachez que sur l’AS/400, les méthodes principales pour créer les déclencheurs sont les suivantes (peut-être en existe-t-il d’autres plus simples ?) :

  • Création d’un programme en RPG : cette façon de procéder est plus compliquée, car il faut jouer sur les tailles des différents “membres” d’une table. Le programme doit ensuite être associé à la table via la commande ADDPFTRG. Cette commande doit être répétée plusieurs fois pour tous les types de trigger que l’on veut créer (avant/après insertion, avant/après mise à jour, etc).
  • Création d’un trigger via le langage procédural SQL : il s’agit là de la méthode la plus facile, mais pas forcément disponible sur toutes les versions du système ; cela dépend de la version interne de la base de données. On utilise alors la syntaxe CREATE TRIGGER pour créer, et DROP TRIGGER pour supprimer.

Triggers en PL/SQL

Le “Procedural Language / SQL” est un langage propriétaire, créé par Oracle et intégré à des SGBD comme TimesTen-In-Memory Database, DB2 depuis la version 9.7, et bien sûr, dans le système du même nom que son propriétaire. Il permet entre autres de coupler des requêtes SQL à des instructions simples comme des boucles ou des conditions. Le résultat est ensuite stocké sur le serveur sous forme de traitements particuliers. Les procédures stockées peuvent donc être écrites dans ce langage.

Créer notre premier trigger

Nous supposons ici que vous avez déjà créé vos tables et donc que vous êtes prêts à entamer le tutoriel. N’oubliez pas que les requêtes SQL et les déclencheurs sont créés dans un schéma particulier, donc n’oubliez pas de changer le contenu de celles-ci si vous utilisez des bibliothèques de développement différentes.

Syntaxe

La documentation officielle d’IBM nous décrit, de manière très détaillée, les différentes clauses valides pour la création d’un trigger. Vous pouvez la trouver en suivant le lien suivant, “CREATE TRIGGER Statement” (en anglais), ou vous pouvez jeter un œil à l’image suivante, copiée depuis cette même source (cliquez pour agrandir).

Code et explications

Dans le premier exemple, nous allons créer un déclencheur qui réagit à l’ajout d’une ligne. Plus particulièrement, celui-ci devra déclencher “après insertion” pour chaque nouvelle ligne. Il exécutera une requête SQL qui insèrera des valeurs dans la table d’historique.

CREATE NEW TRIGGER vlecomte/vltest_ai 
AFTER INSERT ON vlecomte/vltest REFERENCING NEW AS post 
FOR EACH ROW 
BEGIN ATOMIC
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ) 
	VALUES (post.cart, post.lib, 20120101, 'AI'); 
END

Quelques précisions :

  • La clause “Create Trigger” est suivie du nom du déclencheur. Mieux encore : on peut directement spécifier dans quel schéma celui-ci doit être créé.
  • La clause “After Insert On” est suivie du nom de la table sur laquelle on va appliquer le trigger. On a bien précisé “après insertion” dans ce cas, donc cela déclenchera bien une fois que les données auront été intégrées.
  • La clause “Referencing” permet de définir les alias pour les tables ou les anciennes et nouvelles variables, qui pourront être réutilisées lors du traitement, dans les conditions, les boucles, et même les requêtes.
  • La clause “For each row” indique que le traitement doit déclencher pour chaque nouvelle ligne insérée.
  • Entre la clause “Begin Atomic” et “End”, on a placé une requête SQL. On peut en placer autant que l’on veut (nous le verrons après). Certains types de traitements ne peuvent pas être exécutés en fonction du type de déclencheur.
  • La date utilisée ici est écrite en dur.
  • Contraintes à prendre en compte : voir plus bas.

Lorsque le trigger est créé, on reçoit un message de confirmation (s’il est créé via l’interpréteur “Start SQL” du système). En explorant la bibliothèque dans laquelle on l’a créé, on peut apercevoir un objet de type *PGM appelé “VLTEST_AI”, qui a été créé automatiquement et qui a été automatiquement associé à la table. C’est ce qu’on aurait du faire si on avait utilisé la méthode compliquée : coder le programme pour le déclencheur puis l’associer avec la commande AS/400 adéquate.

Insertion de la 1re ligne

INSERT INTO vlecomte/vltest 
VALUES ('05030101','ASTERIX');

En vérifiant dans la table d’historique (VLTESTHI), on retrouve bien une ligne avec le code article “05030101”, le libellé “Astérix”, la date en dur “20120926” et le type du trigger qui a déclenché.

Amélioration

Afin d’insérer la bonne date à chaque fois que le trigger est exécuté, on doit modifier la requête d’insertion du trigger. On va donc le recréer mais d’abord, il faut que vous le supprimez au moyen de l’instruction “DROP TRIGGER”.

Code et explications

Dans l’exemple ci-dessous, nous allons nous servir des variables globales et des fonctions proposées par le système de base de données. Par exemple, CURRENT_DATE permet de renvoyer la date du jour dans un format spécifique. Il faut alors transformer dans une date qui nous convient, comme par exemple une date au format “AAAAMMJJ” (année-mois-jour).

CREATE NEW TRIGGER vlecomte/vltest_ai 
AFTER INSERT ON vlecomte/vltest REFERENCING NEW AS post 
FOR EACH ROW 
BEGIN ATOMIC 
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ) 
	VALUES (post.cart, post.lib, 
		YEAR(DATE(DAYS(CURRENT DATE))) * 1000 
		+ MONTH(DATE(DAYS(CURRENT DATE))) * 100 
		+ DAY(DATE(DAYS(CURRENT DATE)), 'AI'); 
END

La partie qui nous intéresse est une nouvelle fois en rouge : il s’agit de générer une date au bon format avec les différentes fonctions et variables fournies. On traite les années, les mois, et les jours, et on additionne le tout pour obtenir une date comme ceci : “20120926” (c’est la date à laquelle cet article a été rédigé).

Insertion de la 2ème ligne

C’est avec WinDev et les fonctions natives que nous créons une nouvelle ligne. Pour cela, il sera nécessaire d’utiliser le module Easycom (payant). Notez qu’il est aussi possible d’utiliser la fonction “HExecuteRequeteSQL” pour lancer directement une requête texte.

Une fois ce code exécuté, regardez à nouveau le contenu de votre table d’historique. Vous verrez apparaitre l’article “05050102” avec le libellé “TEST ART” et la bonne date, cette fois-ci.

Trigger après mise à jour

Voici un exemple de trigger qui déclenchera après une mise à jour. Étant donné que lorsqu’on écrit un déclencheur “avant mise à jour”, on ne peut exécuter de requête SQL dans le corps de celui-ci. C’est l’une des contraintes que nous évoquerons plus bas dans cet article.

Code et explications

Ce déclencheur s’exécutera lorsqu’une mise à jour sera effectuée (“après la mise à jour”), et ce pour chacune des lignes. On veut conserver, pour chaque ligne, le record avant et après sa modification.

Quelques petites précisions :

  • On a déclaré un alias pour pouvoir interagir avec les anciennes valeurs (clause REFERENCING … OLD AS…). “Old” et “New” ne peuvent pas être utilisés dans tous les types de triggers. Remarque : il s’agit de variables de transition.
  • On a modifié la valeur à insérer dans la dernière colonne pour qu’il indique qu’il s’agit d’un déclencheur de mise à jour (“BU” et “AU”).

Mise à jour d’une ligne

CREATE NEW TRIGGER vlecomte/vltest_bu 
AFTER UPDATE ON vlecomte/vltest 
  REFERENCING NEW AS post OLD AS pre 
FOR EACH ROW
BEGIN ATOMIC 
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ) 
	VALUES (pre.cart, pre.lib, 
		YEAR(DATE(DAYS(CURRENT DATE))) * 1000
		+ MONTH(DATE(DAYS(CURRENT DATE))) * 100 
		+ DAY(DATE(DAYS(CURRENT DATE)), 'BU');
	INSERT INTO vlecomte/vltesthi (cart, lib, vldat, typ)
	VALUES (post.cart, post.lib, 
		YEAR(DATE(DAYS(CURRENT DATE))) * 1000 
		+ MONTH(DATE(DAYS(CURRENT DATE))) * 100 
		+ DAY(DATE(DAYS(CURRENT DATE)), 'AU');
END

Effectuez une requête de mise à jour sur l’article “05030101” inséré lors du premier exemple, et modifiez le libellé en “Modification Libellé”. Normalement, deux lignes s’ajouteront dans la table d’historique. Essayez également d’effectuer cette requête en Java en utilisant le bon pilote JDBC (cela a été expliqué sur ce blog).

Contraintes à prendre en compte

  • On ne peut pas créer un trigger sur une table en cours d’utilisation.
  • Pour pouvoir créer un trigger, il faut suffisamment de privilèges.
  • Les instructions suivantes disponibles pour le corps du déclencheur :

  • Tables et variables de transition exploitables ou non avec la clause “Referencing”, en fonction du type de déclencheur :

Sources

Syntaxe du CREATE TRIGGER

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *