| 
Pour compléter la notion d'intégrité référentielle, Il existe la notion de déclencheur ou "TRIGGER". (non encore normalisé, cela sera l'objet d"une norme SQL-3) un trigger est une procédure enregistrée par le gestionnaire et associée à un évènement sur la base. Syntaxe: CREATE TRIGGER nom-trigger évement ON nom-table [WHEN(condition à vérifier)] instructions SQL [FOR EACH ROW/STATEMENT] |
|
évènements possibles: BEFORE UPDATE BEFORE INSERT BEFORE DELETE AFTER UPDATE AFTER INSERT AFTER DELETE en ce qui concerne la mise à jour , certains SGBD prévoient la notion de colonne. (en cas de modification de CETTE COLONNE). sous la forme : AFTER UPDATE(colonne) ON table. WHEN(conditions) facultatif, même syntaxe que la clause WHERE instruction SQL, toute instruction (sauf SELECT) c.a.d UPDATE, DELETE, INSERT autorisés: sous-sélections (SELECT imbriqués) instructions multiples FOR EACH ROW: trigger exécuté pour chaque enregistrement (màj multiple,...) FOR STATEMENT: trigger exécuté une fois en fin d'instruction SQL. |
|
Quelques exemples: soit la table lcommand contenant QTECDE (quantité commandée) QTELIV (quantité déja livrée) il s'agit d'assurer la cohérence de la zone QTELIV fonction des actions portant sur la table livraison. CREATE TRIGGER liv-add AFTER INSERT ON livraison UPDATE lcommand SET QTELIV = (SELECT SUM(qtelivl) FROM livraison WHERE numcdel = numcde AND nolignl = nolign) CREATE TRIGGER liv-dlt AFTER DELETE ON livraison .... même syntaxe .... CREATE TRIGGER liv-upd AFTER UPDATE(qtelivl) ON livraison .... même syntaxe .... |
|
Quelques exemples: soit la table command contenant NUMCDE (N° de commande) il s'agit d'incrémenter le N° de commande automatiquement (dernier n° connu + 1) CREATE TRIGGER numcde_auto AFTER INSERT ON command WHEN(numcde = 0) UPDATE command SET numcde = (SELECT max(numcde) FROM command) + 1 Il suffit de ne pas renseigner le N° de commande lors de l'ajout (valeur 0 par défaut), pour que le trigger lui assigne un N°. Historisation automatique de la suppression d'un client CREATE TRIGGER histo-cli BEFORE DELETE ON clients INSERT into clilog (numcli, quand, qui) VALUES(NUMCLI, CURRENT_TIMESTAMP, USER) |
|
Les triggers permettent aussi de gérer l'intégrité référentielle, particulièrement les suppressions en cascade. CREATE TRIGGER cli-sup BEFORE DELETE ON clients (DELETE FROM command WHERE CDECLI = NUMCLI) FOR EACH ROW (DELETE FROM lcommand WHERE LNUMCDE = NUMCDE) FOR EACH ROW INSERT into clilog (numcli, quand, qui) VALUES(NUMCLI, CURRENT_TIMESTAMP, USER) FOR EACH ROW suppression de toutes les commandes de ce client (entètes et lignes) et historisation. Certains SGBD autorisent la déclaration de variables internes aux trigger, et l'utilisation de boucles et de tests. il s'agit alors de véritables programmes (ORACLE V7 par exemple) (Voir DB2/400) |
|
Exemple: Réappro automatique: CREATE TRIGGER reappro AFTER UPDATE(qtestock) ON article FOR EACH ROW WHEN(qtestock < qtemini) DECLARE x INTEGER BEGIN SELECT COUNT(*) INTO x FROM cdefournis WHERE cdfart = codart IF (x = 0) THEN INSERT INTO cdefourmis VALUES(artfour, codart, qtereappro, CURRENT_DATE) ENDIF; END; |
|
Exemple: Ce qui aurait pu s'écrire CREATE TRIGGER reappro AFTER UPDATE(qtestock) ON article FOR EACH ROW WHEN(qtestock < qtemini AND NOT EXIST (SELECT * FROM cdefournis WHERE cdfart = codart) ) INSERT INTO cdefourmis VALUES(artfour, codart, qtereappro, CURRENT_DATE) |
|
Mise en oeuvre sur AS/400 avec DB2/400 ADDPFTRG FILE(mabib/command) TRGTIME(*AFTER) <- *AFTER/*BEFORE TRGEVENT(*INSERT) <- *INSERT/*DELETE/*UPDATE PGM(mabib/monprog) RPLTRG(*YES/*NO) TRGUPDCND(*ALWAYS/*CHANGE) *ALWAYS = appel même en cas de Màj à l'identique *CHANGE = uniquement en cas de Màj réelle. Le pgm est écrit dans le langage de votre choix !!! Les performances relèvent du CALL Les triggers recoivent deux paramètres peuvent envoyer un message de type *ESCAPE pour interdir la transaction. En environnement ILE compiler le pgm ACTGRP(*CALLER) [même groupe d'activation) |
|
Paramètres reçus: 1/ une structure complexe (voir ci dessous) 2/ 4 octets binaires : lg de la structure .......................................................................... : de : A : Type : signification : : 1 : 10 : CHAR : nom du fichier : : 11 : 20 : CHAR : nom de la bibliothèque : : 21 : 30 : CHAR : nom du membre : : 31 : 31 : CHAR : 1=Insert , 2=Delete , 3=Update : : 32 : 32 : CHAR : 1=After , 2=Before : : 33 : 33 : CHAR : niveau de verrouillage (de 0=*NONE à 3=*ALL) : : 49 : 52 : BIN : (position - 1) de l'image avant : : 53 : 56 : BIN : lg de l'image avant : : 65 : 68 : BIN : (position - 1) de l'image après : : 69 : 72 : BIN : lg de l'image après : : ?? : ?? : CHAR : Image avant : : ?? : ?? : CHAR : Image après : :........................................................................: |
|
Et ENFIN les procédures cataloguées permettent l'appel de programmes avec passage de paramètres, sous une forme standardisée, et éventuellement sur un système distant. Très intéressant dans une architecture client/serveur, l'OS/400 accepterait des procédures cataloguées écrites dans n'importe quel langage: (y compris REXX) EXEC SQL CALL PGM1 (:nocli, :datecde) <- peut être RPG, COBOL, .... END-EXEC. PGM1 peut tout à fait ne PAS contenir d'ordre SQL. Cette technique est admise par le driver ODBC |
|
Pour terminer petite comparaison DB2/400 - ORACLE (source NEWS 3X) ------------------------------------------------------------------- + A part SQLCODE = 0 et 100, les codes retour ne sont pas compatibles + DB2 reconnait les curseurs dynamiques (FETCH PRIOR), ORACLE non. + ORACLE demande autant de variables résultat lors d'un FETCH, qu'il y a de colonnes extraites, DB2 accepte les structures (DS ou niveau 01) + ORACLE vous laisse la possibilité de "conseiller" le query governor. + ORACLE reconnait les jointures externes, DB2 uniquement depuis V3R10 + ORACLE reconnait NOT NULL , pas NOT NULL WITH DEFAULT (CREATE TABLE) + DB2 reconnait les variables ISO : DATE, TIME, TIMESTAMP , pas ORACLE + ORACLE est capable de stocker des "Binary Large Objects", pas DB2 (V4 ?) + ORACLE reconnait l'ordre SQL CREATE TRIGGER, DB2 la cde ADDPFTRG |
|
+ ORACLE n'admet que les triggers écrits avec le langage PL/SQL (le langage de programmation d'oracle) avec DB2 , les triggers sont écrits dans le langage de votre choix. + ORACLE connait l'ordre ALTER TABLE pour retirer, ajouter ou modifier une colonne, DB2 uniquement depuis les versions V3R60, V3R20. Sur l'ensemble, on peut considerer que DB2/400 n'a pas à rougir de la comparaison, sachant qu'il s'agit d'une base de donnée intégrée. Pour terminer ORACLE annonce le support de DB2/400 comme serveur de données dans un environnement client/serveur via les outils CDE.2 ET le support des bases ORACLE sous DRDA avec la définition d'un pilote (même principe que le driver ODBC) valide avec ADDRDBDIRE ARDPGM() . Voir la documentation "CALL LEVEL INTERFACE" . |
