s_tiesler - Friedrich-Schiller

Aktive Mechanismen in
heutigen relationalen DBMS
Friedrich-Schiller-Universität Jena
Lehrstuhl für Datenbanken und Informationssysteme
Sommersemester 2007
Seminar
Aktive Datenbanken
Stephan Tiesler
Aktive Mechanismen in heutigen relationalen DBMS
Aufgabenstellung

Vergleich von:
-
DB2 UDB
Oracle Database
MS SQL Server
PostgreSQL

Abwandlungen zur Norm(SQL99, SQL2003)
Inhalt

Motivation und Einführung

Das Konzept des Triggers

Die Umsetzung der Triggertechnologie

SQL-Norm und deren Umsetzung

Fazit
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
3
Motivation
Bei der Gestaltung von DB‘s viele theoretische Möglichkeiten,
aber praktisch viele Dinge nicht so einfach umsetzbar

Zulassungsüberprüfungen - Ausleiher max. 3 Bücher ausleihen

Zugangssicherung – keine Änderungen zw. 17.00 und 08:00

Protokollierung von Datenänderungen in einer DB

Externe Aktionen wie E-Mail-Benachrichtigung

Benachrichtigung bei Erreichen von bestimmten Schwellenwerten
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
4
Einführung

Wichtiger Trend – Übergang von passiven zu aktiven DBMS

dadurch Möglichkeit selbstständig auf bestimmte
Situationen zu reagieren

Solche Mechanismen werden durch ECA-Regeln
beschrieben

tatsächliche Umsetzung erfolgt durch sog. Trigger
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
5
Das Konzept des Triggers
Trigger-Definitionen:

Unter einem Trigger (Impuls) versteht man ein, eine
bestimmte Operation auslösendes Signal.

Sind Prozeduren, um Werteinträge zu berechnen, zu
verändern und zu löschen.

Eine gespeicherte Prozedur, die als Antwort auf ein
DML-Ereignis (Data Manipulation Language,
Datenbearbeitungssprache) oder DDL-Ereignis (Data
Definition Language, Datendefinitionssprache)
ausgeführt wird.
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
6
Das Konzept des Triggers

Trigger werden in heutigen DBMS synonym für den Begriff
einer ECA-Regel genutzt

Die drei Elemente Ereignis, Bedingung und Aktion müssen
also enthalten sein:

Ereignisse sind Einfüge-, Lösch- und Änderungsoperationen

Bedingung ist optional und kann über when-Klausel ähnlich des bekannten
where-Teils bei SFW spezifiziert werden. Wichtig hier die Aussage true bzw.
false

Aktion ist eine oder mehrere Anweisungen welche ausgeführt werden sollen;
wird meist als zusätzliche Routine vom eigentlichen Trigger aufgerufen
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
7
Das Konzept des Triggers
Syntax zur Generierung eines Triggers (SQL2003)
CREATE TRIGGER
trigger name
OR
BEFORE
DELETE
AFTER
INSERT
ON
tabelle
UPDATE
FOR EACH STATEMENT
FOR EACH ROW
triggered SQL statement
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
8
Das Konzept des Triggers

BEFORE bzw. AFTER ist fast beliebig mit DELETE,
INSERT und UPDATE kombinierbar (12 Kombinationen)
zB.: Problem der Zugangssicherung (in Oracle)
Create trigger Zugang
before insert or delete or update on Angest
when (user != ‚Admin‘)
and (to_char(sysdate,‘HH24:MI‘) not between ‚08:00‘ and ‚17:00‘)
begin
raise_application_error(-20000,‘Keine Arbeitszeit.‘);
end;
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
9
Das Konzept des Triggers

FOR EACH STATEMENT führt den Trigger nach der
Abarbeitung aller die Änderungsoperation betreffenden
Datensätze aus
 z.B. bei Berechnungen von Summen

FOR EACH ROW führt den Trigger nach jedem
geänderten Datensatz durch die Änderungsoperation aus;
wird meistens gebraucht
 für das Protokollierungsbeispiel notwendig
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
10
Das Konzept des Triggers


Zugriff innerhalb der Funktion auf alte und neue Daten
möglich
OLD/NEW – ROW/TABLE

Verwendung alter Inhalte
 zB. Löschungen im Protokollierungsbeispiel
 Laut SQL2003 nicht bei INSERT

Verwendung neuer Inhalte
 Zulassungsbeispiel
 Laut SQL2003 nicht bei DELETE
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
11
Das Konzept des Triggers
Beispiel zur Nutzung von for each row sowie neuer Inhalte
(DB2 UDB)
Create trigger Budget1
before update of Budget on Abteilung
referencing new as n
for each row
mode db2sql
when (0.5*n.Budget <
(select sum(Gehalt) from Angest
where Abteilung = n.AbtNr))
signal sqlstate ‚70002‘ (‚Budgetreduzierung unterschreitet Minimum.‘)
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
12
Das Konzept des Triggers
Weitere spezielle Triggerarten:

INSTEAD OF-Trigger


Multiple Trigger



sind eigentlich mehrere Trigger welche auf dem selben
Ereignis beruhen
hier oft wichtig in welcher Reihenfolge die Abarbeitung
DDL-Trigger




hierdurch Möglichkeit in Views Daten einzugeben oder zu
löschen
werden bei Ausführung von DDL-Anweisungen aktiviert
hier keine Tabellenzuordnung, sondern zB. DB
andere Auslöseereignisse notwendig
Rekursive Trigger

können sich selbst wieder aufrufen
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
13
Möglichkeiten zur Triggernutzung

Zugangssicherung:
- für beliebige n-m Beziehungen
- Tabellenzugang für best. Zeit
und best. Operationen

Protokollierung:
- für Übersicht / Rückverfolgung

Konsistenzsicherung:
- bei Änderung von Verweiselementen
- Stichwort: referentielle Integrität

Transitionale Bedingungen:
- durch OLD/NEW Möglichkeit auf
zB. Alter Preis < neuer Preis zu
reagieren

Externe Aufgabe:
- Mail, Druck, autom. Bestellung
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
14
Trigger in Oracle Database
Syntax zum Erstellen eines Triggers
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
15
Trigger in Oracle Database
CREATE [ OR REPLACE ] TRIGGER [ schema. ]trigger
{ BEFORE | AFTER | INSTEAD OF }
{ dml_event_clause
| { ddl_event [ OR ddl_event ]...
| database_event [ OR database_event ]...
}
ON { [ schema. ]SCHEMA
| DATABASE
}
}
[ WHEN (condition) ]
{ pl/sql_block | call_procedure_statement } ;
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
16
Trigger in Oracle Database
Spezifikationen eines DML-Triggers
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
17
Trigger in Oracle Database
Trigger ändern und löschen
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
18
Trigger in Oracle Database
 REPLACE zum überschreiben eines Triggers ohne zu löschen
 Syntax ist an SQL2003 angelehnt, mit vielen Erweiterungen
 bei multiplen Triggern Abarbeitung nach folg. Schema
1. BEFORE-STATEMENT; 2. BEFORE-ROW;
3. AFTER-ROW; 4. AFTER-STATEMENT
 der weitere Aufruf erfolgt zufällig
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
19
Trigger in MS SQL Server
CREATE TRIGGER trigger_name
ON { table | view }
{
{
{FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
sql_statement [ ...n ]
}
}
- Syntax unterscheidet sich stark von SQL2003-Norm
- hier nur verkürzte Darstellung; weiter Modifikationen möglich
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
20
Trigger in MS SQL Server

Triggername als identifier und eindeutig in DB

AFTER ist default

Implementierung der Trigger durch Transact-SQL aber auch
durch C# und Visual Basic 2005 möglich

Multiple Trigger können durch sp-settriggerorder-Prozedur eine
best. Ausführungsreihenfolge erhalten

Triggerausführungsreihenfolge kann über (first, last, none)
spezifiziert werden
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
21
Trigger in MS SQL Server

Rekursive Trigger direkt und indirekt möglich

verschachtelte Trigger bis auf 32 Stufen möglich

DDL-Trigger auf CREATE, ALTER, DROP, GRANT, REVOKE
und UPDATE STATISTICS möglich

DDL-Trigger-Granularität: All Server oder Database

Einschränkungen bei DDL-Triggern zB. bei ALTER -, CREATE -,
DROP -, LOAD DATABASE
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
22
Trigger in MS SQL Server
Weiterhin besteht die Möglichkeit Trigger zu ändern, zu löschen, zu
sperren und entsperren.
DROP TRIGGER schema_name.trigger_name [ ,...n ] [ ; ]
ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
DISABLE TRIGGER { [ schema . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
23
Trigger in DB2 UDB
Trigger erstellen:
CREATE TRIGGER <name>
<action>
ON <table_name>
REFERENCING <…>
FOR EACH ROW / FOR EACH STATEMENT
<triggered action>
<action>
- BEFORE, AFTER, INSTEAD OF
- INSERT, DELETE, UPDATE
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
24
Trigger in DB2 UDB
 neben SQL-Trigger auch External-Trigger möglich
 External-Trigger werden in einer höheren Programmiersprache
geschrieben und können SQL mit einbetten
 Multiple Trigger sind möglich
 die Syntax liegt sehr eng an der SQL2003-Norm
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
25
Trigger in PostgreSQL Version 8.2
 Syntax zum Erstellen eines Triggers
CREATE TRIGGER name
{ BEFORE | AFTER }
{ event [ OR ... ] }
ON table
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )
- Aufbau ist exakt an SQL2003 Syntax angelehnt
http://www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
26
Trigger in PostgreSQL Version 8.2
 Syntax zum Ändern eines Triggers
ALTER TRIGGER name
ON table
RENAME TO newname
- Geht über SQL2003 hinaus, dort nicht vorgesehen
- hier nur Möglichkeit den Namen zu ändern
 Syntax zum Löschen eines Triggers
DROP TRIGGER [ IF EXISTS ] name
ON table
[ CASCADE | RESTRICT ]
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
27
Trigger in PostgreSQL Version 8

entgegen dem SQL2003-Standard dürfen Trigger auf
unterschiedlichen Tabellen gleiche Namen besitzen

in multiplen Triggern wird in alphabetischer Reihenfolge
gefeuert

Über spezielle Variable TG-OP kann ermittelt werden durch
was ein Trigger ausgelöst wurde
 interessant bei Protokollierung
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
28
Vergleich der vier Systeme
DB2 UDB
ORACLE
MS SQL
SERVER
PostgreSQL
BEFORE-TRIGGER
AFTER-TRIGGER
INSERT-TRIGGER
UPDATE-TRIGGER
DELETE-TRIGGER
---
INSTEAD-OF-TRIGGER
Multiple-TRIGGER
---
DDL-TRIGGER
?
Rekursive-TRIGGER
ALTER-TRIGGER
DROP-TRIGGER
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
29
Vergleich der vier Systeme
Zugriffsmöglichkeiten im Vergleich
DB2 UDP
ORACLE
MS SQL
SERVER
PostgreSQL
--
--
--
--
--
--
--
--
UPDATE OLD-Werte
UPDATE NEW-Werte
INSERT OLD-Werte
INSERT NEW-Werte
DELETE OLD-Werte
DELETE NEW-Werte
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
30
SQL-Normen
- Trigger wurden seit SQL2 auch im SQL-Standard festgehalten
- seitdem wurden folgende Standards veröffentlicht
 1992
Der Standard SQL2 bzw. SQL-92 wird verabschiedet.
 1999
SQL3 bzw. SQL:1999 wird verabschiedet.
 2003
SQL:2003 wird als Nachfolger des SQL3 verabschiedet
http://de.wikipedia.org/wiki/SQL
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
31
SQL-Norm 1999
Es werden zwei statements für Trigger definiert:
- CREATE TRIGGER <trigger name> <trigger action time>
<trigger event> ON <table name> [ REFERENCING <old or
new values alias list>] <triggered action>
-DROP TRIGGER <trigger name>
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
32
SQL-Norm 2003
Es werden zwei statements für Trigger definiert:
- CREATE TRIGGER <trigger name> <trigger action time>
<trigger event> ON <table name> [ REFERENCING
<transition table or variable list>] <triggered action>
-DROP TRIGGER <trigger name>
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
33
SQL-Norm 2003

Es existieren keine gravierenden Unterschiede zwischen
der SQL99 und der SQL2003 Norm

Die Formatangaben werden von allen 4 Systemen erfüllt
 action time  BEFORE, AFTER
 event  INSERT, DELETE, UPDATE
 action  FOR EACH ROW/STATEMENT

FOR EACH STATEMENT ist Standardverhalten

es muss möglich sein, alte und neue Werte anzusteuern
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
34
Vergleich der Systeme mit SQL-Norm
SQL2003
DB2
UDB
ORACLE
MS SQL
SERVER
Postgre
-SQL
BEFORE
BEFORE-TRIGGER
AFTER
AFTER-TRIGGER
INSERT
INSERT-TRIGGER
DELETE
UPDATE-TRIGGER
UPDATE
ROW
DELETE-TRIGGER
STATEMENT
INSTEAD-OF-TRIGGER
Multiple-TRIGGER
DDL-TRIGGER
Rekursive-TRIGGER
ALTER-TRIGGER
---
---
?
---
---
?
?
---
DROP-TRIGGER
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
35
Vergleich der Systeme mit SQL-Norm
Zugriffsmöglichkeiten im Vergleich
SQL-2003
DB2 UDB
ORACLE
MS SQL
SERVER
PostgreSQL
--
--
--
--
--
--
--
--
--
--
UPDATE OLD-Werte
UPDATE NEW-Werte
INSERT OLD-Werte
INSERT NEW-Werte
DELETE OLD-Werte
DELETE NEW-Werte
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
36
Fazit

Der Funktionsumfang der 4 betrachteten System ist relativ gleich

die SQL-Normen werden im Funktionsumfang eingehalten

Aber: es wird sich nicht immer an Syntax gehalten

Jedes Produkt hat eigene Erweiterungen zur Norm

Jedes Programm setzt sich auch in bestimmten Bereichen von der
„Konkurrenz“ ab
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
37
Quellenverweise
 Kleines Lexikon der Informatik; Zilahi, Szabo; 1995 R.Oldenbourg Verlag
 Taschenbuch der Informatik; Werner u.a.; 1995 Fachbuchverlag Leipzig
 Relationale Datenbanksysteme – Eine praktische Einführung 3.Auflage; Kleinschmidt, Rank; 2005 Springer Verlag
 Fundamentels of Database Systems, Third Edition; Elmasri, Navathe; 2000 Addison Wesley
 SQL Server 2005 Eine umfassende Einführung; Petkovic; 2006 dpunkt.verlag
 Oracle8 effizient einsetzen; Christiansen, Höding, Rautenstrauch, Saake; 1998 Addison Wesley
 Understanding THE NEW SQL A COMPLETE GUIDE; Melton, Simon; 1995 Morgan Kaufmann Publishers
 Datenbanken: Konzepte und Sprache; Heuer, Saake; 2001 mitp
 Database Management Systems, Third Edition; Ramakrishnan, Gehrke; McGraw-Hill
 Trigger in DB2 Version 2; Jens Lufter; Studienarbeit von 1996
 Konsistenzerhaltende Techniken für generierbare Wissensbasen zum Entwurf eingebetteter Systeme; Sporer; 2006
Aktive Mechanismen in heutigen relationalen Datenbanken
-
Stephan Tiesler
38