11. Integrität und Trigger

11. Integrität und Trigger
Grundlagen und Begriffe
■
Grundlagen und Begriffe
Integritätsbedingung (IB) = Bedingung für die „Zulässigkeit“
(„Korrektheit“) betreffend
■
Integritätsbedingungen
■
Integritätsregeln in SQL
■
(einzelne) Datenbankzustände σ ,
■
Trigger
■
Zustandsübergänge hσalt , σneu i oder auch
■
langfristige Datenbankentwicklungen
Bezeichnungen:
Bedingungsklasse
zeitlicher Kontext
statisch
Datenbankzustand
dynamisch transitional Zustandsübergang
temporal
Zustandsfolge
VL Datenbanken I – 11–1
Architekturen zur Integritätssicherung
Anwendung 1
VL Datenbanken I – 11–2
Integritätssicherung durch Anwendung
Anwendung 1
Routinen
Anwendung n
Anwendung n
Routinen
DBMS
DBMS
Datenbank
Datenbank
Vereinfachte Datenbank-Anwendungsarchitektur
VL Datenbanken I – 11–3
VL Datenbanken I – 11–4
Integritätsmonitor
Einkapselung
Anwendung 1
Anwendung 1
Anwendung n
Anwendung n
Kapselung
DBMS
Monitor
DBMS
Datenbank
Datenbank
VL Datenbanken I – 11–5
Inhärente Integritätsbedingungen im RM
1. Typintegrität:
■ SQL erlaubt Angabe von Wertebereichen zu
Attributen
■ Erlauben oder Verbieten von Nullwerten
2. Schlüsselintegrität:
■ Angabe eines Schlüssels für eine Relation
3. Referentielle Integrität:
■ die Angabe von Fremdschlüsseln
VL Datenbanken I – 11–7
VL Datenbanken I – 11–6
Integritätsbedingungen in SQL-DDL
■
not null: Nullwerte verboten
■
default: Angabe von Default-Werten
■
check ( search-condition ): Attributspezifische
Bedingung
(in der Regel Ein-Tupel-Integritätsbedingung)
■
primary key: Angabe eines Primärschlüssel
■
foreign key ( Attribut(e))
references Tabelle( Attribut(e) ):
Angabe der referentiellen Integrität
VL Datenbanken I – 11–8
Erhaltung der referentiellen Integrität
■
Überprüfung der Fremdschlüsselbedingungen nach
Datenbankänderungen
■
für πA (r1 ) ⊆ πK (r2 ),
z.B. πVerlagsname (Bücher) ⊆ πVerlagsname (Verlage)
◆ Tupel t wird eingefügt in r1 ⇒ überprüfen, ob t0 ∈ r2
existiert mit: t0 (K) = t(A), d.h. t(A) ∈ πK (r2 )
falls nicht ⇒ abweisen
◆ Tupel t0 wird aus r2 gelöscht ⇒ überprüfen, ob
σA=t0 (K) (r1 ) = {}, d.h. kein Tupel aus r1 referenziert t0
falls nicht leer ⇒ abweisen oder Tupel aus r1 , die t0
referenzieren, löschen (bei kaskadierendem Löschen)
Überprüfungsmodi von Bedingungen
■
on update | delete
Angabe eines Auslöseereignisses, das die Überprüfung
der Bedingung anstößt
■
cascade | set null | set default | no action
Kaskadierung: Behandlung einiger
Integritätsverletzungen pflanzt sich über mehrere Stufen
fort, z.B. Löschen als Reaktion auf Verletzung der
referentieller Integrität
■
deferred | immediate legt Überprüfungszeitpunkt für
eine Bedingung fest
◆ deferred: Zurückstellen an das Ende der Transaktion
◆ immediate: sofortige Prüfung bei jeder relevanten
Datenbankänderung
VL Datenbanken I – 11–9
VL Datenbanken I – 11–10
Die assertion-Klausel
Überprüfungsmodi: Beispiel
Kaskadierendes Löschen
create table Bücher (
ISBN char(10) not null,
Titel varchar(200),
Verlagsname varchar(30),
primary key (ISBN),
foreign key (Verlagsname)
references Verlage (Verlagsname)
on delete cascade
)
VL Datenbanken I – 11–11
■
Assertion: Prädikat, das eine Bedingung ausdrückt, die
von der Datenbank immer erfüllt sein muß
■
Syntax (SQL:1999)
create assertion name check ( prädikat )
■
Beispiele:
create assertion Preise check
( ( select sum (Preis)
from Buch) < 10000 )
create assertion Preise2 check
( not exists (
select Preis from Buch ) > 200 )
VL Datenbanken I – 11–12
Beispiele für Integritätsbedingungen
Beispiele für Integritätsbedingungen (II)
1. Das Konto von Weiß darf nicht überzogen werden.
6. Der Brotpreis darf nicht erhöht werden.
2. Kein Kundenkonto darf unter -100 absinken.
7. Kunden dürfen nur gelöscht werden, wenn sie keine
Waren mehr bestellt haben.
3. Kunden sind durch ihre Namen identifiziert, d.h., kein
Kundenname darf mehrfach vorkommen.
4. Der Durchschnittspreis für Karotten muß unter dem für
Spargel liegen.
5. Nur solche Waren dürfen bestellt werden, für die es
mindestens einen Lieferanten gibt.
8. Der Preis eines Grundnahrungsmittels darf innerhalb
von drei Jahren höchstens um 15% steigen.
9. Kunden müssen gelöscht werden, wenn sie keine
Waren mehr bestellt haben.
VL Datenbanken I – 11–13
Mögliche Klassifikationen
Kriterium
VL Datenbanken I – 11–14
Mögliche Klassifikationen (II)
Klassen
Kriterium
Granularität
Attribut
Tupel
Relation
Datenbank
Ausdrucksfähigkeit elementare Prädikate
Relationale Algebra
SQL
SQL + transitive Hülle
berechnungsvollständig
Klassen
zeitlicher Kontext
statisch
transitional
temporal
Überprüfungszeitpunkt Einzeländerung
Operationsende
Transaktionsende
Reaktion
Zurückweisung (reject)
Korrektur (repair)
VL Datenbanken I – 11–15
VL Datenbanken I – 11–16
Integritätsregeln
Integritätsregeln (II)
■
IR = hB, O, A, Ri
B
O
A
R
Integritätsbedingung
Menge von Datenbankobjekten, auf
die sich B bezieht
Auslöser, wann B zu überprüfen ist
Reaktionen, falls B verletzt ist
im usprünglichen SQL-Sprachvorschlag
assert <Regelname>
[immediate | deferred] [ on <Op> ]
[for <Rel>] :
<Bedingung wie in where-Klausel>
[else ( <Folge von SQL-Anweisungen> )]
wobei
(A)
(O)
(O, B )
(R)
◆ <Op> ::= { insertion | deletion } of <Rel> |
update of <Rel> [ (<Attribut>)]
◆ <Rel> ::= <Relationsname> [ <Variablenname> ]
VL Datenbanken I – 11–17
Integritätsregeln in SQL: Beispiel I
VL Datenbanken I – 11–18
Integritätsregeln in SQL: Beispiel II
Kunden sind durch ihre Namen eindeutig identifiziert:
Kein Kundenkonto darf unter -100 absinken:
assert IR1
for KUNDE K1, KUNDE K2:
not( K1.KName = K2.KName ) or
(K1.KAdr = K2.KAdr and K1.Kto = K2.Kto)
assert IR2 for KUNDE : Kto >= -100
bzw. inklusive Angabe von Operationen:
assert IR2’
on insertion of KUNDE, update of KUNDE
for KUNDE : Kto >= -100
VL Datenbanken I – 11–19
VL Datenbanken I – 11–20
Integritätsregeln in SQL: Beispiel III
Integritätsregeln in SQL: Beispiel IV
Das Konto von Weiß darf nicht überzogen werden:
Der Durchschnittspreis für Karotten muß unter dem für
Spargel liegen:
assert IR3 for KUNDE :
not KName = ’Weiß’ or Kto >= 0
assert IR4 deferred
( select avg (Preis) from LIEFERANT
where Ware = ’Karotten’)
<= (select avg (Preis) from LIEFERANT
where Ware = ’Spargel’)
VL Datenbanken I – 11–21
VL Datenbanken I – 11–22
Integritätsregeln in SQL: Beispiel V
Integritätsregeln in SQL: Beispiel VI
Nur solche Waren dürfen bestellt werden, für die es
mindestens einen Lieferanten gibt:
Der Brotpreis darf nicht erhöht werden:
assert IR6 (on update of LIEFERANT (Preis))
for LIEFERANT:
not Ware = ’Brot’ or
new Preis <= old Preis
assert IR5 for AUFTRAG A:
exists (select * from LIEFERANT
where Ware = A.Ware)
VL Datenbanken I – 11–23
VL Datenbanken I – 11–24
Integritätsregeln in SQL: Beispiel VII
Integritätsregeln in SQL: Beispiel VIII
Kunden dürfen nur gelöscht werden, wenn sie keine Waren
mehr bestellt haben:
Der Preis eines Grundnahrungsmittels darf innerhalb von
drei Jahren höchstens um 15% steigen.
assert IR7 deferred on deletion of KUNDE
for KUNDE K:
not exists (select * from AUFTRAG
where KName = old K.KName)
⇒ kann in diesem Sprachvorschlag nicht ausgedrückt
werden!
VL Datenbanken I – 11–25
Integritätsregeln in SQL: Beispiel IX
Kunden müssen gelöscht werden, wenn sie keine Waren
mehr bestellt haben:
assert IR9 on deletion of AUFTRAG
for AUFTRAG A1:
exists (select * from AUFTRAG A2
where A2.KName = old A1.KName)
else (
delete KUNDE K
where K.KName = old A1.KName );
VL Datenbanken I – 11–27
VL Datenbanken I – 11–26
Trigger
■
Trigger: Anweisung/Prozedur, die bei Eintreten eines
bestimmten Ereignisses automatisch vom DBMS
ausgeführt wird
■
Anwendung:
◆ Erzwingen von Integritätsbedingungen
(„Implementierung“ von Integritätsregeln)
◆ Auditing von DB-Aktionen
◆
Propagierung von DB-Änderungen
VL Datenbanken I – 11–28
Trigger (II)
Trigger: Entwurf und Implementierung
create trigger ...
after <Operation>
<Anweisungen>
entspricht einer speziellen Integritätsregel:
assert ...on <Operation> :
false else ( <Anweisungen> )
■
Spezifikation von
◆ Ereignis und Bedingung für Aktivierung des Triggers
◆ Aktion(en) zur Ausführung
■
Syntax in SQL:1999 festgelegt
■
verfügbar in den meisten kommerziellen Systemen (aber
mit anderer Syntax)
VL Datenbanken I – 11–29
SQL:1999-Trigger
■
■
VL Datenbanken I – 11–30
Weitere Angaben bei Triggern
Syntax:
create trigger <Name: >
after | before <Ereignis>
on <Relation>
[ when <Bedingung> ]
begin atomic < SQL-Anweisungen > end
Ereignis:
◆ insert
◆ update [ of <Liste von Attributen> ]
◆ delete
VL Datenbanken I – 11–31
■
for each row bzw. for each statement: Aktivierung des
Triggers für jede Einzeländerungen einer
mengenwertigen Änderung oder nur einmal für die
gesamte Änderung
■
before bzw. after: Aktivierung vor oder nach der
Änderung
■
referencing new as bzw. referencing old as: Binden
einer Tupelvariable an die neu eingefügten bzw. gerade
gelöschten („alten’“) Tupel einer Relation
; Tupel der Differenzrelationen
VL Datenbanken I – 11–32
Beispiel für Trigger (I)
Beispiel für Trigger (II)
Kein Kundenkonto darf unter -100 absinken:
Nur solche Waren dürfen bestellt werden, für die es
mindestens einen Lieferanten gibt:
create trigger bad_account
after update of Kto on KUNDE
referencing new as INSERTED
when (exists
(select * from INSERTED where Kto < -100)
)
begin atomic
rollback;
end
create trigger bad_order
after insert on AUFTRAG
referencing new as INSERTED
when (exists (
select * from INSERTED I
where not exists (
select * from LIEFERANT
where Ware = I.Ware))
begin atomic
rollback;
end
; ähnlicher Trigger für insert
VL Datenbanken I – 11–33
Beispiel für Trigger (III)
VL Datenbanken I – 11–34
Methoden der Integritätssicherung
Kunden müssen gelöscht werden, wenn sie keine Waren
mehr bestellt haben:
create trigger bad_customer
delete on AUFTRAG
referencing old as O
for each row
when (not exists
(select * from AUFTRAG A
where A.KName = O.KName))
begin atomic
delete from KUNDE where KName = O.KName;
end
VL Datenbanken I – 11–35
■
■
Integritätssicherung durch Änderungstransformationen
◆
transformiert Änderungsanweisungen in einer
Hochsprache wie SQL so, daß das Ergebnis der
Transformation integritätsrespektierend ist
◆
Integritätsbedingungen werden in Änderungen
„hineinkompiliert“
Generierung von Triggern bzw. ECA-Regeln aus
deskriptiven Integritätsbedingungen
VL Datenbanken I – 11–36
Integritätssicherung durch Trigger
Integritätssicherung durch Trigger (II)
1. Bestimme Objekt oi , für das die Bedingung φ überwacht
werden soll
■ i.d.R. mehrere oi betrachten, wenn Bedingung
relationsübergreifend ist
■ Kandidaten für oi sind Tupel der Relationsnamen, die
in φ auftauchen
2. Bestimme die elementaren Datenbankänderungen u ij
auf Objekten oi , die φ verletzen können
■ Regeln: z.B. Existenzforderungen beim Löschen und
Ändern prüfen, jedoch nicht beim Einfügen etc.
4. Bestimme je nach Anwendung die Reaktion ri auf
Integritätsverletzung
■ Rücksetzen der Transaktion (rollback)
■ korrigierende Datenbankänderungen
5. Formuliere folgende Trigger:
create trigger t-phi-ij after uij on oi
when ¬φ
begin ri end
6. Wenn möglich, vereinfache entstandenen Trigger
VL Datenbanken I – 11–37
Trigger in Oracle
VL Datenbanken I – 11–38
Trigger in Oracle (II)
■
Implementierung in PL/SQL
■
Notation
create [ or replace ] trigger trigger-name
before | after
insert or update [ of spalten ]
or delete on tabelle
[ for each row
[ when ( prädikat ) ] ]
PL/SQL-Block
VL Datenbanken I – 11–39
■
Arten:
◆ Anweisungsebene (statement level trigger ): Trigger
wird ausgelöst vor bzw. nach der DML-Anweisung
◆ Tupelebene (row level trigger ): Trigger wird vor bzw.
nach jeder einzelnen Modifikation ausgelöst (one
tuple at a time)
■
Trigger auf Tupelebene:
◆ Prädikat zur Einschränkung der Anwendung (when)
◆ Zugriff auf altes (:old.col) bzw. neues (:new.col)
Tupel
– für delete: nur (:old.col)
– für insert: nur (:new.col)
– in when-Klausel nur (new.col) bzw. (old.col)
VL Datenbanken I – 11–40
Trigger in Oracle (III)
Trigger in Oracle: Beispiel
■
Transaktionsabbruch durch
raise_application_error(code, message)
■
Unterscheidung der Art der DML-Anweisung
if deleting then ... end if;
if updating then ... end if;
if inserting then ... end if;
Kein Kundenkonto darf unter -100 absinken:
create or replace trigger bad_account
after insert or update of Kto on KUNDE
for each row
when (new.Kto < -100)
begin
raise_application_error(-20221,
’Nicht unter -10000’);
end;
VL Datenbanken I – 11–41
VL Datenbanken I – 11–42