1. Integritätsbedingungen und Trigger

1. Integritätsbedingungen und Trigger
„ ACID und Datenkontrolle
– Synchronisation zur Ablaufintegrität
– Transaktionsablauf
„ Integritätsbedingungen
– Klassifikation: Statische vs. Dynamische Integritätsbedingungen, ...
– Integritätsbedingungen in SQL92
„ Integritätsregeln / Trigger
– Konzept
– CREATE TRIGGER
– Einsatzformen von Trigger
gg
DBS 2
SS08, © Prof. Dr. E. Rahm
1-1
ACID und Datenkontrolle
„
T
Transaktionskonzept
ki k
(ACID-Eigenschaften)
(ACID Ei
h f )
– im SQL-Standard: COMMIT WORK, ROLLBACK WORK, Beginn einer
Transaktion implizit
– Einhaltung der logischen DB-Konsistenz (Consistency)
– Verdeckung der Nebenläufigkeit (concurrency isolation)
– Verdeckung von (erwarteten) Fehlerfällen (->
( > Logging und Recovery)
„
Integritätskontrolle
– Semantische Integritätskontrolle: möglichst hohe Übereinstimmung von DB
DB-Inhalt
Inhalt
und Miniwelt (Datenqualität)
– nur 'sinnvolle' und 'zulässige' Änderungen der DB: bei COMMIT müssen alle
semantischen Integritätsbedingungen erfüllt sein (Transaktionskonsistenz)
– Einhaltung der physischen Integrität sowie der Ablaufintegrität (operationale
Integrität)
„
Zugriffskontrolle
– Maßnahmen zur Datensicherheit und zum Datenschutz
– Sichtkonzept
– Vergabe und Kontrolle von Zugriffsrechten
SS08, © Prof. Dr. E. Rahm
DBS 2
1-2
ACID und Datenkontrolle (2)
Art der Integrität
TransaktionsT
kti
eigenschaft
realisierende DBS-Komponente
Semantische
Integrität
C (Consistency,
Konsistenz)
(Semantische) Integritätskontrolle
Physische Integrität
D: Dauerhaftigkeit
A: Atomarität
Logging, Recovery
((+ korrekte Implementierung
p
g der DBOperationen)
Ablaufintegrität
I (Isolation)
Synchronisation (z. B. Sperrverwaltung)
SS08, © Prof. Dr. E. Rahm
DBS 2
1-3
Synchronisation
„ DBS
S
müssen Mehrbenutzerbetrieb
hb
b i b unterstützen
„ ohne Synchronisation
y
kommt es zu so genannten
g
Mehrbenutzer-Anomalien
–
–
–
–
Verlorengegangene Änderungen (lost updates)
Abh i k i von nicht
Abhängigkeiten
i h freigegeben
f i
b Änderungen
Ä d
(dirty
(di read,
d dirty
di overwrite)
i )
inkonsistente Analyse (non-repeatable read)
Phantom-Probleme
„ Anomalien
sind nur durch Änderungen verursacht
„ Synchronisation erfolgt automatisch durch das DBS,
DBS z.B.
zB
durch Setzen von Sperren vor Datenzugriff (Freigabe der
Sperren am Transaktionsende)
SS08, © Prof. Dr. E. Rahm
DBS 2
1-4
Verloren gegangene Änderung (Lost Update)
DB-Inhalt
Gehaltsänderung T1
SELECT GEHALT INTO :gehalt
FROM PERS
WHERE PNR = 2345
gehalt := gehalt + 2000;
(PNR GEHALT)
(PNR,
Gehaltsänderung T2
2345
29.000
SELECT GEHALT INTO :gehalt
FROM PERS
WHERE PNR = 2345
gehalt := gehalt + 1000;
UPDATE PERS
SET GEHALT = :gehalt
WHERE PNR = 2345
2345
UPDATE PERS
SET GEHALT = :gehalt
WHERE PNR = 2345
2345
Zeit
DBS 2
SS08, © Prof. Dr. E. Rahm
1-5
Die Transaktion als Schnittstelle zwischen
g p g
und DBS
Anwendungsprogramm
Aktionen auf Seite des AP
Sicherstellen der Rücksetzbarkeit
von Änderungsoperationen
BOT
•
•
•
Befehle
Op
i
•
•
•
EOT
Ausführen von DML-Befehlen
(Überprüfen der unverzögerten
Integritätsbedingungen)
Überprüfen
p
der verzögerten
g
Integritätsbedingungen
g
g g
C
2-PhasenC
Commit
it
(2PC)
Aktionen auf Seite des DBS
O
Phase 1
M
M
I
T
Phase 2
Sicherstellen der Wiederholbarkeit aller Änderungen
(Logging)
Freigabe der Betriebsmittel (Sperren)
Bestätigung über erfolgreiches Ende an das AP
Weiterarbeit im AP
SS08, © Prof. Dr. E. Rahm
DBS 2
1-6
(Semant.) Integritätskontrolle
„
Wahrung
W
h
der
d logischen
l i h DB-Konsistenz
DB K i
„ Überwachung von semantischen Integritätsbedingungen durch Anwendungen
oder durch DBS
„ DBS-basierte Integritätskontrolle
–g
größere Sicherheit
– vereinfachte Anwendungserstellung
– Unterstützung von interaktiven sowie programmierten DB-Änderungen
– leichtere
l i h
Änderbarkeit
Ä d b k i von Integritätsbedingungen
I
i ä b di
„ Integritätsbedingungen der Miniwelt sind explizit bekannt zu machen, um
automatische Überwachung zu ermöglichen
AP1
AP2
...
APn
interaktives
Update
AP1
AP2
...
APn
interaktives
Update
Integritätskontrolle
DBS
DBS
AP-Prüflogik
SS08, © Prof. Dr. E. Rahm
DBS 2
1-7
Klassifikation von Integritätsbedingungen
1.
M d lli hä
Modellinhärente
Integritätsbedingungen
I
i ä b di
(vs.
( Anwendungsspezifische
A
d
ifi h IB)
– Primärschlüsseleigenschaft
– referentielle Integrität für Fremdschlüssel
– Definitionsbereiche (Domains) für Attribute
2.
3.
Reichweite
Reichweite
Attribut
Beispiele
GEB-JAHR ist numerisch, 4-stellig
Satzausprägung
ABT.GEHALTSSUMME < ABT.JAHRESETAT
Satztyp
S
t t
mehrere
Satztypen
PNR iistt eindeutig
i d ti
ABT.GEHALTSSUMME ist Summe aller
Angestelltengehälter
Zeitpunkt der Überprüfbarkeit
– unverzögert (sofort bei Änderungsoperation)
– verzögert
er ögert (am Transaktionsende)
4.
Art der Überprüfbarkeit
– Zustandsbedingungen (statische Integritätsbedingungen)
– dynamische Integritätsbedingungen
SS08, © Prof. Dr. E. Rahm
DBS 2
1-8
Dynamische Integritätsbedingungen
„
„
Beziehen
B
i h sich
i h im
i Gegensatz
G
zu statischen
i h IB auff Änderungen
Ä d
selbst und damit auf mehrere Datenbankzustände
Z i Varianten
Zwei
V i t
– Übergangsbedingungen: Änderung von altem zu neuem DB-Zustand wird
g
eingeschränkt
– temporale Bedingungen: Änderungen in bestimmtem zeitlichen Fenster werden
eingeschränkt
„
Beispiele dynamischer Integritätsbedingungen
– Übergang von FAM-STAND von 'ledig' nach 'geschieden' ist unzulässig
– Gehalt darf nicht kleiner werden
– Gehalt darf innerhalb von 3 Jahren nicht um mehr als 25% wachsen
DBS 2
SS08, © Prof. Dr. E. Rahm
1-9
Integritätsbedingungen in SQL
„
Ei d i k i von Attributwerten
Eindeutigkeit
A ib
– UNIQUE bzw. PRIMARY KEY bei CREATE TABLE
– Satztypbedingungen
Bsp.:
„
CREATE TABLE PERS ...
PNR INT UNIQUE
Q
((bzw. PRIMARY KEY))
Fremdschlüsselbedingungen
– FOREIGN-KEY-Klausel
– Satztyp- bzw. satztypübergreifende Bedingung
„
Wertebereichsbeschränkungen von Attributen
–
–
–
–
CREATE DOMAIN
NOT NULL
DEFAULT
Attribut- und Satztyp-Bedingungen
Attribut
Satztyp Bedingungen
SS08, © Prof. Dr. E. Rahm
DBS 2
1-10
Integritätsbedingungen in SQL (2)
„
All
Allgemeine
i IIntegritätsbedingungen
i ä b di
– CHECK-Constraints bei CREATE TABLE
– allgemeine Assertions,
Assertions z.
z B.
B für satztypübergreifende Bedingungen
CHECK-Constraints bei CREATE TABLE
Anweisung CREATE ASSERTION
CREATE TABLE PERS ....
GEB-JAHR INT
CHECK (VALUE BETWEEN 1900 AND 2100)
CREATE TABLE ABT .....
(GEHALTSSUMME < JAHRESETAT)
)
CHECK (
CREATE ASSERTION A1
CHECK (NOT EXISTS
(SELECT * FROM ABT A
WHERE GEHALTSSUMME <>
(SELECT SUM (P.GEHALT) FROM PERS P
WHERE P.ANR = A.ANR)))
DEFERRED
„
Festlegung des Überprüfungszeitpunktes:
– IMMEDIATE: am Ende der Änderungsoperation (Default)
– DEFERRED: am Transaktionsende (COMMIT)
„
Unterstützung für dynamische Integritätsbedingungen durch
Trigger (ab SQL:1999)
SS08, © Prof. Dr. E. Rahm
DBS 2
1-11
Integritätsregeln
„
„
Standardreaktion
S
d d k i auff verletzte
l
Integritätsbedingung:
I
i ä b di
ROLLBACK
Integritätsregeln erlauben Spezifikation von Folgeaktionen, z. B.
um Einhaltung
Ei h lt
von Integritätsbedingungen
I t ität b di
zu erreichen
i h
– SQL92: deklarative Festlegung referentieller Folgeaktionen (CASCADE, SET
NULL,, ...))
– SQL99: Trigger
„
Trigger: Festlegung von Folgeaktionen für Änderungsoperationen
– INSERT
– UPDATE oder
– DELETE
„
„
Trigger wesentlicher Mechanismus von aktiven DBS
Verallgemeinerung durch sogenannte ECA
ECA-Regeln
Regeln (Event /
Condition / Action)
SS08, © Prof. Dr. E. Rahm
DBS 2
1-12
Integritätsregeln (2)
„
B i i l Wartung
Beispiel:
W
der
d referentiellen
f
i ll Integrität
I
iä
– Deklarativ
CREATE TABLE PERS
(PNR INT PRIMARY KEY,
ANR INT FOREIGN KEY
REFERENCES ABT
ON DELETE CASCADE
... ));
– Durch Trigger
CREATE TRIGGER MITARBEITERLÖSCHEN
Ö
BEFORE DELETE ON ABT
REFERENCING OLD AS A
DELETE FROM PERS P
WHERE P.ANR = A.ANR;
SS08, © Prof. Dr. E. Rahm
DBS 2
1-13
Trigger
„
„
ausführbares,
füh b
benanntes
b
DB-Objekt,
DB Obj k das
d implizit
i li i durch
d h bestimmte
b i
Ereignisse (“triggering event”) aufgerufen werden kann
Ti
Triggerspezifikation
ifik ti besteht
b t ht aus
–
–
–
–
„
auslösendem Ereignis (Event)
Ausführungszeitpunkt
g
p
optionaler Zusatzbedingung
Aktion(en)
zahlreiche Einsatzmöglichkeiten
– Überwachung
g nahezu aller Integritätsbedingungen,
g
g g , inkl. dynamischer
y
Integritätsbedingungen
– Validierung von Eingabedaten
– automatische Erzeugung von Werten für neu eingefügten Satz
– Wartung replizierter Datenbestände
– Protokollieren von Änderungsbefehlen (Audit Trail)
•••
SS08, © Prof. Dr. E. Rahm
DBS 2
1-14
Trigger (2)
„
SQL99 S
SQL99-Syntax
CREATE TRIGGER <trigger name>
{BEFORE|AFTER}{INSERT|DELETE|
{
|
}{
|
|
UPDATE [OF <column list>]}
ON <table name>
[ORDER <order value>]
[REFERENCING <old or new alias list>]
[FOR EACH {ROW|STATEMENT}]
[WHEN (<search condition>)]
<triggered SQL statement>
<old
<
ld or new alias>
li > ::=
OLD [AS]<old values correlation
NEW [AS]<new values correlation
OLD_TABLE [AS]<old values table
NEW_TABLE [AS]<new values table
name>|
name>|
alias>|
alias>
– Trigger-Events: INSERT, DELETE, UPDATE
– Zeitpunkt: BEFORE oder AFTER
– mehrere Trigger pro Event/Zeitpunkt
E ent/Zeitp nkt möglich (benutzerdefinierte
(ben t erdefinierte
Aktivierungsreihenfolge)
– Bedingung: beliebiges SQL-Prädikat (z. B. mit komplexen Subqueries)
– Aktion: beliebige SQL-Anweisung (z. B. auch neue prozedurale Anweisungen)
– Trigger-Bedingung und -Aktion können sich sowohl auf alte als auch neue
Tupelwerte der betroffenen Tupel beziehen
– Trigger-Ausführung für jedes betroffene Tupel einzeln (FOR EACH ROW) oder
nur einmal für auslösende Anweisung (FOR EACH STATEMENT)
SS08, © Prof. Dr. E. Rahm
DBS 2
1-15
Trigger-Beispiele
„
R li i
Realisierung
einer
i
dynamischen
d
i h Integritätsbedingung
I
i ä b di
CREATE TRIGGER GEHALTSTEST
AFTER UPDATE OF GEHALT ON PERS
REFERENCING OLD AS AltesGehalt,
NEW AS NeuesGehalt
WHEN (NeuesGehalt < AltesGehalt)
ROLLBACK;
„
Wartung
g einer materialisierten Sicht ARME_PROGRAMMIERER
CREATE TRIGGER AP-INSERT
AFTER INSERT ON PERS
FOR EACH ROW
REFERENCING NEW AS N
WHEN N.BERUF = „Programmierer“ AND N.GEHALT < 30000
INSERT INTO ARME_PROGRAMMIERER
VALUES (N.PNR, N.NAME, N.BERUF, …)
SS08, © Prof. Dr. E. Rahm
DBS 2
1-16
Probleme von Triggern
„
„
„
„
„
teilweise
il i prozedurale
d l Semantik
S
ik (Zeitpunkte,
(Z i
k Verwendung
V
d
alter/neuer Werte, Aktionsteil im Detail festzulegen)
Ti
Trigger
derzeit
d
it beschränkt
b h ä kt auff Änderungsoperationen
Ä d
ti
einer
i
Tabelle (UPDATE, INSERT, DELETE)
derzeit i.
i a.
a keine verzögerte Auswertung von Triggern
Gefahr zyklischer, nicht-terminierender Aktivierungen
K
Korrektheit
kth it des
d DB-/Trigger-Entwurfes
DB /T i
E t
f (Regelabhängigkeiten,
(R l bhä i k it
parallele Regelausführung, ...)
Dennoch sind Trigger
gg sehr mächtiges
g und wertvolles Konstrukt,,
auch zur DBS-internen Nutzung
DBS 2
SS08, © Prof. Dr. E. Rahm
1-17
Zusammenfassung
„
D
Datenkontrolle
k
ll
–
–
–
–
–
„
Semantische Integritätskontrolle
Synchronisation (Ablaufintegrität)
Logging und Recovery (Voraussetzung für physische Datenkonsistenz)
Zugriffskontrolle
ACID Kl
ACID-Klammer
Integritätsbedingungen
– Klassifikation gemäß 4 Kriterien
– Umfassende Unterstützung in SQL92
„
Trigger
gg
– automatische Reaktion bei DB-Änderungen (-> „aktive DBS“)
– zahlreiche Anwendungsmöglichkeiten: Integritätskontrolle, materialisierte Sichten,
...
SS08, © Prof. Dr. E. Rahm
DBS 2
1-18