SQL Erste Schritte

Durchsetzen von Geschäftsregeln mit Hilfe von Integritätsbedingungen und Auslösern

In der Geschäftswelt gibt es bestimmte Regeln, deren ständige Beachtung sichergestellt sein muß. Ein mit einem Projekt befaßter Mitarbeiter muß beispielsweise auf der Gehaltsliste stehen. Denkbar sind auch bestimmte Ereignisse, die automatisch eintreten sollen. Dies könnte beispielsweise bei einem Verkaufsmitarbeiter der Fall sein, dessen Provision erhöht werden soll, wenn er einen Verkauf abschließt.

DB2 Universal Database enthält zu diesem Zweck eine Reihe nützlicher Methoden:

Der erste Abschnitt bietet Ihnen eine konzeptionelle Übersicht über Schlüssel. Anschließende werden die referentielle Integrität, Integritätsbedingungen und Auslöser durch Beispiele und Abbildungen erläutert.

Schlüssel

Ein Schlüssel ist eine Spaltengruppe, mit der Sie eine oder mehrere bestimmte Zeilen angeben oder auf diese zugreifen können.

Ein Schlüssel, der aus mehreren Spalten besteht, wird als zusammengesetzter Schlüssel bezeichnet. In einer Tabelle mit einem zusammengesetzten Schlüssel muß die Reihenfolge der Spalten im zusammengesetzten Schlüssel nicht zwangsläufig der Reihenfolge dieser Spalten in der Tabelle entsprechen.

Eindeutige Schlüssel

Ein eindeutiger Schlüssel ist als eine Spalte (oder Spaltengruppe) definiert, in der es keine identischen Werte gibt. Die Spalten eines eindeutigen Schlüssels können keine Nullwerte enthalten. Die Integritätsbedingung wird durch den Datenbankmanager während der Ausführung von Anweisungen INSERT und UPDATE durchgesetzt. Eine Tabelle kann mehrere eindeutige Schlüssel enthalten. Eindeutige Schlüssel sind wahlfrei und können in einer Anweisung CREATE TABLE oder ALTER TABLE definiert werden.

Primärschlüssel

Ein Primärschlüssel ist ein eindeutiger Schlüssel, der Bestandteil der Tabellendefinition ist. Eine Tabelle kann nur einen Primärschlüssel enthalten. Die Spalten eines Primärschlüssels können keine Nullwerte enthalten. Primärschlüssel sind wahlfrei und können in einer Anweisung CREATE TABLE oder ALTER TABLE definiert werden.

Fremdschlüssel

Ein Fremdschlüssel wird in der Definition einer referentiellen Integritätsbedingung angegeben. Eine Tabelle kann keinen oder auch mehrere Fremdschlüssel enthalten. Der Wert des zusammengesetzten Fremdschlüssels ist Null, wenn eine der Wertkomponenten Null ist. Fremdschlüssel sind wahlfrei und können in einer Anweisung CREATE TABLE oder ALTER TABLE definiert werden.

Eindeutige Integritätsbedingungen

Eine eindeutige Integritätsbedingung stellt sicher, daß die Werte eines Schlüssels innerhalb einer Tabelle eindeutig sind. Eindeutige Integritätsbedingungen sind wahlfrei. Sie können in einer Anweisungen CREATE TABLE oder ALTER TABLE durch Angabe der Klausel PRIMARY KEY oder der Klausel UNIQUE definiert werden. Beispielsweise können Sie eine eindeutige Integritätsbedingung für die Spalte mit der Personalnummer in einer Tabelle definieren, um sicherzustellen, daß jeder Mitarbeiter eine eindeutige Personalnummer hat.

Referentielle Integritätsbedingungen

Durch die Definition von eindeutigen Integritätsbedingungen und Fremdschlüsseln können Sie Beziehungen zwischen Tabellen definieren und bestimmte Geschäftsregeln konsequent durchsetzen. Die Kombination aus Integritätsbedingungen über eindeutige Schlüssel und Fremdschlüssel wird gemeinhin als referentielle Integritätsbedingung bezeichnet. Eine eindeutige Integritätsbedingung, auf die in einem Fremdschlüssel verwiesen wird, wird als übergeordneter Schlüssel bezeichnet. Ein Fremdschlüssel verweist auf einen spezifischen übergeordneten Schlüssel bzw. ist mit ihm verbunden. Beispiel: Eine Regel besagt, daß jeder Mitarbeiter (Tabelle EMPLOYEE) einer existierenden Abteilung (Tabelle DEPARTMENT) zugeordnet sein muß. Daher wird die Abteilungsnummer in der Tabelle EMPLOYEE als Fremdschlüssel und in der Tabelle DEPARTMENT als Primärschlüssel definiert. Die folgende Abbildung veranschaulicht das Konzept der referentielle Integritätsbedingung.

Abbildung 4. Integritätsbedingungen über Primär- und Fremdschlüssel definieren Beziehungen und schützen Daten

REQTEXT

Prüfungen auf Integritätsbedingung in Tabellen

Eine Prüfung auf Integritätsbedingung in Tabellen gibt Bedingungen an, die für jede Zeile einer Tabelle ausgewertet werden. Sie können Prüfungen auf Integritätsbedingung für einzelne Spalten angeben. Zum Hinzufügen von Prüfungen auf Integritätsbedingung verwenden Sie eine Anweisung CREATE oder ALTER TABLE.

Die folgende Anweisung erstellt eine Tabelle mit den folgenden Integritätsbedingungen:

 
     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),
            PRIMARY KEY (ID),
            CONSTRAINT YEARSAL CHECK
                               (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )

Ein Integritätsbedingung wird nur dann verletzt, wenn die Bedingung als falsch ausgewertet wird. Wenn beispielsweise in einer eingefügten Zeile die Spalte DEPT einen Nullwert enthält, wird die Einfügeoperation ohne Fehler fortgesetzt, obwohl die Werte für DEPT, wie in der Integritätsbedingung definiert, zwischen 10 und 100 liegen sollen.

Die folgende Anweisung fügt eine Integritätsbedingung namens COMP zur Tabelle EMPLOYEE hinzu. Diese Integritätsbedingung gibt an, daß der Gesamtverdienst eines Mitarbeiters mehr als $ 15.000 betragen muß:

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

Die in der Tabelle vorhandenen Zeilen werden dahingehend geprüft, daß die neue Integritätsbedingung nicht verletzt wird. Diese Prüfung können Sie verzögern, indem Sie die Anweisung SET CONSTRAINTS folgendermaßen einsetzen:

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

Zunächst wird mit der Anweisung SET CONSTRAINTS die Prüfung auf Integritätsbedingung für die Tabelle verzögert. Anschließend können eine oder mehrere Integritätsbedingungen zur Tabelle hinzugefügt werden, ohne daß eine Prüfung erfolgt. Anschließend wird die Anweisung SET CONSTRAINTS erneut abgesetzt, um die Prüfung der Integritätsbedingungen wieder zu aktivieren und alle verzögerten Prüfungen der Integritätsbedingungen auszuführen.

Auslöser

Ein Auslöser definiert eine Gruppe von Aktionen. Auslöser werden durch Operationen aktiviert, die die Daten in einer angegebenen Basistabelle modifizieren.

Einige Einsatzmöglichkeiten für Auslöser:

Die Verwendung von Auslösern ermöglicht eine schnellere Anwendungsentwicklung, die globale Durchsetzung von Geschäftsregeln und eine einfachere Verwaltung von Anwendungen und Daten.

DB2 Universal Database unterstützt verschiedene Arten von Auslösern. Auslöser können so definiert werden, daß sie entweder vor oder nach einer Operation DELETE, INSERT oder UPDATE aktiviert werden. Jeder Auslöser enthält eine Gruppe von SQL-Anweisungen, die als ausgelöste Aktion bezeichnet wird und eine wahlfreie Suchbedingung enthalten kann.

Nachauslöser können genauer definiert werden, um die ausgelöste Aktion entweder für jede Zeile oder einmal für die gesamte Anweisung auszuführen. Vorauslöser führen die ausgelöste Aktion immer für jede Zeile aus.

Durch die Verwendung eines Auslösers vor einer Anweisung INSERT, UPDATE oder DELETE können bestimmte Bedingungen überprüft werden, bevor eine auslösende Operation ausgeführt wird, oder die Eingabewerte geändert werden, bevor sie in der Tabelle gespeichert werden.

Mit einem Nachauslöser können Werte wie erforderlich weitergegeben oder andere Tasks ausgeführt werden, beispielsweise das Senden einer Nachricht, die als Teil der Auslöseroperation erforderlich ist.

Das folgende Beispiel veranschaulicht eine mögliche Verwendung von Vor- und Nachauslösern. Es wird von einer Anwendung ausgegangen, die Änderungen an Aktienkursen aufzeichnet und protokolliert. Die Datenbank enthält zwei Tabellen namens CURRENTQUOTE (für den aktuellen Kurs) und QUOTEHISTORY (für die Kursentwicklung), die wie folgt definiert sind:

 
     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)

Wird die Spalte QUOTE der Tabelle CURRENTQUOTE mit einer Anweisung wie beispielsweise der folgenden aktiviert:

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

sollte die Spalte STATUS der Tabelle CURRENTQUOTE aktualisiert werden und wiedergeben, ob die Aktie

Dies wird mit dem folgenden Vorauslöser erreicht:

(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 'High'                                     

(5)

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

(6)

          WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE               
             THEN 'Rising'                                      
          WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE                       
             THEN 'Dropping'                                     
          WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE                   
             THEN 'Steady'                                       
       END                                                  
 

(1)
Dieser Codeblock definiert STOCK_STATUS als einen Auslöser, der vor der Aktualisierung der Spalte QUOTE der Tabelle CURRENTQUOTE aktiviert werden soll. Die zweite Zeile gibt an, daß die ausgelöste Aktion angewendet werden soll, bevor eventuelle Änderungen, die durch die tatsächliche Aktualisierung der Tabelle CURRENTQUOTE verursacht werden, auf die Datenbank angewendet werden. Die Klausel NO CASCADE besagt, daß die ausgelöste Aktion keine weiteren Auslöser aktiviert. Die dritte Zeile gibt die Namen an, die als Qualifikationsmerkmale des Spaltennamens für die neuen Werte (NEWQUOTE) und die alten Werte (OLDQUOTE) verwendet werden sollen. Spaltennamen, denen dieser Korrelationsnamen (NEWQUOTE und OLDQUOTE) als Qualifikationsmerkmal zugeordnet wird, werden als Übergangsvariablen bezeichnet. Die vierte Zeile gibt an, daß die ausgelöste Aktion für jede Zeile ausgeführt werden soll.

(2)
Hier beginnt die erste und einzige SQL-Anweisung in der ausgelösten Aktion dieses Auslösers. Die Anweisung SET für die Übergangsvariable wird in einem Auslöser verwendet, um einer Spalte in der Zeile der Tabelle, die durch die Anweisung, die den Auslöser aktiviert hat, aktualisiert wird, einen Wert zuzuordnen. Diese Anweisung ordnet der Spalte STATUS der Tabelle CURRENTQUOTE einen Wert zu.

(3)
Der Ausdruck, der auf der rechten Seite der Zuordnung verwendet wird, ist ein Ausdruck CASE. Der Ausdruck CASE erstreckt sich bis zum Schlüsselwort END.

(4)
Der erste Teil des Ausdrucks CASE überprüft, ob der neue Wert (NEWQUOTE.QUOTE) den Maximalwert für das Börsenkürzel im laufenden Kalenderjahr überschreitet. Die Unterabfrage verwendet die Tabelle QUOTEHISTORY, die durch den folgenden Nachauslöser aktualisiert wird.

(5)
Der zweite Teil des Ausdrucks CASE überprüft, ob der neue Wert (NEWQUOTE.QUOTE) den Mindestwert für das Börsenkürzel im laufenden Kalenderjahr unterschreitet. Die Unterabfrage verwendet die Tabelle QUOTEHISTORY, die durch den folgenden Nachauslöser aktualisiert wird.

(6)
Die letzten drei Teile des Ausdrucks CASE vergleichen den neuen Wert (NEWQUOTE.QUOTE) mit dem Wert, der sich in der Tabelle (OLDQUOTE.QUOTE) befunden hat, und stellen fest, ob er größer, kleiner oder identisch ist. Hier endet die Anweisung SET für die Übergangsvariable.

Zusätzlich zur Aktualisierung des Eintrags in der Tabelle CURRENTQUOTE muß ein Protokolleintrag erstellt werden, indem der neue Wert zusammen mit einer Zeitmarke in die Tabelle QUOTEHISTORY kopiert wird. Dies wird mit dem folgenden Nachauslöser erreicht:

(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)
Dieser Codeblock definiert einen Auslöser namens RECORD_HISTORY als Auslöser, der nach der Aktualisierung der Spalte QUOTE der Tabelle CURRENTQUOTE aktiviert werden sollte. Die dritte Zeile gibt den Namen an, der als Qualifikationsmerkmal des Spaltennamens für den neuen Wert (NEWQUOTE) verwendet werden soll. Die vierte Zeile gibt an, daß die ausgelöste Aktion für jede Zeile ausgeführt werden soll.

(2)
Die ausgelöste Aktion dieses Auslösers enthält eine einzige SQL-Anweisung, die eine Zeile in die Tabelle QUOTEHISTORY einfügt. Zu diesem Zweck werden die Daten aus der Zeile, die aktualisiert wurde (NEWQUOTE.SYMBOL und NEWQUOTE.QUOTE), sowie die aktuelle Zeitmarke verwendet.

CURRENT TIMESTAMP ist ein Sonderregister, das die Zeitmarke enthält. Eine Liste und Erläuterung finden Sie unter Sonderregister.


[ Seitenanfang | Vorherige Seite | Nächste Seite | Inhaltsverzeichnis | Index ]