5. Trigger

5. Trigger
Charakterisierung von Triggern
5. Trigger
• Trigger definieren automatische Reaktionen auf Ereignisse, die durch Datenmanipulationen auf einer Relation ausgelöst werden.
• Trigger stellen eine einfache Form von ECA-Regeln dar:
E Event: auslösendes Ereignis
C Condition: Eine Bedingung, die zum Ausführen der Regelaktion erfüllt sein muss
A Action: auszuführende Aktion
• Beispiele für Anwendungsszenarien von Triggern:
– Unterstützung der Konsistenz: automatische Anpassung von Relationen
– Überprüfung komplexer Integritätsbedingungen
– Protokollierungen
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
192
5. Trigger
Triggerdefinition
Triggerdefinition in SQL99
create trigger triggername
{before | after}
{insert | delete | update [of spaltenliste ]}
on relation
[referencing transitionsvariablen/-tabellen ]
[for each {row | statement}]
[when ( praedikat )]
triggerrumpf
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
193
5. Trigger
Triggerdefinition
Bestandteiler der Triggerdefinition
Ein Trigger besteht in SQL99 aus folgenden Komponenten:
• eindeutiger Name
• Aktivierungszeitpunkt: Man unterscheidet before- und after-Trigger.
before: Aktivierung vor dem auslösenden Ereignis
after: Aktivierung nach dem auslösenden Ereignis
Einschränkung: before-Trigger dürfen keine direkten oder indirekten Änderungsoperationen enthalten
• Aktivierungsereignis: insert-, update- oder delete-Trigger
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
194
5. Trigger
Triggerdefinition
• Transitionsvariablen/-tabellen: Name für Tupel/Tabelle vor bzw. nach Änderung
old
new
old
new
[row]
[row]
table
table
[as]
[as]
[as]
[as]
transitionsvariable
transitionsvariable
transitionstabelle
transitionstabelle
Einschränkungen:
– old nicht in insert-Triggern
– new nicht in delete-Triggern
Granularität:
Zeilentrigger: Aktivierung des Triggers erfolgt für alle betroffenen Tupel einer SQLAnweisung einzeln (for each row).
Anweisungstrigger: Aktivierung erfolgt pro Anweisung (for each statement).
In Anweisungstriggern können keine Transitionsvariablen verwendet werden.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
195
5. Trigger
Triggerdefinition
• Bedingung: Die Ausführung der Triggeraktion kann durch die Angabe einer whenKlausel an eine Bedingung geknüpft werden.
• Aktion: Der Triggerrumpf besteht aus einer einzelnen oder zusammengesetzten
SQL-Anweisung. Hier können auch prozedurale Erweiterungen von SQL genutzt
werden.
Nicht erlaubt sind: Transaktions- und Verbindungsanweisungen
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
196
5. Trigger
Triggerdefinition
Transitionale Integritätsbedingung
• Eine transitionale Integritätsbedingung ist eine Bedingung für den Zustandsübergang von einem konsistenten Zustand A in einen konsistenten Datenbankzustand
B.
• Solch eine Integritätsbedingung schränkt die möglichen Zustandsübergänge ein:
Darf es einen Übergang von A nach B geben?
• Die Konsistenz der Zustände A und B entspricht dabei einem statischen Konsistenzbegriff.
Beispiele:
• Gehälter dürfen nicht sinken.
• Eine Mieterhöhung darf nicht mehr als 10% betragen.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
197
5. Trigger
Triggerdefinition
Implementierung transitionaler Integritätsbedingungen
Before-Trigger sind prädestiniert für die Implementierung transitionaler Integritätsbedingungen.
Beispiel: Gehälter dürfen nicht sinken:
create trigger KeineGehaltsKuerzungen
before update of gehalt on Mitarbeiter
for each row
when (old.gehalt > new.gehalt )
begin
RAISE_APPLICATION_ERROR(-20001, ’Verletzung des Tarifvertrags’);
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
198
5. Trigger
Triggerdefinition
Dynamische temporale Integritätsbedingung
Dynamische temporale Integritätsbedingung beschreiben Einschränkungen an eine
Folge von konsistenten Zuständen.
• Folge von Datenbankzuständen A1, . . . , An
• statische Konsistenz: Die Zustände A1 bis An sind jeweils konsistent.
• transitionale Konsistenz: Die Zustandsübergänge Ai nach Ai+1 sind für alle 1 ≤
i < n konsistent.
• temporale Konsistenz: Die gesamte Folge A1, . . . , An der Zustände stellt eine
konsistente Zustandsfolge dar.
Beispiel:
• Eine Miete darf innerhalb von 5 Jahren nicht mehr als 20% steigen.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
199
5. Trigger
Triggerdefinition
Bemerkungen:
• Die Prüfung dynamische temporale Integritätsbedingungen kann durch BeforeTrigger erfolgen.
• Hierfür werden Versionierungskonzepte oder Archivtabellen benötigt.
• Eine automatische Archivierung (bei Änderungen) kann durch After-Trigger erfolgen.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
200
5. Trigger
Triggerdefinition
Ausführungsmodell für Integritätsbedingungen und Trigger
Ausführung einer SQL-Anweisung:
1. Bestimme die betroffenen Tupel.
2. Führe before-Trigger aus (→ Fehler).
3. Führe eigentliche SQL-Anweisung aus.
4. Prüfe foreign key contraints und führe damit verbundene Operationen aus (→ Fehler).
5. Prüfe sonstige Konsistenzbedingungen (→ Fehler).
6. Führe after-Trigger aus (→ Fehler).
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
201
5. Trigger
Triggerdefinition
Beispiel einer Aktivierungsreihenfolge von Triggern:
Orginal SQL−Anweisung
Trigger 1, Statement 1
Trigger A
Trigger B
Trigger 1, Statement 2
Trigger 2
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
202
5. Trigger
Beispiele
Before-Trigger
• mächtige Integritätsbedingungen
• Anpassung von zu setzenden Datenwerten
Flexibler Default-Mechanismus für Attributwerte: automatische Berechnung des Anfangsgehalts und des Bonus:
CREATE TRIGGER ang_trig1
BEFORE INSERT ON ang
FOR EACH ROW
SET (gehalt, bonus) =
(SELECT gehalt, bonus
FROM anfangsgehalt
WHERE jobcode = new.jobcode);
Konsistenzbedingung auf Basis von Änderungen und automatische Anpassung:
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
203
5. Trigger
Beispiele
CREATE TRIGGER ang_trig2
BEFORE UPDATE OF gehalt ON ang
FOR EACH ROW
WHEN ( new.gehalt > 1.5 * old.gehalt )
SET new.gehalt = 1.5 * old.gehalt;
Beliebig komplexe Integritätsbedingungen durch den Aufruf benutzerdefinierter Funktionen:
CREATE TRIGGER ang_trig3
BEFORE DELETE ON ang
FOR EACH ROW
WHEN ( wichtigkeit(old.jobcode, old.projekt) > 20 )
BEGIN
RAISE_APPLICATION_ERROR(-20002, ’Wir brauchen diesen Mitarbeiter!’ );
END;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
204
5. Trigger
Beispiele
After-Trigger
TEMPS
Ort Temp
Ort
MaxTemp
EXTREMWERTE
MaxDatum MinTemp
MinDatum
CREATE TRIGGER temps_trig1
AFTER update ON temps
FOR EACH ROW
WHEN ( new.temp > (SELECT maxtemp
FROM extremwerte
WHERE ort = new.ort) )
BEGIN
UPDATE extremwerte
SET maxtemp = new.temp,
maxdate = SYSDATE
WHERE ort = new.ort;
END;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
205
5. Trigger
Beispiele
Und wenn in TEMPS neue Orte eingefügt werden?
CREATE TRIGGER temps_trig3
AFTER INSERT ON temps
FOR EACH ROW
BEGIN
INSERT INTO extremwerte (ort, maxtemp, maxdate, mintemp, mindate )
VALUES( new.ort, new.temp, SYSDATE, new.temp, SYSDATE );
END;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
206
5. Trigger
Beispiele
Ein weiteres typisches Einsatzszenario ist die Wahrung von referentiellen Integritätsbedingungen:
Beispiel: Simulation von on update cascade bei Fremdschlüsseln:
create trigger OnUpdateVorgesetzterCascade
after update of id on Mitarbeiter
for each row
when ( exists( select *
from mitarbeiter
where vorgesetzter = old.id ))
begin
update mitarbeiter
set vorgesetzter = new.id
where vorgesetzter = old.id;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
207