RI und Trigger für Fortgeschrittene

Einleitung

Seit Einführung des Database-Containers mit Visual FoxPro benutzen Sie persistente Beziehungen. Aber erstellen und pflegen Sie nicht auch noch viel zu viel Code in Ihren Programmen, der von der Datenbank selbst erledigt werden könnte? Wie kann man die Datenbankleistung besser ausnutzen? Fortgeschrittene RI-Konzepte, sowie der Einsatz von Triggern sind nicht nur eine der Antworten auf diese Fragen, sondern auch eine der Voraussetzung für die Entwicklung von Three-Tier-Applications.

Referentielle Integrität (Definitionen)

Die Datenmodellierung hat zum Ziel, Strukturen der realen Welt in einer aussagekräftigen, robusten, leicht wartbaren und gut auswertbaren Datenbasis abzubilden. Um dies zu erreichen, werden im Laufe des Modellierungsprozesses unter anderem durch einen Zerlegungsprozess (=Normalisierung) Redundanzen entfernt. Die Normalisierung selbst sorgt aber noch nicht dafür, daß die abgebildeten Sachverhalte tatsächlich realitätskonform sind und auch bleiben. Die durch den Modellierungsprozess entstandenen m.o.w. normalisierten Tabellen (Theoretiker mögen mir an dieser Stelle die Ausdrücke "Tabelle" und "Feld" verzeihen) stehen vielmehr in Beziehungen zueinander, die nicht verletzt werden dürfen. Die Richtigkeit (=Integrität) der Beziehungen nennt man Beziehungsintegrität oder auch Referentielle Integrität. Referentielle Integrität definiert also, welche Operationen zwischen Tabellen erlaubt sind, die in Beziehung zueinander stehen.

Die in einer Datenbank definierten Beziehungen zwischen Tabellen werden auch persistente Beziehungen genannt. Sie sollten nicht verwechselt werden mit dem xBase Befehl SET RELATION, der die Beziehung zwischen Datensatzzeigern von Tabellen innerhalb eines Programms steuert.

Persistente Beziehungen werden erst durch das Vorhandensein von Primärschlüsseln und Fremdschlüsseln ermöglicht.

Primärschlüssel sind Felder, mit deren Wert ein Satz einer Tabelle eindeutig identifiziert werden kann. Unter Umständen läßt sich diese eindeutige Identifizierung nur mit einem zusammengesetzten Schlüssel, bestehend aus einer minimalen Kombination von Feldern, erzeugen.

Ein nicht als Primärschlüssel qualifizierendes Feld einer Tabelle ist dann ein Fremdschlüssel, wenn es in einer anderen Tabelle Primärschlüssel ist. Auch hier sind zusammengesetzte Schlüssel möglich.

In Visual Foxpro ist die Primärschlüsseleigenschaft eines Feldes, bzw. einer Zusammensetzung von Feldern dadurch gekennzeichnet, daß man einen Primärindex für dieses Feld, bzw. die Zusammensetzung von Feldern anlegt. Für einen Fremdschlüssel muß ebenfalls ein Index existieren. Persistente Beziehungen werden in Visual Foxpro zum Beispiel dadurch erzeugt, daß man im Datenbankdesigner den mit einem Schlüsselchen gekennzeichneten Primärschlüsselindex einer Tabelle auf den Index des Fremdschlüsselfeldes zieht.

Hier nochmal als Erinnerung für hard-headed Fox-Profis: Primärschlüssel/Fremdschlüssel sind Feldeigenschaften, der Index selbst ist nur Mittel zum Zweck! Und Beziehungen sind bei der Datenmodellierung zunächst unabhängig vom Index, auch wenn dieser in VFP zum visuellen Erstellen der Beziehung benötigt wird.

Doch nun zurück zum Thema, der Integrität von Beziehungen. Zur Wahrung der Referentiellen Integrität gibt es verschiedene Regeln, die beachtet werden müssen, bzw. Aktionen nach sich ziehen, wenn Daten einer Tabelle geändert werden, die eine Beziehung zu einer anderen Tabelle betreffen. Mögliche Regeln sind:

Regel	Aktion
CASCADE	Weitergabe von Löschungen/Modifikationen
RESTRICT	Verhindern von Löschungen/Modifikationen
NULLIFY	Nullsetzen abhängiger Daten
Ignore	Nichtbeachten der RI (VFP-eigene Regel)
Set To Default	Auf Default setzen abhängiger Daten

Nicht alle dieser Regeln sind Standard oder im VFP RI-Designer einstellbar.

Trigger und Stored Procedures (Definitionen)

Trigger und Stored Procedures sind Prozeduren, die in der Datenbank gespeichert werden. Der Unterschied zwischen ihnen besteht grundsätzlich darin, daß Trigger durch ein bestimmtes Datenbankereignis ausgelöst werden, während Stored Procedures explizit aufgerufen werden müssen. Trigger benutzen häufig Stored Procedures, um Prüfungen durchzuführen oder Updates auszuführen. Trigger werden standardmäßig vor Insert-/Update-/Delete-Anforderungen angestoßen. Einige Datenbanksysteme können auch Trigger nach Insert-/Update-/Delete-Aktionen ausführen.

Vergleichen wir doch mal die Datenbank (bzw. den VFP-Datenbankcontainer) mit einer Container-Klasse. Dann entsprechen die Datenbank-Trigger den Events, die für den Container ausgelöst werden. Die Stored Procedures entsprechen den Methoden, die für den Container aufgerufen werden. Die Tabellen einer Datenbank entsprechen den Objekten, die sich im Container befinden.

Referentielle Integrität (Implementation)

Trigger-RI und/oder Deklarative RI

Um die RI wahren zu können, müssen Schutzmechanismen vorhanden sein, die Änderungen des Datenbankinhalts bei Verletzung der RI abweisen. Visual Foxpro realisiert diese Schutzmechanismen ausschließlich über Trigger. Das heißt, daß bei jedem Datenbankupdate, für das ein Trigger vorhanden ist, der entsprechende Triggercode aufgerufen wird.

Auch in Client/Server Datenbanken kann man sämtliche RI über Trigger abbilden. Allerdings verfügen diese Datenbanken meistens über einen datenbankeigenen Schutzmechanismus, der RI-Verletzungen abfängt. Diesen Mechanismus nennt man Deklarative RI (declarative integrity constraint). Deklarative RI hat den Vorteil, daß sie durch datenbankeigene Optimierungsmechanismen schneller ist und nicht gewartet werden muß. Allerdings werden durch sie natürlich nur Standardfehlermeldungen erzeugt, die im Zweifelsfalle vom Anwendungsprogramm nachbearbeitet werden müssen, um für den Benutzer aussagekräftig sein zu können.

Je nach Datenbanksystem kann die Deklarative RI mehr oder weniger selbst Regeln zur Wahrung der Integrität ausführen (z.B. CASCADE DELETE). Informieren Sie sich bei der Planung von Client/Server Datenbanken über deren Möglichkeiten, um sie optimal nutzen zu können.

An Tabellen, für die ein Trigger gefeuert wurde, können bei Ausführung des Triggers keinerlei Änderungen vorgenommen werden!

Funktionsweise

Nehmen wir an, wir wollen einen Satz aus einer Tabelle löschen, während in einer abhängigen Tabelle noch ein Satz vorhanden ist, dessen Fremdschlüssel dem zu löschenden Primärschlüssel entspricht. Um die Integrität der Daten zu wahren, stellen wir hier zwei Möglichkeiten vor: entweder die Löschung wird zurückgewiesen, oder der Fremdschlüssel des Datensatzes in der abhängigen Tabelle wird auf einen Standardwert gesetzt. Ersteres entspricht der RI-Regel "Restrict", letzeres der RI-Regel "Set To Default".

Restrict Delete (Trigger-RI)

Restrict Delete (Deklarative RI)

Delete Set To Default (Trigger-RI)

Delete Set To Default (Trigger/Deklarative RI)

Beziehungen erstellen

Persistente Beziehungen werden in Visual Foxpro entweder wie eingangs beschrieben visuell erstellt, oder indem man den Fremdschlüssel in der abhängigen Tabelle deklariert. In VFP hat dies tatsächlich nur die Erstellung der Beziehung zur Folge, was für die RI noch gar nichts bedeutet, weil RI hier eben ausschließlich über Trigger realisiert wird. Man könnte auch (beschönigend) sagen, daß VFP standardmäßig IGNORE als Insert-, Update- und Delete-Regeln einsetzt.

Beispiel:

Trigger erstellen

In Visual Foxpro können Sie Trigger durch den RI-Assistenten generieren lassen. Zum Ändern von Triggern oder Hinzufügen von Code öffnen Sie die Datenbankprozeduren mit MODIFY PROCEDURE. Sie können über die Bearbeitung von Datenbankprozeduren auch neue Trigger erstellen, müssen diese dann aber im Tabellendesigner mit den gewünschten Aktionen verbinden.

Natürlich können Sie auch Trigger wie bei Client/Server Datenbanken über den CREATE TRIGGER Befehl erstellen.

Referential Integrity Constraints erstellen

Bei Client/Server Datenbanken erzeugt die Deklaration eines Fremdschlüssels automatisch die entsprechende Deklarative RI, weil dort standardmäßig RESTRICT als Regel eingesetzt wird. Hier ein Beispiel zur Erstellung Deklarativer RI unter Oracle.

Stored Procedures erstellen

In VFP können Sie Stored Procedures erstellen, indem Sie im Datenbankdesigner über das Kontexmenü Gespeicherte Prozeduren bearbeiten auswählen oder im Befehlsfenster MODIFY PROCEDURE eingeben.

Trigger planen

Beachten Sie folgendes bei der Planung von Triggern:

• Benutzen Sie Trigger, wenn eine Datenbankoperation weitere Operationen nach sich ziehen soll. Zum Beispiel wenn beim Anlegen einer Bestellung gleich der erste Posten-Datensatz mit angelegt werden soll.
• Benutzen Sie Trigger nur dann, wenn Sie sicher sind, daß der Code immer ausgeführt werden soll, unabhängig davon, ob die Datenbankoperation programmatisch oder über Browse-Fenster ausgelöst wurde, und unabhängig davon, welcher Benutzer sie angestoßen hat.
• Benutzen Sie Trigger nicht, um RI-Verletzungen abzufangen, die durch Deklarative RI abgefangen werden können (gilt nicht für VFP!)
• Halten Sie die Trigger möglichst kurz. Rufen Sie lieber vom Trigger aus Stored Procedures auf, anstatt hunderte von Zeilen Code in einen Trigger zu schreiben.
• Achten Sie darauf, niemals rekursive Trigger zu schreiben!

VFP-Beispiele

Voraussetzungen

Die folgenden Beispiele basieren auf einer stark vereinfachten Beispiel-VFP Datenbank, die aus vier Tabellen besteht: kunde, bestell, posten und protokol. Die ersten drei sind durch persistente Beziehungen miteinander verbunden und unterliegen Referentieller Integrität. Die Tabelle protokol dient zum Protokollieren von Änderungen.

Wie zu vermuten war, sind in der Tabelle kunde Kundendaten gespeichert. Sie verfügt über den Primärschlüssel idkunde. Die Tabelle bestell soll Bestellungen unserer Kunden speichern. Sie verfügt über den Primärschlüssel idbestell und den Fremdschlüssel idkunde. Damit ist sie abhängig von der Tabelle kunde. Die Tabelle posten soll die Bestellposten speichern. Sie verfügt über den Primärschlüssel idposten und den Fremdschlüssel idbestell. Somit ist sie abhängig von der Tabelle bestell.

Beim Löschen und Aktualisieren von Kundendaten sollen die Änderungen an die Bestelltabelle weitergegeben werden (DELETE CASCADE und UPDATE CASCADE). (Wem DELETE CASCADE in diesem Beispiel nicht besonders sinnvoll scheint: Sie werden später sehen, warum wir diese Regel hier verwenden!). Das Löschen von Bestelldaten ist untersagt, wenn zu der Bestellung bereits Posten existieren (DELETE RESTRICT). Änderungen sollen jedoch weitergegeben werden (UPDATE CASCADE). Das Einfügen von Bestelldaten, bzw. Postendaten wird untersagt, wenn der entsprechende Fremdschlüssel nicht in der Parent-Tabelle vorhanden ist (INSERT RESTRICT).

Alle Primärschlüssel sind vom Typ INTEGER. Dies ist nicht nur die schnellste Möglichkeit, sie erleichtert auch in diesem Beispiel den Eintrag in die Protokolltabelle.

Löschen von Kundendaten

Der Delete Trigger für die Tabelle kunde

Beim Löschen in der Tabelle kunde wird zunächst der dazugehörige Trigger aufgerufen

Wenn _triggerlevel=1 ist, wurde diese Funktion direkt durch eine DELETE-Anforderung aufgerufen und nicht etwa durch andere Trigger. In diesem Fall werden zunächst einige Initialisierungsmaßnahmen durchgeführt.

IF _triggerlevel=1

Sämtliche Änderungen werden in die Transaktion eingeschlossen. Im Fehlerfall wird ein ROLLBACK aufzurufen, um die Änderungen rückgängig zu machen

Das Fehlerflag pnerror wird als PRIVATE-Variable von den aufgerufenen Funktionen weiter benutzt.

Fehler automatisch in pnerror speichern lassen

Die Stored Procedure riopen

riopen wird von Triggern zum Öffnen von Tabellen benutzt

Die Variable pcRIcursors enthält die geöffneten Tabellen in folgender Struktur: Tabellenname, In-Use-Flag, Arbeitsbereich. Die Tabelle wird nur dann geöffnet, wenn sie nicht offen, bzw. offen aber in Bearbeitung ist.

Die Stored Procedure riopen

riopen wird zum Zurücksetzen des In-Use-Flags benutzt

Die Stored Procedure riend

riend wird von Triggern zum Beenden der Verarbeitung benutzt

Änderungen im Erfolgsfall wegschreiben

Änderungen im Fehlerfall zurücknehmen

Die geöffneten Tabellen wieder schließen

Die Stored Procedure ridelete

ridelete wird von Triggern zum Löschen von Child-Daten benutzt

Achtung: Warum diese Zeile ausgesternt ist, unbedingt unten nachlesen

Die Zeile von oben wurde manuell hierher verschoben

Die vom RI-Assistenten generierte Prozedur RIDELETE hat einen kleinen, aber unter Umständen verheerenden Haken. Wenn - wie in unserem Beispielfall - der DELETE einen weiteren Trigger auslöst, der unter Umständen fehlschlagen kann (Beachte: Bestellungen werden nur gelöscht, wenn keine Bestellpositionen mehr vorhanden sind), dann muß der Abbruch hier erkannt werden. Da aber die Routine riopen die Tabellen im ungepufferten Modus öffnet, wird kein nächster Level des Triggers vor der Anweisung unlock ausgeführt. Wenn der unlock also erst nach dem Setzen des Returnwertes ausgeführt wird, bekommen wir nichts von einem eventuellen Fehler mit. Die Lösung liegt darin, den Returnwert erst nach dem unlock zu füllen.

Ansonsten können unter unseren beschriebenen Bedingungen Zombie-Datensätze entstehen, wie hier zu sehen ist. Der Parent kunde und die erste Bestellung wurden erfolgreich gelöscht, bevor der DELETE-Trigger der zweiten Bestellung weiteres verhindern konnte.

Automatisches Protokollieren von Änderungen

Nun soll die Protokoll-Tabelle aus unserem Beispiel bei jeder Änderung von Bestelldaten gefüllt werden. Man kann dies jedesmal von Hand im Programm (bzw. in der entsprechenden Klasse) erledigen. Aber was ist, wenn Benutzer auch ohne Anwendungsprogramm, z.B. über das Browse-Fenster, Zugriff auf die Daten haben. Oder was passiert, wenn man das Schreiben der Protokolltabelle irgendwo im Programmcode vergißt?

In unserem Beispiel werden Timestamp, Benutzerid, Tabellenname, ausgeführte Aktion und der betroffene Primärschlüssel über einen Trigger in die Protokolltabelle geschrieben. Wir demonstrieren dies an dem Update-Trigger der Tabelle bestell, aber man könnte so alle wichtigen Tabellen der Datenbank verwalten und sogar Löschungen von Daten problemlos nachvollziehen.

. . . wir sparen uns hier die Erklärung des gesamten Triggers . . .

Durchführen der Änderungen

. . .

Einfügen des Protokollsatzes

Über den Tellerrand hinaus...

Zum Abschluß betrachten wir noch einen Trigger, der aus einer Oracle-Datenbank stammt. Dieser Trigger feuert bei Einträgen in die Mitarbeitertabelle mitarbeiter. Er prüft in der Gehaltsstufen-Tabelle stufe, ob sich das angegebene Gehalt gehalt innerhalb des Rahmens befindet, der für die Qualifikation quali üblich ist.

Vielleicht zahlt es sich ja mal für Sie aus, wenn Sie solche Mechanismen kennen...

Das minimale und maximale Gehalt für die entsprechende Qualifikation aus der Tabelle stufe holen

Wenn das eingegebene Gehalt größer oder kleiner als das Limit ist, wird eine Exception angestoßen. Damit wird eine Fehlermeldungzurückgegeben und das INSERT, bzw. UPDATE, das den Trigger ausgelösthat, wieder zurückgedreht.

From Here...

An dieser Stelle vielen Dank an Doug Hennig für die Fehlerbereinigung in der RIDELETE-Prozedur und Jürgen Wondzinski dafür, daß er diese auf der DEVCON97 vorgestellt hat.

Die im Text angesproche Beispieldatenbank finden Sie auf der Begleit-CD zur DEVCON.

Für Fragen stehe ich Ihnen jederzeit unter Mengel@indisoft-gmbh.de zur Verfügung.

Sie erreichen mich auch auf unserer Website www.indisoft-gmbh.de.