DB12-Trigger - schmiedecke.info
Werbung
Von der passiven zur
aktiven Datenbank II
•
•
•
Trigger
Views
Prozerdurale SQL-Erweiterungen
Konzept der aktiven DB
• ECA-Prinzip:
– Event, Condition, Action
Spezifizierte DB-Zustände
Ereignis
ggf. automatische Aktion
• Ereigniskonzept:
– beliebig viele unabhängige ECA-Spezifikationen pro
Ereignis
(c) schmiedecke 06
DB11- Aktive DB
2
Aktionsspezifikation in SQL
• Constraints: Assertions, Checks
– deskriptiv
"Aktion" besteht im Zurückweisen einer Operation
– Bedingungen, unter denen DML-Operationen zurückgewiesen
werden
• Referentielle Aktionen
– operational
– Reparaturmaßnahmen bei Verletzung der referentiellen Integrität
• Trigger
– operational
– Mit DML-Operationen verknüpfte frei programmierbare Aktionen
(c) schmiedecke 06
DB11- Aktive DB
3
Integritätssicherung
•
•
•
•
DB-Aktionen sollten der Integritätssicherung dienen.
Es ist in der Regel nicht sinnvoll, die (gesamte) Geschäftslogik durch DBAktionen zu implementieren!
DB als gemeinsamer Datenbestand verschiedener Anwendungen mit
unterschiedlicher Geschäftslogik – Integrität als gemeinsame Eigenschaft.
Evtl. Subsystem zur Implementierung gemeinsamer Geschäftsregeln
mehrerer Anwendungen.
Anwendung2
Anwendung1
Anwendung3
Subsystem
Anwendung4
DB
(c) schmiedecke 06
DB11- Aktive DB
4
Trigger
• Aktion aufgrund der Zustandsänderung einer Tabelle
- lokal angesiedelt
- ausgelöst durch das DBMS
- nicht beschrieben, sondern "programmiert"
• Wann?
- Aufgrund von UPDATE, INSERT oder DELETE
- Vor oder nach der Änderung (BEFORE / AFTER)
• Wie?
- einmalig
- oder zeilenweise (FOR EACH ROW)
- dann Zugriff auf alte und neue Werte:
REFERENCING OLD AS alt NEW AS neu
• Was?
- Frei programmierbare SQL-Anweisung
- Frei programmierbare Anweisungen in der prozeduralen SQL-Erweiterung
(SQL-1999: SQL/PSM
Oracle: PL/SQL oder SQLJ, MySQL: T-SQL)
(c) schmiedecke 06
DB11- Aktive DB
5
Trigger-Syntax
(SQL-1999, auch Oracle)
CREATE [OR REPLACE] TRIGGER trigger-name
{ BEFORE | AFTER } trigger-event
[ OF spalten-name
ON tab-name
[ FOR EACH ROW [ WHEN trigger-condition ]
DECLARE var-decls
BEGIN
trigger-body
END trigger-name
ALTER TRIGGER trigger-name { ENABLE | DISABLE };
DROP TRIGGER trigger-name;
(c) schmiedecke 06
DB11- Aktive DB
6
Trigger
• Einfache SQL-Trigger:
– Trigger-Body reines SQL
– evtl. mit Variablen
• Instead-Of-Trigger:
– DML-Operationen auf Views
– oft reines SQL
• Programm-Trigger:
– Trigger-Body in prozeduraler SQL-Erweiterung
– oft Aufruf von Strored Procedures
– oft Ereignisquelle für Geschäftslogik
(c) schmiedecke 06
DB11- Aktive DB
7
"Host-Variablen"
• Einige Variablen aus der Laufzeitumgebung stehen
innerhalb von Triggern zur Verfügung,
• z.B. der alte und neue Zustand der aktuellen Zeile (nur
bei Option "for each row"!)
• Sie heißen OLD und NEW
• Zugriff innerhalb von SQL mit Doppelpunkt: :OLD
:NEW
• OLD und NEW sind Tupel.
• Zugriff auf einzelne Spaltenwerte :NEW.strasse
• Umbenennung mit "Referencing OLD as ALT"
(c) schmiedecke 06
DB11- Aktive DB
8
Trigger zur Wertanpassung
• Jeder Verkauf erhöht die Vertreterprovision um 0.1%:
CREATE OR REPLACE TRIGGER prov_trigger
AFTER INSERT ON aufkopf
FOR EACH ROW
BEGIN
UPDATE vert
SET vert.prov = vert.prov+0.1
WHERE vert.vertnr =
(SELECT vertnr from kdst, vert
WHERE kdst.vertreter = vert.vertnr
AND kdst.kdnr = :NEW.kdnr);
END;
(c) schmiedecke 06
DB11- Aktive DB
9
Variablen
• Beliebige Variablen deklarierbar
• Typ: Standardtypen oder Zeilen- und Spaltentypen:
DECLARE text Varchar(10);
zeile konserven%rowtype;
gruppe konserven%bgtype;
• Verwendung mit INSERT INTO
• oder Zuweisung :=
(c) schmiedecke 06
DB11- Aktive DB
10
Trigger zur Wertanpassung - 2
• Jeder Verkauf erhöht die Vertreterprovision um 0.1% - Version 2:
CREATE OR REPLACE TRIGGER prov_trigger2
AFTER INSERT ON aufkopf
FOR EACH ROW
DECLARE vn NUMBER;
BEGIN
SELECT vertnr INTO vn FROM kdst, vert
WHERE kdst.vertreter = vert.vertnr
AND kdst.kdnr = :NEW.kdnr;
UPDATE vert
SET vert.prov = vert.prov+0.1
WHERE vert.vertnr = vn;
END;
(c) schmiedecke 06
DB11- Aktive DB
11
Trigger für Zusatzeintrag
• Jede neue Konserve der Blutgruppe AB- soll zusätzlich in die
Tabelle "abneg" eingetragen werden:
• CREATE TRIGGER trig_abneg_insert
AFTER INSERT ON konserve
REFERENCING NEW AS neu
FOR EACH ROW
WHEN :neu.blutgr = 'AB-'
BEGIN
INSERT INTO abneg VALUES (:neu.kons_id, :neu.datum);
-- zusätzlich eine PLSQL-Anweisung:
dbms_output.put_line('AB neg inserted: '|| :neu.datum);
END;
• SET SERVEROUTPUT ON
-- damit der Output angezeigt wird….
(c) schmiedecke 06
DB11- Aktive DB
12
Trigger für Zeitstempel
• Jeder Eintrag in die Konserventabelle erhält einen Zeitstempel
• CREATE TRIGGER stempel
BEFORE INSERT ON konserve
REFERENCING NEW AS neu
FOR EACH ROW
BEGIN
:neu.zeitstempel := SYSDATE;
END;
(c) schmiedecke 06
DB11- Aktive DB
13
Löschen ganzer Tabellen
• DELETE FROM tabelle;
löst alle DELETE-Trigger aus
• Umgehen mit DDL-Anweisungen anstelle von DMLAnweisungen:
• DROP TABLE tabelle;
Create TABLE tabelle ...;
• TRUNCATE TABLE tabelle;
(c) schmiedecke 06
DB11- Aktive DB
14
Sequences in Oracle
•
•
•
•
Oracle kennt kein Auto-Increment
stattdessen Sequences
Vorteil: tabellenübergreifend nutzbar
Schritte:
–
–
–
–
Sequence spezifizieren und erzeugen
Sequence initialisieren
Werte lesen
Sequence weiterschalten
(c) schmiedecke 06
DB11- Aktive DB
15
Sequenz erzeugen
• CREATE SEQUENCE sequence_name
[ MINVALUE value ]
[ MAXVALUE value ]
[ START WITH value ]
[ INCREMENT BY value ]
[ CYCLE ]
[ CACHE value ]
[ ORDER ]
;
example:
• CREATE SEQUENCE id_seq
MINVALUE 0
START WITH 10
INCREMENT BY 10
CACHE 20;
(c) schmiedecke 06
DB11- Aktive DB
16
Sequenz benutzen
• 2 Pseudospalten in Pseudotabelle "DUAL":
– sequenz.currval - enthält letzten vergebenen Wert
– sequenz. nextval - erzeugt und liefert Folgewert
- und initialisiert die Sequenz
• SELECT id_seq.nextval FROM dual; -- initialis.
SELECT id_seq.currval, id_seq.nextval FROM dual;
CURRVAL NEXTVAL
10
20
• INSERT INTO spender (id_seq.nextval, 'Mayr', 'Sepp', … );
(c) schmiedecke 06
DB11- Aktive DB
17
Trigger für Auto-PK
CREATE TRIGGER trg_spender_id
BEFORE INSERT ON spender
FOR EACH ROW
BEGIN
SELECT id_seq.nextval INTO
:NEW.spender_id FROM DUAL;
END;
(c) schmiedecke 06
DB11- Aktive DB
18
Views
• Schein-Relationen (vgl. Scheinriese bei Jim Knopf )
• Realisierung externer Sichten
• Gespeicherte benannte SELECT-Anweisungen, die
(fast) wie Tabellen benutzt werden können
• Nicht als Relation gespeichert, sonder immer wieder
frisch erzeugt!
• Komplementärbegriff: Basisrelation
• Vorteile:
– Implementierungs-Kapselung (Verbergen der Struktur)
– Komplexitätskapselung bei Queries
(c) schmiedecke 06
DB11- Aktive DB
19
Definition und Benutzung einer View
CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE predicates;
Beispiel:
CREATE VIEW terminbuch AS
SELECT termin, platz, name AS "Spender"
FROM termin, spender
WHERE termin.spender_id = spender.spender_id
;
SELECT * FROM terminbuch
WHERE platz = P AND termin BETWEEN 01/06/06 AND 01/0706;
Ändern einer VIEW :
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
(c) schmiedecke 06
DB11- Aktive DB
20
DML auf Views:
Instead-of-Trigger
• Generell keine DML-Operationen auf Views
• Erlaubt bei 1-Tabellen-Projektionen
• Instead of – Trigger
– ersetzt scheinbare DML-Anweisung auf einer View
– durch DML-Anweisungen auf den Basisrelationen
(c) schmiedecke 06
DB11- Aktive DB
21
Trigger-Syntax
CREATE [OR REPLACE] TRIGGER trigger-name
{ BEFORE | AFTER | INSTEAD OF } trigger-event
ON tab-name
[ FOR EACH ROW [ WHEN trigger-condition ] ]
BEGIN
trigger-body
END trigger-name
ALTER TRIGGER trigger-name DISABLE;
DROP TRIGGER trigger-name;
(c) schmiedecke 06
DB11- Aktive DB
22
INSTEAD-OF-TRIGGER
zur Erinnerung:
CREATE VIEW terminbuch AS
SELECT termin, platz, name AS "Spender"
FROM termin, spender
WHERE termin.sp_id = spender.sp_id
;
CREATE TRIGGER trg_ins_termin
INSTEAD OF INSERT ON terminbuch
FOR EACH ROW
- - um :NEW zur Verfügung zu haben
BEGIN
INSERT INTO termin (datum, platz, sp_id)
VALUES (:NEW.termin, :NEW.platz,
( SELECT sp_id FROM spender
WHERE sp_name = name)
END trg_ins_termin;
(c) schmiedecke 06
DB11- Aktive DB
23
Verwendung von Views
• Zur Vereinfachung häufig benutzter Abfragen
• Zur Kapselung der Basisrelationen:
– als Schnittstelle für graphische Oberflächen
– als Schnittstelle für Kommunikationsprozesse, die Daten aus der DB
lesen oder dort ablegen
– oder zur Anpassung von Standard-Datenbanken an Projektspezifika
(Verschmelzung mit Ergänzungsrelationen)
• Vorteil:
– Schnittstellenabhängige Software wird von Änderungen der
Basisrelationen abgeschirmt
(c) schmiedecke 06
DB11- Aktive DB
24
Prozedurale SQL-Erweiterung
• SQL ist (bewusst) begrenzt
• Prozedurale Vollständigkeit manchmal nötig, oft
erwünscht
• 2 Möglichkeiten:
– SQL-Anweisungen in Programmiersprache einbetten, DBInteraktion während der Programmausführung
embedded SQL
JDBC, SQLJ, ADO, ...
– Prozedurale Anteile in SQL einfügen,
Ausführung im DBMS
prozedurale Erweiterungen,
SQL/PSM (seit SQL-1999) (persistent stored modules)
in Oracle PL/SQL seit ... ?
(c) schmiedecke 06
DB11- Aktive DB
25
PL/SQL
• ORACLE-DB-Programmiersprache
• sehr einfach zu lernen
• Grundstruktur eines PL/SQL-Skripts:
DECLARE
variablen
BEGIN
anweisungen
EXCEPTION
fehlerbehandlungs-anweisungen
END
• Weblinks:
http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html
---sehr kompakte Syntax-Beschreibung
http://www.dbai.tuwien.ac.at/education/dbs/folien/plsql.pdf ------umfassende Einführung mit Beispielen
(c) schmiedecke 06
DB11- Aktive DB
26
MySQL
•
•
•
•
... Trigger erst ab MySQL 5,
... erst ab Version 5.1.4 ohne Super-Rechte definierbar.
Sprache ziemlich genau SQL/PSM
Beschreibung (bisher nur Englisch)
http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html
(c) schmiedecke 06
DB11- Aktive DB
27
PL/SQL
• die üblichen Kontrollstrukturen
• Datentypen wie in ORACLE
• am besten Variablentypen genau an DB-Typen anpassen:
DECLARE
akt_blutgr konserve.blutgr%TYPE;
akt_kons konserve%ROWTYPE;
• Laden von Daten in PL/SQL-Variablen
SELECT col1, col2 INTO var1, var2
FROM tabelle
WHERE bedingung;
(c) schmiedecke 06
DB11- Aktive DB
28
Trigger zur Alters-Überprüfung
•
•
•
•
•
Spender über 100 Jahre dürfen nicht eingetragen werden.
Zusätzliche Spalte midage soll eine 1 erhalten, falls Spender zwischen 20
und 50, sonst eine 0.
Definiert mit Typ NUMBER(1)
Trigger zur Altersüberprüfung
CREATE OR REPLACE TRIGGER age_trigger
BEFORE INSERT ON konserve
FOR EACH ROW
BEGIN
IF :NEW.alter BETWEEN 20 AND 50
THEN :NEW.midage := 1
ELSEIF alter > 100
THEN :NEW.midage := 999 -- illegal!
ELSE :NEW.midage := 0
ENDIF;
END;
(c) schmiedecke 06
DB11- Aktive DB
29
Exceptions
• Bessere Lösung mit Exceptions
• Dem Selbststudium überlassen...
(s. http://www.dbai.tuwien.ac.at/education/dbs/folien/plsql.pdf)
•
CREATE OR REPLACE TRIGGER age_trigger
BEFORE INSERT ON konserve
FOR EACH ROW
DECLARE age_exception EXCEPTION;
BEGIN
IF :NEW.alter BETWEEN 20 AND 50
THEN :NEW.midage := 1
ELSEIF alter > 100
THEN RAISE age_exception
ELSE :NEW.midage := 0
ENDIF;
END;
(c) schmiedecke 06
DB11- Aktive DB
30
Trigger-Verwendung
•
Semantische Integrität:
– Berechnung abgeleiteter Attribute
– z.B. Summenspalte mitführen
(Achtung: nur Delta addieren, nicht neu summieren
)
•
In ORACLE: AUTO INCREMENT für Primärschlüssel
•
Projekt-Verwaltung
•
Aufruf externer Funktionen
•
Datenmanipulation auf Views
– Eintrag von Datum und Autor bei insert und update
– Logging (Mitschreiben bestimmter DB-Aktionen)
– z.B. Palettenwechsel, Nachbestellungen, …
– Grund: SQL-Befehle werden oft dynamisch zusammengesetzt – im
Programmcode unauffindbar
– instead of – Trigger
(c) schmiedecke 06
nächstes Kapitel
DB11- Aktive DB
31
!
"
#
$%
