Routinen und Trigger - BFH-TI / Organisation

Routinen und Trigger
Pierre Fierz
Chapter 8
Routinen
(SQL-invoked routine)
Routinen und Trigger
Prozedurale
Erweiterung von SQL
Lecture Datenbanken
25.04.2014
Trigger
Pierre Fierz
Berner Fachhochschule
Technik und Informatik
8.1
Contents
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
2 Prozedurale Erweiterung von SQL
3 Trigger
8.2
Die Lernziele dieses Kapitels.
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
1
Syntax der Erweiterung von SQl
2
Kreieren von Prozeduren und Funktionen
3
Kreieren von Triggers
4
Korrektes Anwenden von Prozeduren und Triggers
8.3
Outline
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
2 Prozedurale Erweiterung von SQL
3 Trigger
8.4
Routinen
Routinen und Trigger
Pierre Fierz
• Benutzerdefinierte-Routinen ermöglichen das Speichern
und Ausführen von Programmcode in der Datenbank.
• Der Begriff Benutzerdefinierte-Routine umfasst
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Prozeduren und Funktionen.
• Eine Routine besteht aus einer Signatur und einem
Rumpf.
• Die Signatur besteht aus:
• Dem Namen der Routine
• Den Parametern
• Für Funktionen noch den Typ des Rückgabewertes.
• Der Rumpf enthält die Implementation der Routine in SQL
• Hier können die prozeduralen Erweiterungen von SQL
verwendet werden.
8.5
Routinen und Trigger
Routinen (2)
Pierre Fierz
• Nachfolgend die Syntax für die Definition von Prozeduren
SQL Syntax (CREATE PROCEDURE)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
<procedure>
::= CREATE PROCEDURE
<prozedur-name> ([<Parameterliste>])
[<Routinencharakteristik>]...
<Routine-Body>
<Parameterliste>
::= <Parameterdef> [,<Parameterdef>]...
<Parameterdef>
::= [IN | OUT | INOUT] <parameter-name> <data-type>
• Die Schlüsselworte IN, OUT und INOUT stehen für
Eingabeparameter, Ausgabeparameter bzw. kombinierte
Ein- und Ausgabeparameter.
8.6
Routinen (3)
Routinen und Trigger
Pierre Fierz
• Im folgenden Beispiel ist eine SQL-Prozedur angegeben,
die das Ergebnis einer Berechnung über einen Parameter
zurückgibt.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
SQL Routine
CREATE PROCEDURE StundenProc (IN pNr INTEGER,
OUT ws FLOAT)
BEGIN
SELECT SUM(Wochenstunden) INTO ws -- Setzen OUT
FROM MitProj m
WHERE pNr = m.pNr
END
8.7
Routinen und Trigger
Funktionen
Pierre Fierz
• Nachfolgend die Syntax für die Definition von Funktionen
SQL Syntax (CREATE FUNCTION)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
<function>
::= CREATE FUNCTION
<function-name> ([<Parameterliste>])
RETURNS {<data-type> | <table-type>}
[<Routinencharakteristika>]...
<SQLStatement>
<Parameterliste>
::= <Parameterdef> [,<Parameterdef>]...
<Parameterdef>
::= <parameter-name> <data-type>
<table-type>
::= TABLE( <Column-Element> [,<Column-Element>]...)
<Column-Element> ::= <column-name> <data-type>
8.8
Funktionen (2)
Routinen und Trigger
Pierre Fierz
• Jede Funktion besitzt einen Rückgabewert.
• Der Datentyp wird durch die RETURNS-Klausel festgelegt.
Routinen
(SQL-invoked routine)
• Es gibt einen Laufzeitfehler, wenn eine Funktion nicht mit
RETURN beendet wird.
Prozedurale
Erweiterung von SQL
Trigger
SQL Funktion
CREATE FUNCTION StundenFunc (pNr INTEGER)
RETURNS FLOAT
BEGIN
DECLARE WO FLOAT DEFAULT 0.0;
SELECT SUM(Wochenstunden) INTO WO
FROM MitProj m
WHERE pNr = m.pNr;
RETURN WO;
END
8.9
Routinen und Trigger
Funktionen (3)
Pierre Fierz
• Prozeduren werden mit dem CALL Statement aufgerufen.
• Sie werden meistens von anderen Prozeduren oder
Funktionen aufgerufen.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
• Funktionen werden in einem Ausdruck aufgerufen
• Insbesondere können Sie in einem SELECT Befehl
verwendet werden.
Aufruf einer Funktion
SELECT
FROM
GROUP BY
HAVING
SUM(Wochenstunden) WO
MitProj m
pNr
StundenFunc(7) < WO
8.10
Weitere Prozeduren
Routinen und Trigger
Pierre Fierz
• Neben den Prozeduren und Funktionen existieren im
Standard auch noch Methoden.
• Dies sind spezielle Funktionen, die an einen
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Benutzerdefinierten Datentyp gebunden sind.
• Die Methoden gehören zur objektrelationalen Erweiterung
des Standards.
• Der SQL-Standard lässt neben SQL-Routinen auch
sogenannte externe Routinen zu.
• Externe Routinen werden nicht in SQL implementiert.
• Sie können zum Beispiel in Java oder C implementiert
werden.
8.11
Routinencharakteristika
Routinen und Trigger
Pierre Fierz
• Eine Routine kann verschiedene Charakteristika besitzen.
• Diese Beschreiben verschiedene Merkmale einer Routine.
• Im folgenden sind die wichtigsten Charakteristika
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
aufgeführt.
• Die Sprachklausel gibt die Sprache der Implementation
an
SQL Syntax (LANGUAGE)
LANGUAGE {ADA | C | COBOL | FORTRAN | JAVA
| MUMPS | PASCAL | PLI | SQL}
8.12
Routinencharakteristika (2)
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
• Die Determinismusklausel drückt explizit aus, ob die
Routine nichtdeterministische Anweisungen wie zum
Beispiel CURRENT_TIME enthält oder nicht.
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (DETERMINISTIC)
[NOT DETERMINISTIC | DETERMINISTIC]
• Aufrufe von nicht deterministischen Routinen dürfen nicht
Teil von Check-Klauseln oder Case-Ausdrücken sein.
8.13
Routinencharakteristika (3)
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
• Die Zugriffsklausel
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (DATA ACCESS)
NO SQL
CONTAINS SQL
READS SQL DATA
MODIFIES SQL DATA
Die Routine enthält kein SQL. Nur bei
externen Routinen erlaubt.
Die Routine enthält keine lese/schreibe-Anweisungen.
Die Routine liest eventuell SQL-Daten.
Die Routine liest und verändert
eventuell SQL-Daten.
8.14
Routinen und Trigger
Routinencharakteristika (4)
Pierre Fierz
• Die Nullaufrufsklausel bestimmt, ob die Funktion
aufrufbar ist, wenn einer der Parameter NULL ist.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (NULL CALL)
RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
Ist beim Aufruf der Routine einer
der Parameter NULL, so wird die
Routine nicht ausgeführt und
der Wert NULL zurückgeliefert.
Die Routine wird in jedem Fall
aufgerufen.
• Achtung: Nur eine Funktionsdeklaration darf eine
Nullaufrufsklausel enthalten.
8.15
Routinencharakteristika (5)
Routinen und Trigger
Pierre Fierz
• Die Security Klausel
SQL Syntax (SECURITY CLAUSE)
SQL SECURITY INVOKER
SQL SECURITY DEFINER
Die Routine wird mit den Privilegien
des Aufrufers abgearbeitet.
Die Routine wird mit den Privilegien
des Erstellers der Routine abgearbeitet. Die Funktion CURRENT_USER
gibt dann innerhalb der Routine den
Namen des Erzeugers zurück.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
• Mit Hilfe dieser Klausel kann ein Benutzer temporär
privilegiert werden.
• Man kann z.B. erzwingen, dass ein Benutzer eine Tabelle
nur über eine Routine verändern kann.
8.16
Routinencharakteristika (6)
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
• Es gibt noch weitere Routinencharakteristika
• Diese ergeben aber nur im Zusammenhang mit externen
Routinen einen Sinn.
Prozedurale
Erweiterung von SQL
Trigger
• Defaultmässig sind die folgenden Charakteristica
eingestellt:
LANGUAGE SQL, NOT DETERMINISTIC, CONTAINS SQL, CALLED ON NULL
INPUT, SQL SECURITY DEFINER
• Für normale SQL-Routinen kann meistens die
Default-Einstellung gewählt werden.
8.17
Tabellen als Rückgabewert
Routinen und Trigger
Pierre Fierz
• Ab SQL:2003 können SQL-Funktionen Tabellentypen
zurückliefern.
• Eine Tabellenfunktion kann in einer Anfrage als
Tabellenreferenz verwendet werden.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Tabellenfunktion
CREATE FUNCTION StundenProc()
RETURNS TABLE (pNr INTEGER, Stunden FLOAT, aNr INTEGER)
RETURN (SELECT
p.pNr, sum(Wochenstunden), p.paNr
FROM
MitProj NATURAL JOIN Projekt p
GROUP BY p.pNr, p.paNr);
Die gerade definierte Funktion kann nun als Tabellenreferenz
verwendet werden.
SELECT s.pNr, s.Stunden, s.aNr, a.AName
FROM
StundenProc() s NATURAL JOIN Abteilung a
WHERE s.Stunden > 30.0
8.18
Outline
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
2 Prozedurale Erweiterung von SQL
3 Trigger
8.19
Routinen und Trigger
Block Statement
Pierre Fierz
• Das Blockstatement fasst mehrere Anweisungen zu einer
Einheit zusammen.
SQL Syntax (BLOCKSTATEMENT)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
<stmtblock> ::= BEGIN [[NOT] ATOMIC]
<SQLStatement>;
[<SQLStatement>;]...
END
• Mit der Angabe ATOMIC wird eine Atomare Ausführung der
Einheit gewährleistet. NOT ATOMIC ist der Default.
• Im folgenden bezeichnet SQLStatement eine einzelne oder
zusammengesetzte SQL-Anweisung.
8.20
Lokale Variablen
Routinen und Trigger
Pierre Fierz
• Lokale Variablen müssen wie in einer
Programmiersprache deklariert und initialisiert werden.
SQL Syntax (DECLARATION)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
<declaration> ::= DECLARE <variablen-name> <daten-typ>
[DEFAULT <literal>]
• Mit dem SET Befehl kann einer Variablen einen Wert
zugewiesen werden.
SQL Syntax (ASSIGNMENT)
<assignment> ::= SET <variablen-name> = <expression>
8.21
Bedingte Ausführung
Routinen und Trigger
Pierre Fierz
• Zur bedingten Ausführung stehen zwei Kontrollstrukturen
zur Verfügung.
SQL Syntax (IF)
<ifstmt> ::= IF <conditional-expression> THEN <SQLStatement>
[ELSEIF <conditional-expression> THEN <SQLStatement>]...
[ELSE <SQLStatement>]
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
END IF
Verschiedene Selects
CREATE PROCEDURE bsp(test \INTEGER)
BEGIN
IF test = 0 THEN
SELECT aNr, AName FROM Abteilung;
ELSEIF test = 1 THEN
SELECT mNr, Name FROM Mitarbeiter;
ELSE
SELECT pNr, Bezeichnung FROM Projekt;
END IF;
END
8.22
Bedingte Ausführung (2)
Routinen und Trigger
Pierre Fierz
• Die Case-Anweisung führt einen WHEN-Zweig aus, wenn
der Wert der zugehörigen <expression> gleich dem Wert
der <expression> hinter dem CASE ist.
SQL Syntax (CASE)
<casestmt> ::= CASE
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
<conditional-expression> THEN <SQLStatement>
[WHEN <conditional-expression> THEN <SQLStatement>]
[ELSE <SQLStatement>]
WHEN
END CASE
Verschiedene Selects mit der Case Anweisung
CREATE PROCEDURE bsp5(test INTEGER)
BEGIN
CASE test
WHEN 0 THEN SELECT mNr, Name FROM Mitarbeiter;
WHEN 1 THEN SELECT aNr, AName FROM Abteilung;
END CASE;
END
Falls die Variable Test nicht 0 oder 1 ist, so wird eine Exception
ausgelöst.
8.23
Schleifen
• SQL kennt vier verschiedene Arten von Schleifen
Routinen und Trigger
Pierre Fierz
SQL Syntax (LOOP)
<loopstmt> ::= [<label>:]
LOOP
<SQLStatement>
END LOOP [<label>]
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (WHILE)
<whilestmt> ::= [<label>:]
WHILE <conditional-expression> DO
<SQLStatement>
END WHILE [<label>]
SQL Syntax (REPEATE)
<repeatstmt> ::= [<label>:]
REPEAT
<SQLStatement>
UNTIL <conditional-expression>
END REPEAT [<label>]
8.24
Schleifen (2)
• Eine For-Schleife iteriert über alle Elemente der
Ergebnismenge einer Anfrage bzw. eines Cursors.
SQL Syntax (FOR)
<forstmt> ::= [<label>:]
FOR <loopvariable> AS
[<cursor-name> CURSOR FOR]
<select-befehl> DO
<SQLStatement>
END FOR [<label>]
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Wochenstunden aller Projekte
CREATE FUNCTION TestCurs()
RETURNS FLOAT
BEGIN
DECLARE wo FLOAT DEFAULT 0;
FOR proj AS
SELECT Wochenstunden FROM MitProj
DO
SET wo = wo + proj.Wochenstunden;
END FOR;
END
8.25
Verlassen von Schleifen
Routinen und Trigger
Pierre Fierz
• Mit Hilfe der Label kann eine Iteration einer Schleife
abgebrochen werden oder die Schleife ganz verlassen
werden.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
• Abbrechen der aktuellen Iteration
SQL Syntax (ITERATE)
<iteratestmt> ::= ITERATE <label-name>
• Abbrechen der Schleife
SQL Syntax (LEAVE)
<leavestmt> ::= LEAVE <label-name>
8.26
Fehlerbehandlung
• Die Fehlerbehandlung geschieht mit Hilfe von Handler.
• Handler werden für einen Block deklariert.
• Wenn im entsprechenden Block ein Fehler passiert, wird
der entsprechende Handler aufgerufen
SQL Syntax (ERROR HANDLER)
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
::= DECLARE {CONTINUE| EXIT|UNDO}
HANDLER FOR <condition-value> [, <condition-value>]...
<SQLStatement>
<condition-value>::= {<sqlstate-decl>|<condition-name>
|SQL EXCEPTION|SQL WARNING|NOT FOUND}
<handler>
<sqlstate-decl>
::= SQLSTATE <sqlstate-value>
• Handler unterscheiden sich in der Art, wie sie nach ihrem
Aufruf die Kontrolle an das Programm zurückgeben:
CONTINUE: Das Programm fährt mit der nächsten
Anweisung fort.
EXIT: Das Programm verlässt den aktuellen Block
und fährt mit dem nächsten Block fort.
UNDO: Wie EXIT, nur dass alle Änderungen des
Blocks zurückgesetzt werden.
8.27
Fehlerbehandlung (2)
Routinen und Trigger
Pierre Fierz
• SQLSTATE ist ein String der Länge 5.
• Die zwei ersten Zeichen bezeichnen die Fehlerklasse
• Die 3 letzten Zeichen die Fehlersubklasse.
• in der nachfolgenden Tabelle sind einige Beispiele
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
angegeben
Fehlercodes
Klasse
00
01
01
02
02
08
08
08
08
Subklasse
000
00C
004
000
001
000
003
006
Bedeutung
Erfolgreich
Warnings
DYNAMIC RESULT SETS RETURNED
STRING DATA RIGHT TRUNCATION
usw.
Keine Daten verarbeitet
NO DATA
NO ADDITIONAL DYNAMIC RESULT SETS RETURNED
Connection Exception
CONNECTION EXCEPTION
CONNECTION DOES NOT EXIST
CONNECTION FAILURE
usw.
8.28
Fehlerbehandlung (3)
Routinen und Trigger
Pierre Fierz
• Es ist möglich den einzelnen Fehlercodes einen Namen zu
geben
Routinen
(SQL-invoked routine)
• Der Name kann beim deklarieren eines Handlers oder
beim signalisieren eines Fehlers verwendet werden.
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (DECLARE CONDITION)
<condiditiondeclare> ::= DECLARE <condition-name>
FOR SQLSTATE <sqlstate-value>
• Im Standard sind die folgenden Namen vordefiniert:
SQLWARNING
NO DATA
SQLEXCEPTION
Dieser Name steht für alle Warnings, die von SQL
generiert werden. (SQLSTATE = ’01xxx’).
Diese Bedingungen werden generiert, wenn einer
der SQL-Befehle SELECT oder FETCH keine Daten
gefunden hat (SQLSTATE = ’02xxx’).
Steht für alle Werte von SQLSTATE die weder durch
SQLWARNING noch durch NO DATA abgedeckt sind.
8.29
Fehlerbehandlung (4)
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
• Eine Exception wird im Normalfall durch eine
SQL-Anweisung ausgelöst.
Prozedurale
Erweiterung von SQL
Trigger
• Man kann aber auch mit der SIGNAL-Anweisung direkt
eine Exception auslösen
SQL Syntax (SIGNAL)
<signalstmt> ::= SIGNAL {<exception-name>|<sqlstate-decl>}
[SET <informationsliste>]
• Die Informationsliste kann beispielsweise eine Zuweisung
der Form MESSAGE.TEXT = <information> enthalten.
8.30
Fehlerbehandlung (5)
Routinen und Trigger
Pierre Fierz
Error Handler
CREATE FUNCTION testcurs()
RETURNS FLOAT
BEGIN
DECLARE wo
FLOAT DEFAULT 0.0;
DECLARE p
FLOAT DEFAULT 0.0;
DECLARE done BOOLEAN DEFAULT false;
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
DECLARE curs CURSOR FOR
SELECT Wochenstunden FROM MitProj;
BEGIN
-- Handler fuer diesen Block
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = true;
OPEN curs;
FETCH curs INTO wo;
WHILE NOT done DO
SET p = p + wo;
FETCH curs INTO wo;
END WHILE;
CLOSE curs;
END;
RETURN wo;
END
8.31
Outline
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
2 Prozedurale Erweiterung von SQL
3 Trigger
8.32
Trigger
Routinen und Trigger
Pierre Fierz
• Trigger definieren automatische Reaktionen auf
Ereignisse, die durch Datenmanipulationen auf einer
Tabelle ausgelöst werden.
• Die Definition eines Triggers geschieht mit der folgenden
Syntax
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (CREATE TRIGGER)
createtrigger ::= CREATE TRIGGER <trigger-name>
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE [OF <attributlist>]}
ON <table-name>
[REFERENCING {<transitionvariable>|<transitiontable>}]
[FOR EACH {ROW|STATEMENT}]
[WHEN (<condition>)]
<SQLStatement>
8.33
Trigger (2)
Routinen und Trigger
Pierre Fierz
• Aktivierungszeitpunkt
• BEFORE
• Der Trigger wird vor dem auslösenden Ereignis ausgelöst.
• In diesem Trigger dürfen keine Anweisungen oder
Prozeduren verwendet werden, die die Daten verändern.
• Before Trigger werden verwendet um die Daten vor der
Operation noch zu verändern.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
• AFTER
• Der Trigger wird nach dem auslösenden Ereignis ausgelöst.
• Mit Hilfe einer Signal Anweisung kann die Updateoperation
abgebrochen werden.
• Aktivierungsereignis
• Ein Trigger kann durch eine auf eine Basistabelle
ausgeführten Datenmanipulationsanweisung INSERT,
UPDATE oder DELETE aktiviert werden.
8.34
Trigger (3)
Routinen und Trigger
Pierre Fierz
• Transitionsvariablen und tabellen
Routinen
(SQL-invoked routine)
• Ermöglichen den Zugriff auf die von der Anweisung
Prozedurale
Erweiterung von SQL
(UPDATE, INSERT, DELETE) betroffenen Zeilen.
Trigger
SQL Syntax (TRANSITION)
<transitionvariable> ::= OLD [ROW] [AS] <variable-name>
NEW [ROW] [AS] <variable-name>
<transitiontable>
::= OLD TABLE [AS] <variable-name>
NEW TABLE [AS] <variable-name>
• OLD und NEW bestimmen den Zustand der Zeilen vor bzw.
nach der Ausführung der Anweisung.
8.35
Trigger (4)
Routinen und Trigger
Pierre Fierz
• Granularität
• FOR EACH ROW: Der Trigger wird für jedes Tupel
aufgerufen
• FOR EACH STATEMENT: Der Trigger wird nur einmal pro
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Update Anweisung aufgerufen.
• Bedingung
• Die Ausführung der Triggeraktion kann durch die Angabe
der WHEN-Klausel an eine Bedingung gekoppelt werden.
• Aktion
• Der Triggerrumpf besteht aus einer einzelnen oder
zusammengesetzten SQL-Anweisung die atomar sein
muss.
8.36
Reihenfolge der Aktivierung
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
• Dasselbe Ereignis kann mehrere Triggers besitzen
Prozedurale
Erweiterung von SQL
Trigger
• In diesem Fall werden die Triggers nacheinander
aufgerufen
• Jeder Trigger besitzt intern einen Zeitstempel, der den
Zeitpunkt der Erzeugung des Triggers angibt.
• Die Zeitstempel legen die Aktivierungsreihenfolge fest.
• Es werden zuerst die Trigger mit dem ältesten Zeitstempel
aktiviert.
8.37
Transitionale Integritätsbedingungen
Routinen und Trigger
Pierre Fierz
• Trigger eignen sich gut für die Umsetzung sogenannter
transitionaler Integritätsbedingungen.
• Das heisst, Bedingungen, die sowohl auf die alten wie
auch auf die neuen Werte eines Tupels zugreifen.
• Transitionale Integritätsbedingungen verhindern
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
semantisch inkorrekte Datenbankzustandstransitionen.
Gehalt testen
In dieser Firma darf das Gehalt eines Mitarbeiters nicht
verkleinert werden.
CREATE TRIGGER testGehalt
AFTER UPDATE ON Mitarbeiter
REFERENCING OLD AS alt NEW AS neu
FOR EACH ROW
BEGIN ATOMIC
DECLARE gehaltexception EXCEPTION FOR SQLSTATE ’99001’;
IF (neu.Gehalt $<$ old.Gehalt)
THEN
SIGNAL gehaltexception;
END IF;
END
8.38
Triggers und Assertion
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
• Assertions können häufig mit Hilfe von Triggers ersetzt
werden.
Prozedurale
Erweiterung von SQL
Trigger
• Wir wollen die folgende Assertion mit Hilfe von Triggers
ersetzen.
Kontrolle der Anzahl Wochenstunden
CREATE ASSERTION maxstunden
CHECK(NOT EXISTS
(SELECT SUM(Wochenstunden) Stunden
FROM Mitarbeiter m NATURAL JOIN MitProj
GROUP BY m.mNr
HAVING Stunden > 35))
8.39
Triggers und Assertion (2)
Routinen und Trigger
Pierre Fierz
• Als erstes definieren wir eine Funktion, die die
Wochenstunden eines Mitarbeiters bestimmt.
Kontrolle der Anzahl Wochenstunden (2)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
CREATE FUNCTION maxStunden (mNr INTEGER)
RETURNS FLOAT
BEGIN
DECLARE st FLOAT DEFAULT 0.0;
SELECT
FROM
WHERE
RETURN
SUM(Wochenstunden) INTO st
MitProj m
mNr = m.mNr;
st;
END
8.40
Triggers und Assertion (3)
Routinen und Trigger
Pierre Fierz
• Nun definieren wir einen Insert trigger für die Tabelle
MitProj;
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Kontrolle der Anzahl Wochenstunden (3)
CREATE TRIGGER maxstundenI
AFTER INSERT ON MitProj
REFERENCING NEW AS neu
FOR EACH ROW
BEGIN ATOMIC
DECLARE stundenexception EXCEPTION FOR SQLSTATE ’99002’;
IF (maxStunden(neu.mNr) > 35) THEN
SIGNAL stundenexception;
END IF;
END
8.41
Triggers und Assertion (4)
Routinen und Trigger
Pierre Fierz
• Als letztes definieren wir einen Update trigger für die
Tabelle MitProj;
Kontrolle der Anzahl Wochenstunden (4)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
CREATE TRIGGER maxstundenU
AFTER UPDATE ON MitProj
REFERENCING OLD AS alt NEW AS neu
FOR EACH ROW
WHEN (alt.Wochenstunden != neu.Wochenstunden)
BEGIN ATOMIC
DECLARE stundenexception EXCEPTION FOR SQLSTATE ’99002’;
IF (maxStunden(alt.mNr) > 35) THEN
SIGNAL stundenexception;
END IF;
END
8.42