vierseitig

6. Trigger
Charakterisierung von Triggern
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
• 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
6. Trigger
6. Trigger
287
Triggerdefinition
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
6. Trigger
289
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
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
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.
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;
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.
Beispiel: Gehälter dürfen nicht sinken:
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
6. Trigger
291
Triggerdefinition
Ein weiteres typisches Einsatzszenario ist die Wahrung von referentiellen Integrit ätsbedingungen:
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
6. Trigger
293
Triggerdefinition
Ausführungsmodell für Integritätsbedingungen und Trigger
Beispiel: Simulation von on update cascade bei Fremdschlüsseln:
Ausführung einer SQL-Anweisung:
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
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).
292
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
294
6. Trigger
Triggerdefinition
Beispiel einer Aktivierungsreihenfolge von Triggern:
6. Trigger
Beispiele
Konsistensbedingung auf Basis von Änderungen und automatische Anpassung:
Orginal SQL−Anweisung
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;
Trigger 1, Statement 1
Trigger A
Trigger B
Beliebig komplexe Integritätsbedingungen durch den Aufruf benutzerdefinierter Funktionen:
Trigger 1, Statement 2
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’ );
Trigger 2
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
6. Trigger
295
Beispiele
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
6. Trigger
Beispiele
Before-Trigger
After-Trigger
TEMPS
Ort Temp
• 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
297
296
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