Document

Entwicklung eines Triggersubsystems auf dem
Datenbank-Managementsystem Oracle7
Marcus Winkler
Studienarbeit am Institut für Informatik der Universität Hannover
Sept. 1994
Inhaltsverzeichnis
1 Einleitung
2
2 Integritätsüberwachung in Oracle7
2.1 Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2 Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5
5
6
3 Erweiterung des Oracle7-Triggerkonzepts
3.1 Syntax verzögerter Trigger . . . . . . . . .
3.2 Ausführungssemantik . . . . . . . . . . . .
3.3 Umsetzung auf Oracle7 . . . . . . . . . . .
3.3.1 Die ∆-Tabellen und der Hilfstrigger
3.3.2 Speicherung verzögerter Trigger . .
3.3.3 Prozedur my commit . . . . . . . .
3.3.4 Einschränkungen . . . . . . . . . .
4 Programmbeschreibung
4.1 main.h . . . . . . . . .
4.2 main and oracle.pc .
4.3 head and body.c . . .
4.4 my commit.c . . . . . .
4.5 errors.c . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
14
14
18
23
24
28
29
32
.
.
.
.
.
33
35
36
38
39
40
5 Benutzerhandbuch
42
5.1 Triggererstellung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
5.2 Programmbedienung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
6 Erweiterungsmöglichkeiten
48
Literaturverzeichnis
49
1
Kapitel 1
Einleitung
Die Überwachung von Integritätsbedingungen in aktiven relationalen Datenbanksystemen
findet immer größeren Einsatz. Durch sie soll die logische Korrektheit von Datenbankinhalten gewährleistet werden. Dazu werden beim Datenbankentwurf Integritätsbedingungen aufgestellt, die, bezogen auf einzelne Relationen, die hinsichtlich der zu modellierenden
Anwendung die technisch möglichen Datenbankzustände und -operationen (Einfügen /
Löschen / Verändern) einschränken.
Im Prinzip müßte ein Datenbanksystem alle Verletzungen von Integritätsbedingungen abfangen, sei es als Plausibilitätsprüfung gegen Eingabefehler oder als Kontrollinstrument
für die Anwendungswelt. Die Überprüfung der Integritätsbedingungen zur Laufzeit kann
durch einen universellen Integritätsmonitor vorgenommen werden. Dabei ist eine Überprüfung nach jeder Operation oder auch erst nach einer Folge von Operationen (Transaktion) denkbar. Als Reaktion des Monitors auf eine negativ ausgefallene Prüfung erfolgt
das Rückgängigmachen der Operation bzw. Transaktion.
Die von DBMSen heutzutage für die Integritätsüberwachung zur Verfügung gestellten
Mittel sind Trigger. Trigger sind Prozeduren, die in einer Erweiterung der Datenbankanfragesprache (in der Regel SQL) geschrieben und in compilierter Form im Data Dictionary abgespeichert werden. Sie bestehen aus den folgenden drei Bausteinen:
• Angabe der kritischen Datenbankoperation
• Vorbedingung (allgemeines SQL-Prädikat)
• Reaktionen (z.B. Fehlermeldung oder -korrektur)
Die Ausführung der Trigger geschieht automatisch, so daß die Verantwortung dafür beim
DBMS und nicht beim Benutzer liegt, und ist abhängig vom gewählten Triggertyp.
Möglich ist die Ausführung
• vor oder nach einer Änderung eines Datensatzes (Immediate Row / DatensatzTrigger), d.h. Tupelbezogen
2
KAPITEL 1. EINLEITUNG
3
• vor oder nach einer Operation (Immediate / Befehls-Trigger), d.h. nach einer Menge
von Tupeln
• nach einer aus mehreren Operatioen bestehenden Transaktion (Deferred / verzögerter Trigger)
In den heute angebotenen kommerziellen DBMSen werden nur Befehls-, manchmal auch
Datensatz-Trigger zur Verfügung gestellt. Es existiert zur Zeit kein kommerzielles relationales DBMS, das das Konzept der verzögerten Trigger implementiert hat. Unter den nichtkommerziellen Systemen ist Starburst bekannt [Widom, 1992], [Widom, 1993]. Im Bereich
der objekt-orientierten Datenbanken werden verzögerte Trigger bereits unterstützt. Ein
Beispiel dafür ist Ode [Gehani and Jagadish, 1991].
Dabei haben diese Trigger eine große Bedeutung in der Anwendung. Bei referentiellen Abhängigkeiten zwischen Relationen kommt man ohne sie nur mit großen Einschränkungen
in der Integritätsüberwachung oder im Anwendungskomfort aus.
Bei der Implementation verzögerter Trigger stellen sich einige Fragen:
• Was passiert, wenn mehrere Trigger in einem DB-Zustand gleichzeitig gefeuert werden müßten, weil deren Vorbedingungen gleichzeitig erfüllt werden ?
• Werden alle ausgeführt, und wenn ja, in welcher Reihenfolge und auf welchen Daten
jeweils ?
• Kann die Ausführung eines Triggers weitere auslösen, und kann dabei ein Zyklus
erkannt werden bzw. sind Zyklen bereits bei der Spezifikation der Trigger erkennbar ?
• Wenn in einem DB-Zustand mehrere Trigger gefeuert werden müßten und noch
andere Trigger auf ihre Feuerung warten, wie ist da die Reihenfolge der Abarbeitung
geregelt ?
• Kann der zuerst ausgeführte Trigger die Vorbedingungen anderer Trigger, deren
Feuerung zurückgestellt wurde, wieder ungültig machen ?
Gegenstand der Studienarbeit ist es nun, auf das Oracle7 DBMS ein Triggersubsystem
aufzusetzen, das die Verwendung verzögerter Trigger ermöglicht. Dabei sollen mehrere
Trigger unabhängig voneinander auf einer Tabelle arbeiten können und die Abarbeitungsreihenfolge durch Prioritäten bestimmt sein. Die während der Transaktion neu eingefügten
und die gelöschten Tupel sollen ebenso referenzierbar sein wie die veränderten Tupel vor
und nach einem Update. Das Transaktionsende soll als Auslöser der Triggerabarbeitung
vom Anwender durch den Befehl my commit gekennzeichnet werden.
KAPITEL 1. EINLEITUNG
4
Oracle7 besitzt eine prozedurale Erweiterung von SQL namens PL/SQL, mit der es
möglich ist, Prozeduren und Trigger zu erstellen (vgl. [O7-PL/SQL]). Prozeduren sind
PL/SQL-Blöcke, die, wie in Programmiersprachen üblich, mit ihrem Namen aufgerufen
werden. Mit Hilfe von Datensatz-Triggern und Prozeduren können nun verzögerte Trigger
realisiert werden. Dabei wird allerdings keine so hohe Performance erreicht wie bei einem
eingebauten Triggersystem mit entsprechender Funktionalität. Oracle7 ist jedoch nicht
auf einer dem DBMS näheren Ebene zu programmieren. Es bleibt also nur die Möglichkeit des Aufsatzes.
Weitere Arbeiten, die sich mit verzögerten Triggern
[Gertz and Lipeck, 1993] sowie [Ceri and Widom, 1990].
beschäftigen,
sind
In den folgenden Kapiteln werden das Triggerkonzept von Oracle7 sowie die Umsetzung
verzögerter Trigger mit den Möglichkeiten dieses DBMS beschrieben. Es folgen die Beschreibung des Programmablaufs und der Modularisierung. Das Benutzerhandbuch erklärt
die Vorgehensweise beim Erstellen der Trigger. Den Abschluß bildet ein Ausblick auf Erweiterungsmöglichkeiten des Programms.
Kapitel 2
Integritätsüberwachung in Oracle7
In Oracle7 gibt es prinzipiell zwei Möglichkeiten, Integritätsbedingungen zu spezifizieren:
mit Constraints oder mit Triggern.
2.1
Constraints
Constraints werden bei der Tabellendefinition (oder -änderung) angegeben. So kann
man z.B. durch Angabe von not null erreichen, daß in einer Spalte keine Nullwerte auftreten dürfen. Mit der Angabe unique wird es unmöglich, daß in einer Spalte ein Wert mehrfach auftritt. Mit primary key und references bzw. foreign key
references werden Primär- und Fremdschlüsselbedingungen ausgedrückt. Folgt auf eine
Primärschlüsselbedingung noch die on delete cascade-Klausel, so werden beim Löschen
des Primärschlüssels auch gleich alle zugehörigen Fremdschlüssel gelöscht. Mit Hilfe der
check-Bedingung können logische Ausdrücke formuliert werden, die überprüft werden sollen. Jede dieser Überprüfungen findet unmittelbar nach der auslösenden DB-Operation
statt.
Ein Beispiel für die Verwendung solcher Constraints ist die folgende Definition einer Angestelltenrelation, die die Nummer, den Namen, und das Gehalt eines Angestellten verwaltet:
create table EMP values(empno number(5) primary key,
ename varchar2(15) not null,
sal number(7,2),
check (sal > 0));
Dabei ist die Angestelltennummer Primärschlüssel der Tabelle. Der Namenseintrag darf
nie fehlen, und das Gehalt muß immer positiv sein. In der folgenden Abteilungsrelation soll die Angestelltennummer referenziert werden. Sie besteht aus den Angaben von
Nummer, Name und Abteilungsleiter. Der Abteilungsleiter muß in der Angestelltentabelle eingetragen sein.
5
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
6
create table DEPT values(deptno number(4),
dname varchar2(15) not null,
mgr number(5) not null
references EMP on delete cascade);
Einfache Integritätsbedingungen und vor allem referentielle Abhängigkeiten können also
mit Constraints ausgedrückt werden. Sollen kompliziertere Abhängigkeiten geprüft oder
DB-Operationen als Reaktion auf ein auslösendes Ereignis durchgeführt werden, die über
ein einfaches Löschen (on delete cascade) oder ein Zurücksetzen in den DB-Zustand
vor dem auslösenden Ereignis hinausgehen, so werden Trigger notwendig.
2.2
Trigger
Ein Trigger wird in Oracle7 immer auf genau einer Tabelle definiert. Auf einer Tabelle
wiederum können maximal 12 verschiedene Trigger definiert werden. Dabei unterscheidet man das Auslöseereignis, also die auslösende Datenbankoperation, den Zeitpunkt der
Feuerung (before / after) und den Triggertyp (Befehls-/ Datensatz-Typ). Die folgende
Tabelle stellt die verschiedenen Definitionsmöglichkeiten dar:
Zeitpunkt
Ereignis
Triggertyp
before
after
Befehl
Datensatz
insert
?
?
?
?
delete
?
?
?
?
update
?
?
?
?
Dabei kann man auch verschiedene Auslöseereignisse kombinieren, z.B. kann man einen
after insert or update-Trigger konstruieren, was jedoch die Anzahl der Trigger einschränkt. Ein weiterer after update-Trigger wäre hier nicht mehr möglich. Will man
mehrere Integritätsbedingungen umsetzen, die nach demselben Ereignis überprüft werden
sollen, so müssen alle im selben Trigger untergebracht werden. Dabei ist die Abarbeitungsreihenfolge zu beachten.
Ein Befehls-Trigger wird immer genau einmal ausgeführt, unabhängig davon, wieviele Datensätze durch die auslösende Operation verändert wurden. Ein Datensatz-Trigger hingegen wird entsprechend der Anzahl der veränderten Datensätze gefeuert. D.h. werden drei
Datensätze verändert, wird der Datensatz-Trigger dreimal ausgeführt, der Befehls-Trigger
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
7
jedoch nur einmal.
Die verschiedenen Trigger werden in der folgenden Reihenfolge abgearbeitet:
1. Before-Befehls-Typ: einmal vor der Ausführung der auslösenden Operation
2. Before-Datensatz-Typ: n-mal vorher, evtl. gar nicht bei n=0
3. After-Datensatz-Typ: n-mal nachher
4. After-Befehls-Typ: einmal nachher
Bei den Datensatztriggern steht n für die Anzahl der Tupel, die von der auslösenden DBOperation verändert bzw. eingefügt oder gelöscht wurden.
Bei Datensatz-Triggern ist es möglich, auf die alten und neuen Tabelleneinträge zuzugreifen. Die Präfixe :new. bzw. :old. vor dem entsprechenden Spaltennamen erlauben den
Zugriff. Die folgende Tabelle zeigt, welche Werte abhängig von der DB-Operation jeweils
referenziert werden können:
Ereignis
Adressierung der Datensatz-Instanzen
insert
old
new
null
neue Werte
update
old
new
alte Werte
neue Werte
delete
old
new
alte Werte
null
Soll beispielsweise aus einer Angestelltenrelation EMP die Anzahl der Angestellten bestimmt werden, die eine Gehaltserhöhung erhalten haben, so sähe dies wie folgt aus:
select count(*) into anzahl from EMP
where :new.sal > :old.sal;
Zur Definition eines Triggers dient der create trigger-Befehl. Definiert werden dabei:
• der Triggername
• der Triggerzeitpunkt (before / after)
• die auslösenden DB-Operationen (insert / delete / update)
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
8
• der Tabellenname
• der Triggertyp (Befehls-/ Datensatz-Typ)
• die Triggerrestriktion
• und der Triggerrumpf, in dem sich die eigentliche Prozedur befindet.
Bei einem Update-Trigger kann zusätzlich angegeben werden, welche Spalten eine UpdateOperation verändern muß, um die Feuerung des Triggers auszulösen. Innerhalb der
Triggerrestriktion kann man eine zusätzliche Bedingung angeben, die vor der Triggerausführung überprüft werden muß. Nur wenn die Bedingung zutrifft, wird der Triggerrumpf ausgeführt, sonst wird die Trigger-Ausführung schon an dieser Stelle positiv
beendet.
In PL/SQL-Syntax sieht der Triggeraufbau wie folgt aus (vgl. [O7-AppDev]):
create or replace trigger <trig name> Triggername
before / after
Triggerzeitpunkt
insert or
update of <Spalte1>, <Spalte2>, . . . or
delete
DB-Operation
on <table name>
Tabellenname
(for each row)
Triggertyp
when <Bedingung>
Triggerrestriktion
declare
<Variablen- und
Cursor-Deklarationen>
Triggerrumpf
<PL/SQL-Programm-Code>
Innerhalb des Triggerrumpfs steht ein PL/SQL-Block (siehe dazu [O7-PL/SQL]), der
das eigentliche Triggerprogramm enthält. Ist ein Datensatz-Trigger für mehrere DBOperationen definiert, so kann bei diesem mit den Konstrukten
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
if
if
if
if
9
inserting then <SQL-Block>
updating then <SQL-Block>
updating (’<Spalte>’) then <SQL-Block>
deleting then <SQL-Block>
eine Programmverzweigung in Abhängigkeit von den entsprechenden Ereignissen bewirkt
werden.
Zum PL/SQL-Befehlssatz gehören auch die insert-, delete- und update-Operationen. Da
auf diese wieder Trigger gefeuert werden können (Kaskade), muß man bei den Triggerdefinitionen darauf achten, daß kein Zyklus entsteht, also z.B. keine Triggerausführung
immer wieder die eigene Feuerung nach sich zieht. Das Oracle7 DBMS ist nicht in der
Lage, zyklische Trigger zu erkennen, da der Zyklus im PL/SQL-Block codiert ist.
Mittels des Prozeduraufrufs raise application error kann innerhalb des PL/SQLBlocks eine Oracle7-Fehlermeldung ausgegeben werden; dabei wird außerdem der Trigger
negativ beendet.
Was bedeutet es nun eigentlich, wenn ein Trigger positiv oder negativ beendet wird ? Da
erst die gefeuerten Trigger eine DB-Operation abschließen, führt ein negatives Triggerende dazu, daß alle DB-Operationen, die während der Abarbeitung des letzten Triggers
durchgeführt wurden, rückgängig gemacht werden. Eine positive Triggerausführung läßt
die geänderten DB-Inhalte bestehen.
Mit dem Kommando alter trigger trig name ist es möglich, Trigger vorübergehend
auszuschalten, so daß während dieser Zeit die dem Trigger zugrunde liegenden Integritätsbedingungen verletzt werden können. Das vollständige Löschen eines Triggers geschieht
mit dem Befehl drop trigger trig name.
Das Oracle7 DBMS speichert Trigger zunächst nicht in compilierter Form, sondern übersetzt den PL/SQL-Code erst bei der ersten Feuerung. Danach liegt der Trigger dann in
compilierter Form vor, so daß die folgenden Triggerausführungen erheblich schneller werden.
Eine wichtige Eigenschaft von Triggern ist es auch, daß im Triggerrumpf keinerlei Einschränkungen gegenüber den PL/SQL-Prozeduren gemacht werden. Es muß also nicht
unbedingt eine die logische Korrektheit erhaltende Reaktion auf das auslösende Ereignis
im Rumpf stehen wie etwa die Ausgabe einer Fehlermeldung oder ein Rollback. Genausogut können dort Algorithmen untergebracht werden, die gewisse Teile der Relation nach
einer bestimmten DB-Operation kopieren sollen.
Auf der anderen Seite kann man nicht jede Integritätsbedingung in Constraints oder
in Trigger übersetzen. Um Beispiele dafür geben zu können, wird die folgende Ange-
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
10
stellten-Relation benutzt, die die Grundlage aller weiteren Beispiele sein wird. Zugleich
ist sie Teil der Oracle7 Demonstrationsrelation. Sie besteht aus den Tabellen EMP und
DEPT. EMP enthält die Daten der Angestellten: Nummer, Name, Beruf, Vorgesetzter,
Einstellungsdatum, Gehalt und Abteilungsnummer. DEPT beinhaltet Angaben über die
Abteilungen: Nummer, Name, Ort und Abteilungsleiter.
EMP
DEPT
empno ename job mgr hiredate
deptno dname loc
sal deptno
mgr
Es gelte die folgende Integritätsbedingung:
Abteilungsleiter beziehen das höchste Gehalt in ihrer Abteilung.
Hier wäre ein Vergleich des Gehalts des Abteilungsleiters mit den Gehältern seiner Mitarbeiter nötig. Da man bei Constraints keine select-Operationen verwenden kann, müßte
man Trigger einsetzen. Doch ein Immediate-Trigger kann auf einer sich ändernden Tabelle
keine Aggregation durchführen. In diesem Falle wäre dies das Bestimmen des maximalen
Gehalts der Mitarbeiter eines Abteilungsleiters. Der Einsatz eines verzögerten Triggers
wird notwendig.
Nun sollen die folgenden zwei Integritätsbedingungen umgesetzt werden:
1. Zu jeder Abteilung existiert ein Abteilungsleiter.
2. Jeder Angestellte arbeitet in genau einer Abteilung.
Zunächst folgt die Umsetzung mit Constraints:
create table EMP
(empno number(4) primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
deptno number(2) not null references dept(deptno))
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
11
create table DEPT (deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13),
mgr number(4) not null references emp(empno))
Für die Überwachung dieser Integritätsbedingungen mit Triggern muß man zwei Trigger
erstellen: einen für die Tabelle DEPT und einen für die Tabelle EMP. Beide sind erheblich
aufwendiger zu erzeugen, und ihre Abarbeitung dauert durch die select-Operationen auf
den anderen Tabellen länger als die obigen Constraints. Dafür werden noch Fehlermeldungen ausgegeben, bevor das Rollback durchgeführt wird.
create trigger dept_trig
after insert or update or delete on DEPT
for each row
declare
anzahl number;
begin
if deleting
/∗ Anzahl der Angestellten in der gelöschten Abteilung > 0 ? ∗/
then select count(empno) into anzahl from EMP
where deptno=:old.deptno;
if anzahl>0
then raise_application_error(-20001,’In der Abteilung ’ ||
:new.deptno ||
’ arbeiten noch Angestellte !’);
end if;
/* Anzahl der Angestellten, die Abteilungsleiter sind, = 0 ? */
else select count(empno) into anzahl from EMP
where empno=:new.mgr;
if :new.mgr is null
/* kein Abteilungsleiter */
or anzahl=0
then raise_application_error(-20002,’Zur Abteilung ’ ||
:new.deptno ||
’ existiert kein Abteilungsleiter ’ ||
’in der Tabelle EMP !’);
end if;
end if;
end;
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
12
create trigger emp_trig
after insert or update or delete
for each row
declare
anzahl number;
begin
if deleting
/* Anzahl der vom Angestellten geleiteten Abteilungen > 0 ? */
then select count(deptno) into anzahl from DEPT
where mgr=:old.empno;
if anzahl>0
then raise_application_error(-20003,’Der Angestellte ’ ||
:old.empno ||
’ ist noch Abteilungsleiter einer Abteilung !’);
end if;
/* Anzahl der Abteilungen, in der der Angestellte arbeitet, = 0 ? */
else select count(deptno) into anzahl
where deptno=:new.deptno;
if :new.deptno is null
or anzahl=0
then raise_application_error(-20004,’Der Angestellte ’ ||
:new.empno ||
’ arbeitet in keiner existierenden Abteilung’);
end if;
end if;
end;
Will man in die anfangs noch leere Relation einen Angestellten einfügen, so muß man
zuerst eine Abteilung einfügen. Eine Abteilung kann jedoch erst eingegeben werden, wenn
schon ein Abteilungsleiter in der Angestellten-Tabelle existiert. Einer der Constraints
bzw. Trigger für die erste oder die zweite Integritätsbedingung muß also erst einmal abgeschaltet und später wieder aktiviert werden. Es zeigt sich, daß der Eingabekomfort stark
eingeschränkt wird. Außerdem hat es wenig Sinn, Integritätsbedingungen aufzustellen, die
die Eingabe überwachen sollen, dann aber genau bei dieser Aktion außer Kraft gesetzt
werden.
KAPITEL 2. INTEGRITÄTSÜBERWACHUNG IN ORACLE7
13
Hier würden nun verzögerte Trigger Abhilfe schaffen. Man könnte die Daten in beliebiger
Reihenfolge in die Relation einfügen, und am Ende würde die logische Korrektheit der
Transaktion geprüft werden.
Kapitel 3
Erweiterung des
Oracle7-Triggerkonzepts
3.1
Syntax verzögerter Trigger
Ein verzögerter Trigger besteht wie ein Immediate-Trigger auch aus der Angabe des bzw.
der auslösenden Ereignisse, der Vorbedingung und der Reaktion. Im Triggerkopf sind
anzugeben:
• der Triggername
• die auslösenden Ereignisse
• der Tabellenname
Im Triggerrumpf wiederum steht das eigentliche Triggerprogramm, in dem die Vorbedingung geprüft und entsprechend reagiert wird. Im Rumpf ist es außerdem möglich, die neu
eingefügten, die gelöschten und die veränderten Tupel vor und nach der Änderung zu
referenzieren.
Die Syntax der verzögerten Trigger ist stark an die der in Oracle7 vorhandenen Trigger
angelehnt. Neu ist das zusätzliche Schlüsselwort deferred. Im Triggerkopf müssen der
Triggername, die auslösenden Ereignisse und der betreffende Tabellenname spezifiziert
werden:
14
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
15
create deferred trigger <trig name> Triggername
after
insert or
update or
delete
DB-Operation
on <table name>
Tabellenname
(declare)
optional
<Variablen- und
Cursor-Deklarationen>
Triggerrumpf
<PL/SQL-Programm-Code>
Dabei kann die declare-Anweisung verwendet werden, wie es bei den Immediate-Triggern
verlangt wird, sie kann aber auch weggelassen werden, wie es bei den Prozeduren in
Oracle7 gemacht werden muß. Nur um das look-and-feel der Immediate-Trigger bei den
verzögerten Triggern zu erhalten, wurde diese Angabe zugelassen.
Innerhalb des Triggerrumpfs können auch bei den verzögerten Triggern Verzweigungen in
Abhängigkeit vom auslösenden Ereignis durchgeführt werden, wie es bei den DatensatzTriggern auch der Fall ist. Die zur Verfügung stehenden Konstrukte sind:
if inserting then <SQL-Block>
if updating then <SQL-Block>
if deleting then <SQL-Block>
Dabei ist zu beachten, daß beim Updating-Konstrukt keine Spaltenangabe mehr erlaubt
ist. Dasselbe gilt für den Triggerkopf, wo als auslösendes Ereignis auch nur das allgemeine
Update möglich ist. Dadurch wurde die Implementation vereinfacht. Bei den ImmediateTriggern war an dieser Stelle noch die Einschränkung auf bestimmte Spalten erlaubt.
Aus demselben Grunde fehlt bei den verzögerten Triggern im Vergleich zu den vorhandenen die Restriktion im Triggerkopf. Die Triggerbedingung muß im Triggerrumpf spezifiziert werden.
Über die Priorität eines Triggers werden explizit keine Angaben gemacht. Da die verzögerten Trigger alle in eine Datei geschrieben werden müssen, bevor sie in Prozeduren bzw.
Trigger für die Differenztabellen umgewandelt werden, wird ihre Reihenfolge in der Datei
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
16
als implizite Angabe von Prioritäten angesehen. D.h. der Trigger mit der höchsten Priorität steht in der Datei an erster Stelle.
Will man nun die Integritätsbedingung, die verlangt, daß in einer Abteilung der Abteilungsleiter das höchste Gehalt bezieht, mit einem verzögerten Trigger umsetzen, so sieht
dieser wie folgt aus:
create deferred trigger gehalt
after insert or update on emp
declare
/* Cursor lesen zeilenweise Tupel aus einer Tabelle aus. Dazu werden
sie mit open initialisiert und in einer Schleife mit fetch ausgelesen. Mit close wird der Vorgang abgeschlossen. */
/* liest die neu eingetragenen Manager aus, deren Gehalt niedriger
als das eines Mitarbeiters ist */
cursor ins_mgr_cur is
select empno from i_gehalt i
where exists
(select empno from emp
where mgr = i.empno and sal > i.sal);
/* diejenigen neuen Angestellten, deren Manager weniger verdient als
sie */
cursor ins_emp_cur is
select empno from i_gehalt i
where exists
(select empno from emp
where i.mgr = empno and sal < i.sal);
/* Manager und ihr altes Gehalt, wo das neue geringer ist als das
eines Mitarbeiters */
cursor upd_mgr_cur is
select new_empno, old_sal from u_gehalt u
where exists
(select empno from emp
where mgr = u.new_empno and sal > u.new_sal);
/* Angestellte und ihr altes Gehalt, deren Manager weniger
verdient */
cursor upd_emp_cur is
select new_empno, old_sal from u_gehalt u
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
where exists
(select empno from emp
where u.new_mgr = empno and sal < u.new_sal);
ins_mgr number(4);
ins_emp number(4);
upd_mgr upd_mgr_cur%rowtype;
upd_emp upd_emp_cur%rowtype;
begin
if inserting then
/* alle neuen Manager entfernen, die zu wenig verdienen */
open ins_mgr_cur;
loop
fetch ins_mgr_cur into ins_mgr;
exit when ins_mgr_cur%notfound;
delete from emp
where empno = ins_mgr;
end loop;
close ins_mgr_cur;
/* alle neuen Angestellten entfernen, die zuviel verdienen */
open ins_emp_cur;
loop
fetch ins_emp_cur into ins_emp;
exit when ins_emp_cur%notfound;
delete from emp
where empno = ins_emp;
end loop;
close ins_emp_cur;
end if;
if updating then
/* Gehalt der Manager mit zu niedrigem neuen Gehalt auf alten
Wert setzen */
open upd_mgr_cur;
loop
fetch upd_mgr_cur into upd_mgr;
exit when upd_mgr_cur%notfound;
update emp
set sal = upd_mgr.old_sal
where empno = upd_mgr.new_empno;
end loop;
close upd_mgr_cur;
17
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
18
/* Gehalt der Angestellten mit zu hohem neuen Gehalt auf alten
Wert setzen */
open upd_emp_cur;
loop
fetch upd_emp_cur into upd_emp;
exit when upd_emp_cur%notfound;
update emp
set sal = upd_emp.old_sal
where empno = upd_emp.new_empno;
end loop;
close upd_emp_cur;
end if;
end;
Es werden als Reaktion auf Einträge, die die Integritätsbedingung verletzen, die Gehälter
der Betroffenen wieder auf ihren alten Wert gesetzt. Zunächst werden die neu eingefügten
Tupel untersucht, danach die veränderten. Dabei werden erst die Abteilungsleiter und
dann die Mitarbeiter geprüft und gegebenenfalls deren Gehälter geändert.
3.2
Ausführungssemantik
Um den notwendigen Abarbeitungsmechanismus verzögerter Trigger zu beschreiben,
wird eine mengenorientierte Beschreibung des Transaktionsbegriffs eingeführt (vgl.
[Hanson and Widom, 1992]).
Zunächst sei die folgende Syntax für einen Block von SQL-Operationen gegeben:
transaction := sql − op; sql − op; . . . ; sql − op
sql − op := update − op | delete − op | insert − op
update − op := update table
set columns = expressions
where predicate
delete − op := delete from table
where predicate
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
19
insert − op := insert into table
values hv1 , v2 , . . . , vn i
| insert into table
{ select − op }
select − op := select columns
from tables
where predicate
Die Prädikate können dabei beliebig komplex sein und auch select-Operationen enthalten.
Innerhalb einer Tabelle wird eine Zeile, also ein Tupel, eindeutig durch ihren TupleIdentifier identifiziert. Auch wenn man alle Werte eines Tupels ändert, der Tupelidentifier
bleibt erhalten.
Betrachtet man die Veränderung eines DB-Zustands durch einen Block von DB-Operationen, so kann man den Nettoeffekt beschreiben als
• die neu eingefügten Tupel - das sind die Tupel, die nach dem Block existieren, jedoch
vorher nicht vorhanden waren.
+ die gelöschten Tupel - das sind die Tupel, die vor dem Block vorhanden waren, aber
nicht mehr danach.
+ die veränderten Tupel - das sind die Tupel, die nach dem Block andere Werte haben
als vorher.
Man kann also jedem Block von DB-Operationen ein Tripel (I,D,U) zuordnen, wobei I die
Menge der neu eingefügten Tuple-Identifier ist, D die Menge der nicht mehr vorhandenen
und U die Menge derjenigen, wo die Tupel veränderte Werte enthalten. Die Ausführung
eines Blocks von DB-Operationen nennt man Transition, und mit dem Effekt einer Transition bezeichnet man das zugehörige Tripel (I,D,U).
Dabei sind einige Regeln zu beachten. Ein Tupel, das während eines Blocks neu eingefügt
und später dann verändert wird, wird genau so behandelt, als ob das Tupel nur mit den
zuletzt gültigen Werten eingefügt worden wäre. Ein Tupel, das erst eingefügt und dann
wieder gelöscht wird, tritt im Nettoeffekt gar nicht auf. Ein Tupel, das mehrfach verändert
und danach gelöscht wurde, wird so behandelt, als ob das Tupel mit den ursprünglichen
Werten gelöscht wurde. Wurde es lediglich mehrfach verändert, dann betrachtet man nur
den Original- und den Endzustand des Tupels. Ein Tupel, das zunächst gelöscht, später
aber wieder neu eingefügt worden ist, hat einen neuen Tuple-Identifier erhalten, gilt also
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
20
als gelöscht und neu eingefügt.
Findet nun eine Transition T1 statt, so ändert sich der DB-Zustand S0 nach S1 . Dabei
tritt der Effekt E1 auf:
E1 S1
S0
T1
Die nachfolgende Tabelle gibt an, welcher Effekt E1 entsteht, wenn die Transaktion T1 aus
zwei DB-Operationen besteht, die jeweils ein Tupel t einfügen I(t), verändern U(t,t0 ) oder
löschen D(t):
T1
I(t)
U(t,t0 )
I(t)
D(t)
U(t,t0 ) U(t0 ,t00 )
U(t,t0 ) D(t0 )
U(t,t0 ) U(t0 ,t)
I(t)
D(t)
E1
0
I(t )
∅
U(t,t00 )
D(t)
∅
D(t) ∪ I(t)
Jeder verzögerte Trigger soll die Einhaltung der ihm zugrundeliegenden Integritätsbedingungen am Ende einer Transaktion gewährleisten. Also wird jedem Trigger ein Effekt E
zugeordnet, der den Nettoeffekt der DB-Operationen seit der letzten Feuerung des Triggers bzw. dessen Definition beschreibt. Wurden mehrere Trigger definiert, so unterscheiden
sich die für sie relevanten Effekte in Abhängigkeit von der Feuerungsreihenfolge.
Es befinde sich eine Datenbank zunächst im Zustand S1 nach einer Transaktion T1 mit
Effekt E1 . Dieser Effekt sei zunächst für alle verzögerten Trigger relevant. Nun werde ein
Trigger Ri aufgrund des Effekts E1 gefeuert. Dann finde durch die Triggerausführung eine
weitere Transition TRi mit Effekt E2 statt. Ein neuer DB-Zustand S2 wird erreicht. Zu
allen Triggern Rj 6= Ri gehört jetzt der Effekt E20 := E1 ◦ E2 . Lediglich zum Trigger Ri
gehört nur der Effekt E2 , da bei diesem auf den Effekt E1 schon reagiert wurde. Siehe dazu
auch die folgende Tabelle:
E1
I(t)
I(t)
U(t,t0 )
U(t,t0 )
U(t,t0 )
D(t)
E2
U(t,t0 )
D(t)
U(t0 ,t00 )
D(t0 )
U(t0 ,t)
I(t)
E1 ◦ E2
I(t0 )
∅
U(t,t00 )
D(t)
∅
D(t) ∪ I(t)
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
21
Es wird deutlich, daß sich mit jeder weiteren Transition TRj , die durch die Abarbeitung
eines Triggers entsteht, die für die Trigger relevanten Effekte immer mehr voneinander
unterscheiden (in einem DB-Zustand Sl ).
Während einer Triggerabarbeitung verändert sich nicht der zugrundeliegende Effekt, denn
erst eine Transition – in diesem Falle also die vollständige Abarbeitung des Triggers – führt
in diesem Modell zu einem neuen DB-Zustand mit dazugehörigem neuen Effekt.
Erst wenn für jeden Trigger gilt, daß seine Bedingung auf dem zu ihm gehörenden Effekt
nicht mehr erfüllt wird, wenn also ein Endzustand erreicht ist, spricht man vom Transaktionsende.
Wird innerhalb einer Triggerabarbeitung ein Rollback durchgeführt, so werden alle DBOperationen bis zum letzten Commit zurückgenommen. Falls nicht durch einen verzögerten Trigger ein Commit durchgeführt wurde, sind die zu allen verzögerten Triggern
gehörenden Effekte leer. Wurde jedoch ein Commit durch einen verzögerten Trigger ausgeführt und daraufhin der Zustand Si erreicht, so entsprechen die zu den verzögerten
Triggern gehörenden Effekte nach dem Rollback denen, die sie im Zustand Si besaßen.
Wird von einem verzögerten Trigger eine Fehlermeldung mit Hilfe der Funktion
raise application error ausgegeben, so werden alle von diesem Trigger durchgeführten DB-Operationen zurückgenommen und die Feuerung aller Trigger abgebrochen. Für
jeden Trigger ist dann wieder derselbe Effekt relevant, wie vor der Feuerung des letzten
Triggers.
Um ein Beispiel für den Abarbeitungsmechanismus zu geben, wird folgende Integritätsbedingung auf der Angestellten-Relation EMP aufgestellt, die einen kaskadierenden Trigger
zur Folge hat, der also aufgrund seines Effekts wieder gefeuert werden kann:
Wird ein Abteilungsleiter versetzt, so müssen auch alle seine Mitarbeiter
in die neue Abteilung versetzt werden.
Der dazugehörige prüfende verzögerte Trigger hat dann folgendes Aussehen:
create deferred trigger Manager_Versetzung
after update on emp
declare
/* liest die Daten der versetzten Vorgesetzen mit Mitarbeitern*/
cursor man_cur is select * from u_manager_versetzung u
where old_deptno != new_deptno and exists
(select empno from emp
where u.new_empno = mgr);
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
22
man u_manager_versetzung%rowtype;
begin
open man_cur;
loop
fetch man_cur into man;
exit when man_cur%notfound;
/* versetze alle Mitarbeiter der Vorgesetzen in dessen neue
Abteilung. */
update emp
set deptno = man.new_deptno
where mgr = man.old_empno;
end loop;
close man_cur;
end;
Die Ausprägung der Relation EMP vor der Transaktion im Zustand S0 sei die folgende:
EMPNO
DEPTNO
MGR
JOB
ENAME
---------- ---------- ---------- --------- ---------7839
20
PRESIDENT KING
7566
20
7839 MANAGER
JONES
7902
20
7566 ANALYST
FORD
7369
20
7902 CLERK
SMITH
Es werde die DB-Operation T1 ausgeführt:
update emp set deptno=10 where ename=’JONES’
EMPNO
DEPTNO
MGR
JOB
ENAME
---------- ---------- ---------- --------- ---------7839
20
PRESIDENT KING
7566
10
7839 MANAGER
JONES
7902
20
7566 ANALYST
FORD
7369
20
7902 CLERK
SMITH
Nach Erreichen des Transaktionsendes besitzt die Relation EMP diese Ausprägung:
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
23
exec my_commit
EMPNO
DEPTNO
MGR
JOB
ENAME
---------- ---------- ---------- --------- ---------7839
20
PRESIDENT KING
7566
10
7839 MANAGER
JONES
7902
10
7566 ANALYST
FORD
7369
10
7902 CLERK
SMITH
Die Relation wurde zunächst durch die DB-Operation T1 in den Zustand S1 überführt,
was dem Effekt E1 entspricht. Bei der ersten Triggerfeuerung T2 wurde der Angestellte
FORD versetzt, Effekt E2 , und die Relation ging in den Zustand S2 über. Als Effekt E3 der
zweiten Triggerfeuerung T3 wurde SMITH versetzt und der Zustand S3 erreicht. Die dritte
Triggerfeuerung T4 bewirkt keine Veränderung mehr. Der Endzustand S4 ist erreicht.
E1E2E3E4S0
S1
S2
S3
S4
T1
T2
T3
T4
3.3
Umsetzung auf Oracle7
Die eben beschriebene Berechnung des Nettoeffekts wird unter Ausnutzung der in Oracle7
vorhandenen Datensatz-Trigger realisiert. Für jede Tabelle, auf der ein verzögerter Trigger
definiert ist, werden drei Hilfstabellen erzeugt, die den Mengen I, D und U aus dem EffektTripel entsprechen. Bezeichnet werden sie später mit i <trig name>, d <trig name>,
u <trig name>. Jetzt sollen sie der Einfachheit halber Inserted-, Deleted- bzw. UpdatedTabelle genannt werden. Alle drei Tabellen enthalten neben den Spaltenwerten noch
einen Eintrag für den Tuple-Identifier des entsprechenden Tupels. Die Hilfstabellen werden Transition-Tables oder ∆-Tabellen genannt. Während der Abarbeitung eines Triggers
können diese Tabellen im Triggerrumpf referenziert werden.
Weiterhin wird auf jeder betroffenen Tabelle ein Datensatz-Trigger definiert, der dafür sorgen soll, daß die Änderungen des DB-Zustands durch eine Transition in den ∆-Tabellen
widergespiegelt werden.
Die einzelnen verzögerten Trigger werden in Prozeduren umgewandelt, die von der
zusätzlichen Prozedur my commit aufgerufen werden. Der Anwender soll das Transaktionsende mit dem Aufruf dieser Prozedur durch Eingabe des Befehls exec my commit
auf SQLPLUS-Ebene angeben. Dabei wird dann die prioritätsgesteuerte Feuerung der
verzögerten Trigger vorgenommen. Der Ablauf der prioritätsgesteuerten Feuerung im einzelnen wird in Abschnitt 3.3.3 beschrieben.
Zu jeder Tabelle können beliebig viele Trigger erstellt werden. So können auf einer Tabelle
mehrere Trigger mit verschiedenen Prioritäten definiert werden. Dies ist derzeit bei den
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
24
nicht verzögerten Triggern von Oracle7 nicht möglich.
Es folgt die Beschreibung der Umsetzung im einzelnen.
3.3.1
Die ∆-Tabellen und der Hilfstrigger
Prinzipiell stehen mehrere Möglichkeiten der Verwaltung der Mengen I, D und U durch
Hilfstabellen zur Verfügung. Damit eng verbunden ist das Aussehen des Hilfstriggers, der
nach jeder DB-Operation die veränderten Tupel in die Inserted-, Deleted- und UpdatedTabellen einfügen soll.
Alternative I
Die einfachste Möglichkeit ist die Verwendung von zwei Sätzen ∆-Tabellen pro verzögertem Trigger. Der eine Satz Tabellen, die Hilfs-Inserted-, Hilfs-Deleted- und die HilfsUpdated-Tabelle, wird ständig durch den Hilfstrigger aktualisiert. Das geschieht auch
während der Abarbeitung des dazugehörigen verzögerten Triggers. Dabei wird entsprechend den Regeln der Effektberechnung aus Abschnitt 3.2 jedes neu eingefügte, veränderte
oder gelöschte Tupel mittels des Hilftriggers in die Hilfs-∆-Tabellen eingetragen.
Um den Effekt für den verzögerten Trigger nach seiner Feuerung zu bestimmen, werden
vor seiner Feuerung Kopien der Hilfs-∆-Tabellen angelegt. Diese Kopien bilden die eigentlichen ∆-Tabellen. Sie bleiben unverändert während der Triggerabarbeitung und können
im Triggerrumpf referenziert werden.
Betrachtet man die Regeln der Effektberechnung, so ergibt sich durch die Abarbeitung
des Trigger Rl als neuer Transitionseffekt zu diesem Trigger lediglich der Unterschied zwischen den DB-Zuständen Si vor und Si+1 nach der Triggerabarbeitung:
Ei+1 Si+1
Si
TRl
Ek0 i+1 := Eki ◦ Ei+1 gelten für alle Trigger Rk 6=Rl und Ei+1 für den Trigger Rl . Dabei waren
Eki die für die Trigger Rk relevanten Effekte im Zustand Si .
Falls die Überprüfung, ob in den ∆-Tabellen ein den Trigger Rl auslösendes Ereignis existiert, negativ ausfällt, ist der Effekt Ei+1 für diesen Trigger anschließend leer.
Die Berechnung des Effekts Ei+1 für den Trigger Rl muß also auf anfangs leeren Mengen
I, D und U erfolgen. Dazu werden nach dem Kopieren der Hilfs-∆-Tabellen alle darin
enthaltenen Tupel entfernt. Jetzt wird deutlich, daß die Kopien notwendig wurden, um
den auslösenden Effekt bei der Triggerabarbeitung referenzieren zu können.
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
25
Da in Oracle7 auf jeder Tabelle nur ein Datensatz-Trigger mit auslösendem Ereignis after
insert or delete or update existieren darf, kann für alle auf einer Tabelle existierenden verzögerten Trigger zusammen nur ein Hilfstrigger erzeugt werden, der dann die
Hilfs-∆- und ∆-Tabellen für alle diese Trigger verwaltet1 . Für den hier beschriebenen
Zweck wäre die Verwendung eines Hilfstriggers pro verzögerten Trigger zwar übersichtlicher, aber bei weitem nicht effizienter, müssen doch statt einem auslösenden Ereignis
und einer Triggerbedingung bei der Feuerung des Einfüge-Triggers hier dann für jeden
einzelnen verzögerten Trigger diese Bedingungen überprüft werden.
Der Hilfstrigger hat nun folgendes Aussehen für einen verzögerten Trigger <trig name>
auf einer Tabelle <table name>:
create trigger <table_name>_def_trigger
after insert or delete or update on <table_name>
for each row
begin
if inserting
/* alle neuen Tupel mit Tupel-ID eintragen */
then insert into hilf_i_<trig_name>
values :new.tuple, :new.rowID;
if updating
/* falls Tupel-ID schon in Inserted, dort die neuen Werte
einsetzen */
then if row_ID in hilf_i_<trig_name>
then update hilf_i_<trig_name>
set tuple = :new.tuple
where rowID = hilf_i_<trig_name>.rowID;
/* falls Tupel-ID schon in Updated, dort die neuen Werte
einsetzen */
else if row_ID in hilf_u_<trig_name>
then update hilf_u_<trig_name>
set new_tuple = :new.tuple
where rowID = hilf_u_<trig_name>.rowID;
/* sonst in Updated eintragen */
else insert into hilf_u_<trig_name>
values :old.tuple, :new.tuple, :new.rowID;
1
In Oracle7.1 kann man mittlerweile mehrere Datensatz-Trigger dieser Art auf einer einzigen Tabelle
definieren.
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
26
delete from hilf_u_<trig_name>
where new_tuple = old_tuple;
if deleting
/* falls Tupel-ID schon in Inserted, dann dort entfernen */
then if row_ID in hilf_i_<trig_name>
then delete from hilf_i_<trig_name>
where row_ID = hilf_i_<trig_name>.row_ID;
/* falls Tupel-ID schon in Updated, dann dort entfernen
und mit den alten Werten in Deleted eintragen */
else if row_ID in hilf_u_<trig_name>
then delete from hilf_u_<trig_name>
where row_ID = hilf_u_<trig_name>.row_ID;
insert into hilf_d_<trig_name>
values :old.tuple;
/* sonst in Deleted eintragen */
else insert into hilf_d_<trig_name>
values :old.tuple;
end;
Der Nachteil dieser Methode von zwei Sätzen ∆-Tabellen liegt auf der Hand: Es gibt eine große Redundanz der Daten. Außerdem ist der Aufwand mit dem Anlegen von sechs
∆-Tabellen sehr groß.
Alternative II
Es geht auch mit weniger, wenn man in einer zusätzlichen Spalte je Inserted-, Deletedund Updated-Tabelle noch die Information festhält, ob das Tupel vor Abarbeitung des
Triggers schon existierte. Dann kann man nach Triggerabarbeitung einfach alle Tupel mit
diesem Vermerk löschen.
Doch auch hier treten Probleme auf: Will man die Tupel im Triggerrumpf referenzieren,
so muß das Triggersubsystem im Triggerrumpf ergänzen, daß nur die Tupel mit Vermerk
gemeint sind. Das sehe z.B. so aus:
...
select count(empno) into anzahl from i_<trig_name>
where deptno is null
and referenzierbar = ’ja’)
if anzahl > 0 then ...
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
27
Auch der Hilfstrigger müßte nun die neuen Vermerke über die Referenzierbarkeit beachten. Er erhielte folgendes Aussehen:
create trigger <table_name>_def_trigger
...
if updating
then if row_ID in i_<trig_name>
and referenzierbar = ’nein’
/* neu */
then update i_<trig_name>
set tuple = :new.tuple
where rowID = i_<trig_name>.rowID;
else if row_ID in i_<trig_name>
and referenzierbar = ’ja’
/* neu */
then insert into u_<trig_name>
values :old.tuple, :new.tuple, :new.rowID, ’nein’;
/* neu */
else if row_ID in u_<trig_name>
and referenzierbar = ’nein’ /* neu */
then update u_<trig_name>
set new_tuple = :new.tuple
where rowID = u_<trig_name>.rowID;
else if row_ID in u_<trig_name>
and referenzierbar = ’ja’
/* neu */
then insert into u_<trig_name>
values :old.tuple, :new.tuple, :new.rowID, ’nein’;
/* neu */
else insert into u_<trig_name>
values :old.tuple, :new.tuple, :new.rowID, ’nein’;
/* neu */
delete from hilf_u_trig1
where new_tuple = old_tuple;
...
end;
Vor der Feuerung eines Triggers müssen dann natürlich alle Tupel in den dazugehörigen ∆-Tabellen in der Spalte referenzierbar auf ja gesetzt werden. Nach der Triggerabarbeitung werden dann alle Tupel mit dem Vermerk ja in der Spalte referenzierbar
gelöscht.
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
28
Alternative III
Eine dritte Variante geht noch weiter als die zweite: Jetzt soll pro Tabelle, auf der verzögerte Trigger existieren, nur noch ein einziger Satz ∆-Tabellen erstellt werden. In diesem muß
dann vermerkt werden, zu welchem Trigger bzw. Triggereffekt welches Tupel gehört.
Ein Beispiel soll die Kompliziertheit dieser Methode verdeutlichen: Es seien auf einer
Tabelle die verzögerten Trigger R1 , R2 und R3 definiert. In der Ausgangstransition wird
ein Tupel t eingefügt. Trigger R1 verändert dieses Tupel nach t0 , Trigger R2 dasselbe Tupel
(jetzt t0 ) nach t00 und Trigger R3 nach t000 :
Transition
T0
TR1
TR2
TR3
Effekt zu Trigger
R1
R2
R3
i(t)
i(t)
i(t)
0
0
u(t,t ) i(t )
i(t0 )
00
0 00
u(t,t ) u(t ,t ) i(t00 )
u(t,t000 ) u(t0 ,t000 ) u(t00 ,t000 )
Mit dieser Methode kann man zwar einige Eintragungen und Tabellen sparen, der Abarbeitungsmechanismus des Hilfstriggers wird jedoch schwieriger, weil jeweils erkannt werden
muß, welches Tupel zu welchem Trigger gehört und ob es referenziert wird. Dabei können
mehrere Tupel mit demselben Tuple-Identifier auftreten, die allerdings unterschiedlich
behandelt werden müssen. Außerdem müssen immer noch die Referenzierbarkeitsbedingungen eingesetzt werden.
Ich habe die erste der drei Methoden gewählt, da sie am einfachsten zu implementieren ist.
Der Triggerrumpf muß bis auf die if inserting ... Konstrukte nicht weiter untersucht
werden. Sobald ein Tool existiert, das dem Benutzer hilft, verzögerte Trigger nach seinen
Wünschen zu erstellen, so daß er sie im einzelnen nicht mehr selbst programmieren muß,
sollte das Programm auf die zweite oder dritte Methode umgestellt werden. Dann wird
vom Tool die Angabe der Referenzierbarkeitsbedingung übernommen und dem Triggersubsystem bleibt jede Triggerrumpf-Untersuchung erspart.
3.3.2
Speicherung verzögerter Trigger
Oracle7 bietet ein prozedurales Konzept an. Dazu gehören die bereits besprochenen Trigger, sowie Prozeduren und Funktionen. Allen gemeinsam ist, daß sie einen PL/SQLBlock enthalten, wie er bereits aus den Triggern bekannt ist. Die Implementation der
verzögerten Trigger geht nun so vor sich, daß der Triggerrumpf mit den Variablenund Cursor-Deklarationen komplett in einen Prozedurrumpf hineinkopiert wird. Lediglich die if inserting, if deleting, if updating Konstrukte werden übersetzt: Aus
if inserting then ... wird:
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
29
select count(*) into i or u or d from i< trig name>;
if i or u or d> 0 then ...
Die Variable i or u or d enthält die Anzahl der jeweils betroffenen eingefügten, veränderten bzw. gelöschten Tupel. Aus dem verzögerten Trigger gehalt aus Abschnitt 3.1
entstehen dann die ∆-Tabellen i gehalt, u gehalt, d gehalt, die Hilfs-∆-Tabellen
hilf i gehalt, hilf u gehalt, hilf d gehalt, der Trigger emp def trigger, die Prozedur my commit sowie aus dem Triggerrumpf die Prozedur gehalt. Hier ein Auszug aus
der Prozedur gehalt:
create procedure gehalt
as
i_or_u_or_d number;
/* neu */
cursor ins_mgr_cur is
select empno from i_gehalt i
where exists
(select empno from emp
where mgr = i.empno and sal > i.sal);
...
ins_mgr number(4);
...
begin
/* nun statt if inserting then */
select count(*) into i_or_u_or_d from i_gehalt;
if i_or_u_or_d>0 then
open ins_mgr_cur;
loop
fetch ins_mgr_cur into ins_mgr;
exit when ins_mgr_cur%notfound;
delete from emp
where empno = ins_mgr;
end loop;
close ins_mgr_cur;
...
end;
3.3.3
Prozedur my commit
Die Prozedur my commit erfüllt, wie bereits besprochen, eine Doppelfunktion: Zum einen
kennzeichnet ihr Aufruf auf SQLPLUS-Ebene mit exec my commit das Transaktionsende, zum anderen übernimmt sie die prioritätsgesteuerte Feuerung der verzögerten Trigger,
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
30
also den Aufruf der Prozeduren.
Der Ablauf sieht so aus, daß in einer Schleife die Trigger solange gefeuert werden, bis
keine Triggerbedingung mehr erfüllt ist. Die Triggerbedingung reduziert sich hier nur auf
die Existenz des Auslöseereignisses (vgl. dazu die Syntax in Abschnitt 3.1). Dabei wird
nach jedem Ende einer Triggerabarbeitung die Bedingung des Triggers mit der höchsten
Priorität geprüft. Ist sie nicht erfüllt, wird die Bedingung des Triggers mit der nächst
niedrigeren Priorität untersucht usw. bis ein Trigger gefeuert werden muß. Danach beginnt die Überprüfung wieder beim Trigger mit der höchsten Priorität:
procedure my_commit
as
...
begin
loop
/* Testen der Triggerbedingung des ersten Triggers */
if condition_trig1 = true
then trig1;
/* Testen der Triggerbedingung des zweiten Triggers */
else if condition_trig2 = true
then trig2;
/* Testen der Triggerbedingung des dritten Triggers */
else if condition_trig3 = true
then trig3;
...
/* Falls keine Triggerbedingung zutrifft: Transaktionsende */
else exit;
end loop;
end;
Die Reihenfolge, in der die Trigger in der Prozedur my commit auftreten, entspricht ihrer
Reihenfolge in der Datei, in der die verzögerten Trigger definiert sind.
Das ist natürlich noch nicht alles, was in my commit passiert. Wie in Abschnitt 3.3.1
Alternative I beschrieben, müssen vor dem Aufruf der Prozeduren jeweils noch die Inhalte
der Hilfs-∆-Tabellen in die eigentlichen noch leeren ∆-Tabellen hineinkopiert werden,
dann werden die Hilfs-∆-Tabellen geleert. Ganz am Ende werden dann noch einmal die
∆-Tabellen geleert. Für einen after insert or update-Trigger <trig name> sieht das
dann so aus:
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
procedure my_commit
as
ins number;
del number;
upd number;
...
begin
loop
ins=0;
del=0;
upd=0;
/* referenzierbare Tabellen leeren */
delete from i_<trig_name>;
delete from d_<trig_name>;
delete from u_<trig_name>;
/* Hilfstabellen in referenzierbare hineinkopieren */
insert into i_<trig_name>
select * from hilf_i_<trig_name>;
insert into d_<trig_name>
select * from hilf_d_<trig_name>;
insert into u_<trig_name>
select * from hilf_u_<trig_name>;
/* Hilfstabellen leeren */
delete from hilf_i_<trig_name>;
delete from hilf_d_<trig_name>;
delete from hilf_u_<trig_name>;
/* Anzahl der Inserted-Tupel bestimmen */
select count(*) into ins from i_<trig_name>;
/* Anzahl der Updated-Tupel bestimmen */
select count(*) into upd from u_<trig_name>;
/* falls solche Tupel existieren, ’Trigger’ feuern,
also Prozedur aufrufen */
if ins>0 or del>0 or upd>0
then
<trig_name>;
else
...
/* analog bei den weiteren Triggern */
31
KAPITEL 3. ERWEITERUNG DES ORACLE7-TRIGGERKONZEPTS
32
else exit; /* solange Triggerbedingungen zutreffen */
end loop;
/* Leeren der Hilfstabellen von eventuell noch vorhandenen
Eintragungen */
delete from hilf_i_<trig_name>;
delete from hilf_d_<trig_name>;
delete from hilf_u_<trig_name>;
...
/* analog bei den weiteren Triggern */
end;
3.3.4
Einschränkungen
Bisher ist es nicht möglich, verzögerte Trigger auf Tabellen zu erstellen, die dem Anwender nicht gehören, auch wenn dafür die Zugriffsrechte bestehen. Das liegt daran, daß der
Zugriffsmechanismus auf die Spaltennamen und -typen fremder Tabellen über das Data
Dictionary nicht gerade plausibel dokumentiert ist. Wenn Möglichkeiten gefunden werden,
an diese Informationen heranzukommen, sollte diese Einschränkung aufgehoben werden.
Es sind zwar beliebig viele verzögerte Trigger auf einer Tabelle möglich, jedoch kann kein
Trigger Ereignisse in zwei Tabellen gleichzeitig überwachen. Hierfür muß eine Lösung mit
zwei Triggern gewählt werden.
Kapitel 4
Programmbeschreibung
Das Programm Trigger, das die verzögerten Trigger aus einer Datei ausliest und dazu die
∆-und Hilfs-∆-Tabellen, Trigger und Prozeduren unter Oracle7 anlegt, ist in Pro*C geschrieben. Oracle7 bietet mehrere Programmiersprachen an, die um Sprachkonstrukte zur
Kommunikation mit dem Oracle7 DMBS erweitert wurden. Pro*C ist die Erweiterung
der Programmiersprache C (vgl. [O7-Pro*C]). Jedem Pro*C-Befehl wird die Ankündigung EXEC SQL vorangestellt. Der Pro*C-Präprozessor wandelt diese Befehle in normale
Funktionenaufrufe um. Dazu werden später beim Linken die Oracle7 Kommunikationsbibliotheken hinzugebunden. Mit dem Standard C-Compiler wird der modifizierte Quellcode übersetzt und vom Linker anschließend zusammengebunden. Das folgende Bild zeigt
diesen Ablauf anhand der Beispieldatei foo.pc.
foo.pc - die Pro*C-Datei
?
Pro*C-Präprozessor
?
foo.c - eine C-Datei
?
C-Compiler CC
?
foo.o - eine Objektdatei
?
Linker LD
?
foo - das ausführbare Programm
33
KAPITEL 4. PROGRAMMBESCHREIBUNG
34
Der Quelltext von Trigger besteht aus sechs Modulen:
• main and oracle.pc
Enthält die Funktion main, die den Programmablauf steuert, sowie alle Funktionen,
die Pro*C-Befehle enthalten.
• head and body.c
Enthält die Funktionen, die die Triggerköpfe analysieren und aus den Triggerrümpfen Prozeduren erstellen.
• my commit.c
Die Funktionen in diesem Modul erzeugen die Prozedur my commit sowie die Datei
drop all.sql.
• errors.c
In diesem Modul sind Funktionen enthalten, die Fehlermeldungen ausgeben.
Das folgende Bild gibt einen Überblick über die Abhängigkeiten der Module:
main and oracle.pc
?
head and body.c
?
my commit.c
?
errors.c
Alle Module haben eine gemeinsame Header-Datei main.h, in der die Deklarationen der
Konstanten, globalen Variablen und aller Funktionen enthalten sind.
KAPITEL 4. PROGRAMMBESCHREIBUNG
35
Der Programmablauf gestaltet sich in acht Schritten:
Anmelden bei Oracle7
?
Lesen des Dateinamens
des Trigger-Definitionsfiles
?
Triggerköpfe lesen
Triggernamen zu Tabellen zuordnen
?
Datei drop all.sql anlegen
?
∆-und Hilfs-∆-Tabellen
sowie Einfügetrigger anlegen
?
Trigger in Prozeduren umwandeln
?
my commit erzeugen
?
Abmelden bei Oracle7
Es folgt die Beschreibung der Module im einzelnen.
4.1
main.h
In dieser Headerdatei werden alle Konstanten, Typ-Definitionen und globalen Variablen
angegeben, die von den anderen Modulen benötigt werden. Zusätzlich werden alle Headerfiles für die Einbindung der notwendigen Bibliotheken aufgeführt.
Konstanten und Datenstrukturen
Im folgenden sind die Konstanten mit ihren Defaultwerten in Klammern aufgeführt:
KAPITEL 4. PROGRAMMBESCHREIBUNG
TRIGANZ :
Anzahl der insgesamt möglichen verzögerten Trigger (10)
MAXCOLUMNS :
Anzahl der maximal möglichen Spalten in einer Tabelle (10)
MAXTRIGLEN :
Anzahl der Zeichen in einem verzögerten Trigger (10000)
WORTLEN :
Länge einer von Leerzeichen getrennten Zeichenkette (100)
NAMELEN :
Länge eines Trigger- oder Tabellennamens (30)
BLOCKLEN :
Länge einer Blocks, der an Oracle7 gesandt wird (10000)
36
Die anderen Konstanten geben Fehlercodes für die Funktion show err() aus dem Modul
errors an.
Wichtige globale Datenstrukturen sind:
trig cnt
build proc
trig
table
name
insert
update
delete
flag
pos
jmp err
Anzahl der verzögerten Trigger
Flag für Beachtung der Groß-/Kleinschreibung
Struktur mit Informationen zu den verzögerten Triggern:
Tabellenname
Triggername
Flag für Insert-Trigger
analog
analog
un-/benutzt-Flag
Position des Triggerrumpfbeginns in der Datei
Struktur, die Programmzustand und Adresse für globalen Sprung speichert
Der Pro*C-Datentyp VARCHAR, entspricht einer Datenstruktur, in der die Variable arr
eine Zeichenkette und len deren Länge enthält. Die Verwendung von Variablen dieses
Datentyps anstelle von Character-Arrays wird von Oracle7 bei der Kommunikation vorgeschrieben.
4.2
main and oracle.pc
In diesem Modul befinden sich alle Funktionen, die Pro*C-Befehle enthalten1 . Um die
SQL-Fehlermeldungen und -Warnungen abfangen zu können, werden die Funktionen
sqlerror() und sqlwarn() benötigt. Da sie nichtlokale Sprünge in die main()-Funktion
ausführen, müssen sie sich mit dieser im selben Modul befinden. Eine nähere Beschreibung
1
Zur Zeit ist mir kein Weg bekannt, wie mehrere Module von Pro*C präcompiliert werden können,
ohne daß später beim Compilieren Probleme auftreten.
KAPITEL 4. PROGRAMMBESCHREIBUNG
nichtlokaler Sprünge befindet sich in [Illik, 1990]
Funktionenbeschreibung
oracle login
Aufgabe:
Baut eine Kommunikationsleitung zu Oracle7 auf, die benötigt
wird, um Tabellen, Trigger und Prozeduren in Oracle7 anstelle
des Benutzers zu erstellen. Bis zum expliziten Abmelden des Programms bei Oracle7 bleibt die Verbindung bestehen. Das Einlesen
des Passworts geschieht ohne Bildschirmecho.
Parameter: keine
oracle logout
Aufgabe:
Beendet die Kommunikationsleitung zu Oracle7.
Parameter: keine
sende block
Aufgabe:
Schickt eine Zeichenkette an Oracle7. Diese muß eine DB-Operation
oder ein PL/SQL-Block sein.
Parameter: block - Zeichenkette
build trigger and tab
Aufgabe:
Erzeugt die ∆-und Hilfs-∆-Tabellen sowie den Immediate-Trigger
zum automatischen Füllen dieser Tabellen. Das genaue Aussehen
des Hilfstriggers ist in Abschnitt 3.3.1 Alternative I in Pseudocode
beschrieben.
Parameter: keine
main
Aufgabe:
Steuert den auf Seite 35 beschriebenen Programmablauf.
Parameter: keine
sqlerror
Aufgabe:
Gibt Oracle7-Fehlermeldungen aus und sorgt für den nichtlokalen
Sprung in die Funktion main. Dort wurde der Programmzustand
mit setjmp() abgespeichert. Der Sprung bewirkt ein Wiederaufsetzen des Programms mit dem gespeicherten Zustand.
Parameter: keine
37
KAPITEL 4. PROGRAMMBESCHREIBUNG
38
sqlwarn
Aufgabe:
Gibt Oracle7-Warnungen aus und sorgt für den nichtlokalen Sprung
in die Funktion main.
Parameter: keine
4.3
head and body.c
In diesem Modul befinden sich diejenigen Funktionen, die die verzögerten Trigger analysieren und aus den Triggerrümpfen Prozeduren generieren.
Die Funktion read trigger() prüft die syntaktische Korrektheit der vom Benutzer spezifizierten verzögerten Trigger. Zunächst wird der Name der Datei mit den verzögerten
Triggern in der Variablen filename abgelegt; danach beginnt die syntaktische Analyse
des Triggerkopfes.
Werden Schlüsselwörter ausgelassen, so wird der Fehler von der Funktion mis err() aus
dem Modul errors angezeigt. Wird ein Triggername mehrfach verwendet, so wird dieser
Fehler über die Funktion show err() gemeldet. In beiden Fällen wird anschließend die
Funktion mit dem Rückgabewert -1 verlassen.
Eine Analyse des Triggerrumpfs wird nicht vorgenommen, da dieser, wenn er erst einmal
in eine Prozedur umgewandelt wurde, beim Hinübersenden nach Oracle7 dort sowieso auf
syntaktische und semantische Korrektheit überprüft wird.
Funktionenbeschreibung
Exportierte Funktionen sind:
read trigger
Aufgabe:
Prüft die syntaktische Korrektheit der vom Benutzer spezifizierten
verzögerten Trigger.
Parameter: keine
read block
Aufgabe:
Die Triggerrümpfe werden hier ausgelesen und daraus Prozeduren
erstellt.
Parameter: keine
KAPITEL 4. PROGRAMMBESCHREIBUNG
39
Interne Funktionen sind:
read typ
Aufgabe:
Liest zum aktuell untersuchten Trigger den Typ (inserted, updated,
deleted) ein.
Parameter: fd - Filedeskriptor
if ersetzen
Aufgabe:
Transformiert die Zeichenketten if inserting, if deleting,
if updating in ihre PL/SQL Äquivalente. Aus dem Konstrukt
if inserting wird: select count(rowident) into i or u or d
from i trigname; if i or u or d > 0.
Parameter: trignum - aktueller Trigger
fd - Filedeskriptor
c - zuletzt gelesenes Zeichen
cnt - Filepointerposition
wort - neue Zeichenkette
get token
Aufgabe:
Liest aus einer Datei die nächste Zeichenkette bis zum nächsten
Leerzeichen, Tabulator- oder Returnzeichen ein.
Parameter: fd - Filedeskriptor
c - zuletzt gelesenes Zeichen
cnt - Filepointerposition
4.4
my commit.c
Das Modul my commit enthält zwei Funktionen. Die Funktion drop all def() erstellt die
Datei mit den Anweisungen, die alle Tabellen, Trigger und Prozeduren wieder löschen.
my commit() baut die namensgleiche Prozedur so zusammen, daß sie die in Abschnitt 3.3.3
beschriebene Gestalt erhält.
Aus den in der Datenstruktur trig vorhandenen Informationen stellt drop all def() die
Löschoperationen für die Datei drop all.sql zusammen. Ist die Datei bereits vorhanden,
wird sie ersetzt, ansonsten neu angelegt.
Für den Trigger <trig name> auf der Tabelle <table name> sehe die Datei so aus:
drop trigger <table_name>_def_trigger;
drop procedure <trig_name>;
KAPITEL 4. PROGRAMMBESCHREIBUNG
drop
drop
drop
drop
drop
drop
drop
40
table i_<trig_name>;
table u_<trig_name>;
table d_<trig_name>;
table hilf_i_<trig_name>;
table hilf_u_<trig_name>;
table hilf_d_<trig_name>;
procedure my_commit;
Dabei wird wie jeder solcher Datensatz-Trigger nur einmal zum Löschen angegeben.
Funktionenbeschreibung
my commit
Aufgabe:
Parameter:
Erstellt die gleichnamige SQL-Prozedur.
keine
drop all def
Aufgabe:
Erstellt die Datei drop all.sql.
Parameter: keine
4.5
errors.c
Wann immer ein Fehler bei der Erstellung der Trigger auftritt, sei er syntaktischer oder
semantischer Natur, springt das Programm wieder an die Stelle, wo der Dateiname eingegeben werden muß. Falls bereits Tabellen, Trigger oder Prozeduren erstellt wurden,
müssen diese wieder entfernt werden, bevor ein neuer Versuch unternommen werden kann,
verzögerte Trigger zu erstellen. Dazu wird die Datei drop all.sql angelegt, sobald die
Namen der verzögerten Trigger und der betroffenen Tabellen dem Programm bekannt
sind.
Funktionenbeschreibung
mis err()
Aufgabe:
Gibt bei der Fehlermeldung den Namen des fehlerhaften Triggers
und das fehlende Token aus.
Parameter: trig num - Triggernummer
err code - fehlendes Token
KAPITEL 4. PROGRAMMBESCHREIBUNG
show err()
Aufgabe:
Gibt bei der Fehlermeldung den Namen des fehlerhaften Triggers
und eine Beschreibung des Fehlers aus.
Parameter: trig num - Triggernummer
err code - Fehlerkonstante
41
Kapitel 5
Benutzerhandbuch
5.1
Triggererstellung
Alle verzögerten Trigger, die die Integrität überwachen sollen, müssen zunächst in eine
Datei geschrieben werden. Die Reihenfolge in der Datei gibt dabei implizit die Reihenfolge
der Feuerung an. Damit wird eine prioritätsgesteuerte Feuerung ermöglicht.
Prinzipiell sind beliebig viele verzögerte Trigger möglich. Tatsächlich wird die maximale
Anzahl der Trigger in der Konstanten TRIGANZ festgesetzt. Hier kann der Anwender das
Programm also seinen Bedürfnissen anpassen.
Die Syntax der verzögerten Trigger entspricht weitestgehend der der Trigger in Oracle7
(vgl. dazu Abschnitt 3.1). Neu ist das Schlüsselwort deferred zur Kennzeichnung der
verzögerten Trigger. Das declare ist hier optional.
Jeder Trigger muß in der Datei mit einem Slash ’/’ in der Zeile nach dem letzten end;
abgeschlossen werden. Außer dem Slash darf nichts anderes in dieser Zeile stehen. Ansonsten sind innerhalb des Triggers beliebig viele Leerzeichen und Tabulatoren zwischen den
einzelnen Wörtern möglich.
Die Reihenfolge, in der im Triggerrumpf die auslösenden Ereignisse angegeben werden,
ist beliebig:
... after insert or update
... after delete or insert
... after update or delete or insert
Ein Ereignis darf nicht mehrfach angegeben werden.
Der Triggerrumpf muß ein PL/SQL-Programm enthalten, wie es auch in Prozeduren vorkommen kann. Als einzige Abweichung sind die Konstrukte
42
KAPITEL 5. BENUTZERHANDBUCH
43
if inserting
if deleting
if updating
erlaubt, um eine Verzweigung in Abhängigkeit vom auslösenden Ereignis zu ermöglichen.
Eine Kombination wie
if inserting or updating
ist jedoch nicht gestattet.
Es ist bei der Triggererstellung darauf zu achten, daß keine Endlos-Zyklen entstehen, da
diese nicht erkannt werden. Weiterhin ist die Reihenfolge der Trigger bzw. die Priorität
zu beachten, damit auch der gewünschte Effekt erzielt wird. Das Vertauschen der Reihenfolge zweier Trigger kann sonst völlig unterschiedliche Ergebnisse erzielen. Die beiden
folgenden Trigger bilden dafür ein Beispiel:
Der Trigger pres gehalt bewirkt, daß das Gehalt eines neuen Präsidenten mindesten
doppelt so hoch ist wie das seiner Manager.
Der Trigger manager gibt jedem neuen Manager eine zwanzigprozentige Gehaltserhöhung.
create deferred trigger pres_gehalt
after insert or update on emp
declare
gehalt number(7,2);
mgr_gehalt number(7,2);
begin
if inserting
then
/* Gehalt des Chefs lesen */
select sal into gehalt from i_pres_gehalt
where job = ’PRESIDENT’;
/* maximales Managergehalt lesen */
select max(sal) into mgr_gehalt from emp
where job = ’MANAGER’;
/* Falls Chefgehalt zu gering: anheben ! */
if gehalt is not null and gehalt < 2*mgr_gehalt
then update emp
set sal = 2*mgr_gehalt
where job = ’PRESIDENT’;
end if;
end if;
KAPITEL 5. BENUTZERHANDBUCH
if updating
then
/* Neues Chefgehalt lesen */
select new_sal into gehalt from u_pres_gehalt
where new_job = ’PRESIDENT’;
/* maximales Managergehalt lesen */
select max(sal) into mgr_gehalt from emp
where job = ’MANAGER’;
/* Chefgehalt anheben, falls niedriger */
if gehalt is not null and gehalt < 2*mgr_gehalt
then update emp
set sal = 2*mgr_gehalt
where job = ’PRESIDENT’;
end if;
end if;
end;
create deferred trigger manager
after update on emp
declare
/* liest Angestelltennummer neuer Manager */
cursor upd_mgr_cur is
select new_empno from u_manager
where new_job = ’MANAGER’
and old_job != ’MANAGER’;
upd number;
begin
open upd_mgr_cur;
loop
fetch upd_mgr_cur into upd;
exit when upd_mgr_cur%notfound;
/* Setze Gehalt neuer Manager um 20% herauf */
update emp
set sal = sal*1.20
where empno = upd;
end loop;
close upd_mgr_cur;
end;
44
KAPITEL 5. BENUTZERHANDBUCH
45
Der Anfangszustand sehe wie folgt aus:
EMPNO ENAME
JOB
MGR
SAL
DEPTNO
---------- ---------- --------- ---------- --------- ---------7839
7499
7698
7782
KING
ALLEN
BLAKE
CLARK
PRESIDENT
SALESMAN
MANAGER
MANAGER
7698
7839
7839
5000
2500
2085
2450
20
30
30
20
Die DB-Operationen seien die folgenden:
update emp set job=’MANAGER’ where ename=’ALLEN’;
update emp set sal=5300 where ename=’KING’;
exec my_commit
Hat der Trigger pres gehalt die höhere Priorität, so sieht der Endzustand nach Abarbeitung beider Trigger so aus:
EMPNO ENAME
JOB
MGR
SAL
DEPTNO
---------- ---------- --------- ---------- --------- ---------7839
7499
7698
7782
KING
ALLEN
BLAKE
CLARK
PRESIDENT
MANAGER
MANAGER
MANAGER
7698
7839
7839
5300
3000
2085
2450
20
30
30
20
Offenbar verdient der Präsident nun nicht doppelt so viel wie Allen. Wenn hingegen der
Trigger manager die höhere Priorität erhält, sieht das Ergebnis anders aus:
EMPNO ENAME
JOB
MGR
SAL
DEPTNO
---------- ---------- --------- ---------- --------- ---------7839
7499
7698
7782
KING
ALLEN
BLAKE
CLARK
PRESIDENT
MANAGER
MANAGER
MANAGER
7698
7839
7839
6000
3000
2085
2450
20
30
30
20
KAPITEL 5. BENUTZERHANDBUCH
5.2
46
Programmbedienung
Das Programm, das die nötigen Tabellen, Trigger und Prozeduren zu den verzögerten Trigger erstellt, heißt Trigger. Nach dem Programmstart sind Benutzername und Passwort
einzugeben, damit das Programm eine Verbindung zur Oracle7-Datenbank unter der Kennung des Benutzers aufbauen kann. Diese Verbindung ist nötig, um die Tabellen, Trigger
und Prozeduren erstellen zu können.
Sobald die Kommunikation aufgebaut ist, wird die Eingabe des Namens der Datei verlangt, in der die verzögerten Trigger stehen. Als nächstes werden die Triggerköpfe analysiert. Zuerst wird eine Datei drop all.sql erzeugt, die die nötigen SQL-Befehle enthält,
um die von Trigger erzeugten Tabellen, Trigger und Prozeduren löschen. Wenn die
verzögerten Trigger gelöscht oder verändert werden sollen, muß man diese Befehle eingeben oder die Datei mit @drop all von SQLPLUS aus einladen.
Dies ist auch nötig, wenn sich ein Fehler in einen verzögerten Trigger eingeschlichen hat
oder wenn noch verzögerte Trigger mit gleichem Namen existieren.
Soll das Programm abgebrochen werden, wenn nach dem Dateinamen gefragt wird, so ist
das Ausrufezeichen ! einzugeben.
Trigger erzeugt pro verzögerten Trigger <trig name> die folgenden Tabellen:
i_<trig_name>
d_<trig_name>
u_<trig_name>
hilf_i_<trig_name>
hilf_d_<trig_name>
hilf_u_<trig_name>
und die Prozedur <trig name>.
Weiterhin werden die Prozedur my commit und pro Tabelle <table name>, auf der ein
verzögerter Trigger existiert, die (after insert or delete or update) Datensatz-Trigger
<table name> def trigger erzeugt.
Wenn das Programm fehlerfrei beendet wurde, sind die verzögerten Trigger aktiviert. Die
Transaktion, die die Trigger überwachen sollen, kann nun durchgeführt werden. Das Transaktionsende muß dann durch den Aufruf der Prozedur my commit gekennzeichnet werden.
Unter SQLPLUS geschieht dies durch den Befehl exec my commit. Nach der Triggerabarbeitung meldet sich SQLPLUS mit der Meldung procedure successfully completed
zurück. Jetzt können entweder eine neue Transaktion begonnen oder die verzögerten Trigger entfernt werden.
KAPITEL 5. BENUTZERHANDBUCH
47
Sollte es einmal nötig sein, den Quellcode von Trigger neu zu compilieren, so kann das mit
dem Makefile prok.mk geschehen. Der Präprozessor benötigt dazu aus dem Makefile einen
Benutzernamen und das dazugehörige Passwort. Die Verwendung von SCOTT/TIGER
reicht dabei völlig. Leider ist das Makefile nicht optimal, da ein extrem großes ausführbares Programm erstellt wird. Hier wäre eine Verbesserung angebracht, die aber eine gute
Dokumentation von Pro*C voraussetzt.
Der UNIX-Befehl zum Compilieren aller Module und zum anschließenden Zusammenlinken lautet:
make -f proc main and oracle
Kapitel 6
Erweiterungsmöglichkeiten
Die Einschränkungen wurden bereits in den vorangegangenen Kapiteln beschrieben. Sie
gilt es in der Zukunft auszumerzen – vielleicht auch, wenn Oracle7 bessere Zugriffsmöglichkeiten auf das Data Dictionary gestattet oder die vorhandenen besser dokumentiert.
Das wohl größte Manko ist der hohe Speicherplatzbedarf, bedingt durch die sechs Tabellen,
die für jeden Trigger angelegt werden, und die damit verbundene Redundanz. Eine schöne
Weiterentwicklung wäre ein Tool, das dem Anwender das Entwerfen der verzögerten Trigger erleichtert. Um die Syntax und Variablen- bzw. Cursor-Deklarationen bräuchte sich
der Benutzer dann nicht mehr zu kümmern, er gibt nur die auslösenden Ereignisse, die
Bedingung und die Aktion an, und um das Zusammenfügen zu einem verzögerten Trigger kümmert sich das Tool. Dann sollte man auch die in Kapitel 3.3.1 vorgeschlagenen
Methoden implementieren, die die Trigger mit weniger Tabellen und weniger Redundanz
realisieren.
Weiterhin kann es wünschenswert sein, wenn ein verzögerter Trigger auch auf mehreren
Tabellen erstellt werden könnte. Bei der Erstellung von Triggern auf fremden Tabellen
stellt sich zur Zeit noch das Problem des Zugriffs auf die Spaltennamen und -typen der
fremden Tabellen.
48
Literaturverzeichnis
[Ceri and Widom, 1990] Ceri, S. and Widom, J. (1990). Deriving production rules for
constraint maintenance. In McLeod, D., Sacks-Davis, R., and Schek, H., editors, Proceedings of the 16th Int. Conf. on Very Large Data Bases - 1990, pages 566–577. Morgan
Kaufmann Publishers.
[Gehani and Jagadish, 1991] Gehani, N. and Jagadish, H. (1991). Ode as an active database: Constraints and triggers. In Lohmann, G. M., Sernadas, A., and Camps, R.,
editors, Proceedings of the 17th Int. Conf. on Very Large Data Bases - 1991, pages
327–336. Morgan Kaufmann Publishers.
[Gertz and Lipeck, 1993] Gertz, M. and Lipeck, U. W. (1993). Deriving integrity maintaining triggers from transition graphs. In Proceedings Ninth International Conference
on Data Engineering, pages 22–29. IEEE Computer Society Press.
[Hanson and Widom, 1992] Hanson, E. N. and Widom, J. (1992). An overview of production rules in database systems. Technical Report CIS-TR-92-031, University of
Florida.
[Illik, 1990] Illik, J. A. (1990). Programmieren in C unter UNIX. Sybex, Düsseldorf.
[O7-AppDev] Oracle7: Application Developer’s Guide. Part No. 6695-70-1292, 1992.
[O7-PL/SQL] Oracle7: PL/SQL User’s Guide and Reference. Part No. 800-20-1292, 1992.
[O7-Pro*C] Oracle7: Pro*C Supplement to the Oracle Precompilers Guide. Part No.
5452-15-1292, 1992.
[Widom, 1992] Widom, J. (1992). Starburst rule system user’s guide. Technical report,
IBM Almaden Research Center, San Jose, CA.
[Widom, 1993] Widom, J. (1993). The starburst active database rule system. Technical
report, Department of Computer Science, Stanford, CA.
49