Initiation à SQL

Respect des règles de gestion à l'aide de contraintes et de déclencheurs

Dans le monde de l'entreprise, nous devons nous assurer que certaines règles sont toujours respectées. Par exemple, un employé travaillant sur un projet doit être inscrit au registre du personnel. Ou, peut-être, certains événements doivent se produire de manière systématique. Par exemple, si un vendeur réalise une vente, sa commission doit être augmentée.

DB2 Universal Database propose un ensemble de méthodes très utiles pour garantir le respect de ces règles :

Cette première section présente une vue d'ensemble des clés. Dans les sections qui suivent, plusieurs exemples et diagrammes permettent d'étudier les possibilités offertes par l'intégrité référentielle, les contraintes et les déclencheurs.

Clés

Une clé est un ensemble de colonnes à utiliser pour identifier une ou plusieurs lignes ou pour y accéder.

Une clé composée de plusieurs colonnes est appelée clé composée. Dans une table à clé composée, l'ordre des colonnes dans la clé ne correspond pas nécessairement à leur ordre dans la table.

Clés uniques

Une clé unique peut être définie comme une colonne (ou un ensemble de colonnes) qui ne peut pas contenir de valeurs en double. Les colonnes auxquelles s'applique une clé unique ne peuvent pas contenir de valeur NULL. Le respect de la contrainte est assuré par le gestionnaire de bases de données, pendant l'exécution des instructions INSERT et UPDATE. Plusieurs clés uniques peuvent être appliquées à une même table. Facultatives, les clés uniques peuvent être définies dans les instructions CREATE TABLE et ALTER TABLE.

Clés primaires

Une clé primaire est une clé unique faisant partie de la définition de la table. Une table ne peut pas avoir plusieurs clés primaires, et les colonnes concernées par une clé primaire ne peuvent pas contenir de valeur NULL. Facultatives, les clés primaires peuvent être définies dans les instructions CREATE TABLE et ALTER TABLE.

Clés associées

Une clé associée fait partie de la définition d'une contrainte référentielle. Aucune, une ou plusieurs clés associées peuvent être appliquées à une table. La valeur de la clé associée composée est NULL si un des composants de la valeur est NULL. Facultatives, les clés associées peuvent être définies dans les instructions CREATE TABLE et ALTER TABLE.

Contraintes d'unicité

Une contrainte d'unicité garantit que les valeurs d'une clé sont uniques dans une même table. Facultatives, les contraintes d'unicité peuvent être définies avec les instructions CREATE TABLE ou ALTER TABLE, en précisant une clause PRIMARY KEY ou UNIQUE. Par exemple, il est possible de définir une contrainte d'unicité sur la colonne EMPNO (matricules) d'une table, pour garantir que chaque numéro d'employé sera unique.

Contraintes d'intégrité référentielle

Les contraintes d'unicité et les clés associées permettent de définir les relations entre les tables et, par conséquent, de garantir le respect de certaines règles de gestion. Les contraintes utilisant des clés uniques et des clés associées sont couramment appelées contraintes d'intégrité référentielle. Une contrainte d'unicité référencée par une clé associée est appelée clé parente. Une clé associée se réfère à une clé parente spécifique, ou elle lui est reliée. Par exemple, une règle peut fixer que tous les employés (table EMPLOYEE) doivent appartenir à un service existant (table DEPARTMENT). Ainsi, le numéro de service défini dans la table EMPLOYEE sera utilisé comme clé associée et le numéro de service défini dans la table DEPARTMENT sera utilisé comme clé primaire. Le diagramme suivant illustre les contraintes d'intégrité référentielle :

Figure 4. Les contraintes primaires et associées définissent les relations entre les données et assurent leur protection


REQTEXT

Contraintes de vérification de table

Les contraintes de vérification de table indiquent les conditions qui sont vérifiées pour chaque ligne d'une table. Il est possible d'indiquer des contraintes de vérification de table individuelles. Elles peuvent être ajoutées au moyen des instructions CREATE ou ALTER TABLE.

L'instruction ci-dessous crée une table devant respecter les contraintes suivantes :

 
     CREATE TABLE EMP
           (ID           SMALLINT NOT NULL,
            NAME         VARCHAR(9),
            DEPT         SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
            JOB          CHAR(5)   CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),
            HIREDATE     DATE,
            SALARY       DECIMAL(7,2),
            COMM         DECIMAL(7,2),
            CLE PRIMAIRE (ID),
            CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )

Il n'y a violation de contrainte que si l'évaluation donne un résultat faux. Par exemple, si DEPT a une valeur NULL sur une ligne insérée, l'insertion se fait sans erreur, bien que la contrainte demande que les numéros de service soient compris entre 10 et 100.

L'instruction suivante indique pour la table EMPLOYEE une contrainte COMP selon laquelle le salaire compensé total de l'employé doit dépasser 15000 F :

 
     ALTER TABLE EMP
        ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)

Le respect de la nouvelle contrainte doit également être vérifié dans les lignes existantes de la table. Il est possible de différer cette vérification, en utilisant l'instruction SET CONSTRAINTS de la manière suivante :

     SET CONSTRAINTS FOR EMP OFF
     ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
     SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED
 

Tout d'abord, l'instruction SET CONSTRAINTS permet de différer la vérification des contraintes de la la table. Ensuite, une ou plusieurs contraintes peuvent être ajoutées à la table sans être vérifiées. Enfin, l'instruction SET CONSTRAINTS, émise une nouvelle fois, réactive la vérification de la table, pour que les vérifications différées, s'il y en a, aient lieu.

Déclencheurs

Un déclencheur définit un ensemble d'actions activé par une opération qui modifie les données dans la table de base précisée.

Les déclencheurs peuvent être utilisés pour :

Les déclencheurs permettent de développer plus rapidement des applications, d'imposer des règles de gestion globales et de faciliter la maintenance des applications et des données.

DB2 Universal Database accepte plusieurs types de déclencheurs. La définition des déclencheurs peut prévoir qu'ils soient activés avant ou après une opération DELETE, INSERT ou UPDATE. Chaque déclencheur comporte un ensemble d'instructions SQL, appelé action déclenchée, qui peut inclure une condition de recherche facultative.

La définition des déclencheurs AFTER peut également préciser que l'action doit être déclenchée à chaque ligne ou une seule fois pour l'instruction entière, tandis que les déclencheurs BEFORE appliquent toujours l'action déclenchée à chacune des lignes.

Un déclencheur placé avant une instruction INSERT, UPDATE ou DELETE permet de vérifier que certaines conditions sont remplies avant d'exécuter une opération de déclenchement ou de modifier des valeurs avant de les stocker dans la table.

Un déclencheur AFTER permet de transmettre des valeurs en fonction des besoins ou d'exécuter d'autres tâches nécessaires au cours de l'opération de déclenchement, comme l'envoi d'un message.

L'exemple suivant décrit l'utilisation de déclencheurs BEFORE et AFTER. Prenons le cas d'une application qui enregistre et suit les modifications des cours de la Bourse. La base de données contient deux tables, CURRENTQUOTE et QUOTEHISTORY :

 
     CREATE TABLE CURRENTQUOTE
     (SYMBOL VARCHAR(10),
      QUOTE DECIMAL(5,2),
      STATUS VARCHAR(9))
     
     CREATE TABLE  QUOTEHISTORY
     (SYMBOL VARCHAR(10),
      QUOTE DECIMAL(5,2),
      TIMESTAMP TIMESTAMP)

Lorsque la colonne QUOTE de CURRENTQUOTE est mise à jour à l'aide d'une instruction telle que :

 
     UPDATE CURRENTQUOTE
        SET QUOTE = 68.5
        WHERE SYMBOLE = 'IBM'

La colonne STATUS de CURRENTQUOTE doit être mise à jour pour indiquer si la valeur de l'action :

Cette mise à jour s'effectue à l'aide du déclencheur BEFORE ci-dessous :

(1)

     CREATE TRIGGER STOCK_STATUS
        NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
        REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
        FOR EACH ROW MODE DB2SQL

(2)

      SET NEWQUOTE.STATUS =

(3)

         CASE

(4)

            WHEN NEWQUOTE.QUOTE >=
                      (SELECT MAX(QUOTE)
                          FROM QUOTEHISTORY
                          WHERE SYMBOL = NEWQUOTE.SYMBOL
                            AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
            THEN 'En hausse'

(5)

          WHEN NEWQUOTE.QUOTE <=                             
                      (SELECT MIN(QUOTE)
                          FROM QUOTEHISTORY
                          WHERE SYMBOL = NEWQUOTE.SYMBOL
                          AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
          THEN 'En baisse'

(6)

          WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
             THEN 'En hausse'
          WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE                       
             THEN 'En baisse'
          WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
             THEN 'Stable'
     END                                                  
 

(1)
Ce bloc de syntaxe définit STOCK_STATUS en tant que déclencheur avant la mise à jour de la colonne QUOTE de la table CURRENTQUOTE. La deuxième ligne indique que l'action doit être déclenchée avant l'application, dans la base de données, des modifications qu'entraînerait la mise à jour de la table CURRENTQUOTE. La clause NO CASCADE signifie que l'action déclenchée n'activera pas elle-même d'autres déclencheurs. La troisième ligne précise les noms à utiliser comme qualificatifs de nom de colonne pour les nouvelles valeurs (NEWQUOTE) et les anciennes (OLDQUOTE). Les noms de colonne que NEWQUOTE et OLDQUOTE (noms de corrélation) qualifient sont appelés variables de transition. La quatrième ligne indique que l'action déclenchée doit s'exécuter pour chaque ligne.

(2)
Ce bloc indique le début de la première et unique instruction SQL de l'action déclenchée par ce déclencheur. Sur la ligne mise à jour par l'instruction ayant activé le déclencheur, l'instruction SET variable-transition affecte une valeur à une colonne. Dans notre exemple, l'instruction affecte une valeur à la colonne STATUS de la table CURRENTQUOTE.

(3)
L'expression utilisée à droite de l'affectation est une expression CASE. Elle se termine par le mot clé END.

(4)
Cette première expression CASE vérifie si la nouvelle cotation (NEWQUOTE.QUOTE) dépasse la valeur maximale des actions boursières fixée pour l'année en cours. La sous-requête utilise la table QUOTEHISTORY mise à jour par le déclencheur AFTER qui suit.

(5)
Cette deuxième expression CASE vérifie si la nouvelle cotation (NEWQUOTE.QUOTE) est inférieure à la valeur minimale des actions boursières fixée pour l'année en cours. La sous-requête utilise la table QUOTEHISTORY mise à jour par le déclencheur AFTER qui suit.

(6)
Ces trois dernières expressions CASE déterminent si la nouvelle cotation (NEWQUOTE.QUOTE) est supérieure, inférieure ou égale à la cotation (OLDQUOTE.QUOTE) comprise dans la table. L'instruction SET variable-transition se termine ici.

Outre la mise à jour de l'entrée dans la table CURRENTQUOTE, vous devez créer un enregistrement d'audit en copiant la nouvelle cotation ainsi que l'horodatage, dans la table QUOTEHISTORY. Cette mise à jour s'effectue à l'aide du déclencheur AFTER ci-dessous :

(1)

     CREATE TRIGGER RECORD_HISTORY                                  
     AFTER UPDATE OF QUOTE ON CURRENTQUOTE                                 
     REFERENCING NEW AS NEWQUOTE                                     
     FOR EACH ROW MODE DB2SQL                                    
     BEGIN ATOMIC                                                       

(2)

     INSERT INTO QUOTEHISTORY                                           
        VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);  
     END                                                         

(1)
Ce bloc de syntaxe définit que le déclencheur RECORD_HISTORY devra être activé après la mise à jour de la colonne QUOTE de la table CURRENTQUOTE. La troisième ligne indique le nom qui doit qualifier le nom de colonne de la nouvelle valeur (NEWQUOTE). La quatrième ligne indique que l'action déclenchée doit s'exécuter pour chaque ligne.

(2)
L'action entraînée par ce déclencheur comprend une seule instruction SQL qui insère une ligne dans la table QUOTEHISTORY à l'aide des données de la ligne mise à jour (NEWQUOTE.SYMBOL et NEWQUOTE.QUOTE) et de l'horodatage en cours.

CURRENT DATESTAMP est un registre spécial contenant l'horodatage. Vous trouverez une liste et une explication des registres à la section Registres spéciaux.


[ Début de page | Page précédente | Page suivante | Table des matières | Index ]