6. Trigger

6. Trigger
Charakterisierung von Triggern
6. Trigger
• Trigger definieren automatische Reaktionen auf Ereignisse, die durch Datenmanupilationen 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 muß.
A Action: auszuführende Aktion
• Beispiele für Anwendungsszenarien von Triggern:
– Ünterstützung der Konsistenz: automatische Anpassung von Relationen
– Überprüfung komplexer Integritätsbedingungen
– Protokollierungen
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
287
6. 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
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
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
288
6. Trigger
Triggerdefinition
Einschränkung: before-Trigger dürfen keine direkten oder indirekten Änderungsoperationen enthalten
• Aktivierungsereignis: insert-, update- oder delete-Trigger
• 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.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
289
6. 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
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
290
6. Trigger
Triggerdefinition
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
referencing old as alt new as neu
for each row
when (alt.gehalt > neu.gehalt )
begin atomic
signal sqlstate ’75001’;
set message_text = ’Gehalt darf nicht fallen’;
end;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
291
6. Trigger
Triggerdefinition
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
referencing old as alt new as neu
for each row
when ( exists( select *
from mitarbeiter
where vorgesetzter = alt.id ))
begin atomic
update mitarbeiter
set vorgesetzter = neu.id
where vorgesetzter = alt.id;
end;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
292
6. Trigger
Triggerdefinition
Besonderheiten der Triggerdefinition in DB2:
• no cascade before statt before
Dient als Erinnerung, daß ein before-Trigger niemals einen anderen beforeTrigger aktiviert.
• before-Trigger müssen stets Zeilentrigger sein.
• before-Trigger können eine Zuweisungsanweisung enthalten, die zur Konditionierung der zu setzenden Werte dienen kann.
• for each {row | statement} mode db2sql
Repräsentiert den in DB2 implementierten Triggerausführungsmodus. Soll sicherstellen, daß existierende Anwendungen nicht von zukünftigen alternativen
Ausführungsmodi betroffen sind.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
293
6. 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. Überprüfe FK mit restrict (→ Fehler).
5. Überprüfe FK mit cascade, set null, set default (→ Fehler).
6. Überprüfe FK mit no action und allen übrigen Bedingungen (→ Fehler).
7. Führe after-Trigger aus (→ Fehler).
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
294
6. Trigger
Triggerdefinition
Beispiel einer Aktivierungsreihenfolge von Triggern:
Orginal SQL−Anweisung
Trigger 1, Statement 1
Trigger A
Trigger B
Trigger 1, Statement 2
Trigger 2
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
295
6. 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
NO CASCADE BEFORE INSERT ON ang
REFERENCING NEW AS neuezeile
FOR EACH ROW MODE DB2SQL
SET (gehalt, bonus) =
(SELECT gehalt, bonus
FROM anfangsgehalt
WHERE jobcode = neuezeile.jobcode);
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
296
6. Trigger
Beispiele
Konsistensbedingung auf Basis von Änderungen und automatische Anpassung:
CREATE TRIGGER ang_trig2
NO CASCADE BEFORE UPDATE OF gehalt ON ang
REFERENCING OLD AS altezeile NEW AS neuezeile
FOR EACH ROW MODE DB2SQL
WHEN ( neuezeile.gehalt > 1.5 * altezeile.gehalt )
SET neuezeile.gehalt = 1.5 * altezeile.gehalt;
Beliebig komplexe Integritätsbedingungen durch den Aufruf benutzerdefinierter Funktionen:
CREATE TRIGGER ang_trig3
NO CASCADE BEFORE DELETE ON ang
REFERENCING OLD AS altezeile
FOR EACH ROW MODE DB2SQL
WHEN ( wichtigkeit(altezeile.jobcode, altezeile.projekt) > 20 )
SIGNAL SQLSTATE ’70010’ ( ’Wir brauchen diese Person’ );
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
297
6. Trigger
Beispiele
After-Trigger
TEMPS
Ort Temp
Ort
MaxTemp
EXTREMWERTE
MaxDatum MinTemp
MinDatum
CREATE TRIGGER temps_trig1
AFTER update ON temps
REFERENCING NEW AS neuezeile
FOR EACH ROW MODE DB2SQL
WHEN ( neuezeile.temp > (SELECT maxtemp
FROM extremwerte
WHERE ort = neuezeile.ort)
OR (SELECT maxtemp
FROM extremwerte
WHERE ort = neuezeile.ort) IS NULL )
UPDATE extremwerte
SET maxtemp = neuezeile.temp,
maxdate = CURRENT DATE
WHERE ort = neuezeile.ort;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
298
6. Trigger
Beispiele
Und wenn in TEMPS neue Orte eingefügt werden?
CREATE TRIGGER temps_trig3
AFTER INSERT ON temps
REFERENCING NEW AS neuezeile
FOR EACH ROW MODE DB2SQL
INSERT INTO extremwerte (ort, maxtemp, maxdate, mintemp, mindate )
VALUES( neuezeile.ort, neuezeile.temp, CURRENT DATE,
neuezeile.temp, CURRENT DATE );
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
299