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.
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.
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.
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.
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.
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.
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.
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.
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
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
CURRENT TIMESTAMP ist ein Sonderregister, das die Zeitmarke enthält. Eine Liste und Erläuterung finden Sie unter Sonderregister.