Aktive Mechanismen in heutigen relationalen DBMS

1. Aktive Mechanismen in heutigen relationalen DBMS Friedrich-Schiller-Universität Jena Lehrstuhl für Datenbanken und Informationssysteme Sommersemester 2007 Seminar Aktive Datenbanken Stephan Tiesler

2. Aufgabenstellung • Vergleich von: - DB2 UDB • - Oracle Database • - MS SQL Server • - PostgreSQL • Abwandlungen zur Norm(SQL99, SQL2003) Aktive Mechanismen in heutigen relationalen DBMS

3. Motivation und Einführung • Das Konzept des Triggers • Die Umsetzung der Triggertechnologie • SQL-Norm und deren Umsetzung • Fazit Inhalt Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

4. 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 Motivation Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

5. 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 Einführung Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

6. 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. Das Konzept des Triggers Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

7. 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

8. Das Konzept des Triggers Syntax zur Generierung eines Triggers (SQL2003) CREATE TRIGGER trigger name OR BEFORE DELETE ON tabelle AFTER INSERT UPDATE FOR EACH STATEMENT FOR EACH ROW triggered SQL statement Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

9. 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 beforeinsert or delete or updateon 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

10. 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

11. 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

12. Das Konzept des Triggers 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.‘) Beispiel zur Nutzung von for each row sowie neuer Inhalte (DB2 UDB) Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

13. Das Konzept des Triggers Weitere spezielle Triggerarten: • INSTEAD OF-Trigger • hierdurch Möglichkeit in Views Daten einzugeben oder zu löschen • Multiple Trigger • sind eigentlich mehrere Trigger welche auf dem selben Ereignis beruhen • hier oft wichtig in welcher Reihenfolge die Abarbeitung • DDL-Trigger • 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

14. 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

15. Trigger in Oracle Database Syntax zum Erstellen eines Triggers Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

16. 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

17. Trigger in Oracle Database Spezifikationen eines DML-Triggers Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

18. Trigger in Oracle Database Trigger ändern und löschen Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

19. 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

20. 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

21. 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

22. 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

23. 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

24. 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

25. 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

26. 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

27. 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

28. 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

29. Vergleich der vier Systeme Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

30. Vergleich der vier Systeme Zugriffsmöglichkeiten im Vergleich Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

31. 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

32. 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

33. 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

34. 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

35. Vergleich der Systeme mit SQL-Norm Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

36. Vergleich der Systeme mit SQL-Norm Zugriffsmöglichkeiten im Vergleich Aktive Mechanismen in heutigen relationalen Datenbanken - Stephan Tiesler

37. 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

38. 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