Routinen und Trigger - BFH-TI Staff

Routinen und Trigger
Contents
Pierre Fierz
Routinen und Trigger
Pierre Fierz
Chapter 8
Routinen und Trigger
Lecture Datenbanken
25.04.2014
Routinen
(SQL-invoked routine)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
Trigger
2 Prozedurale Erweiterung von SQL
3 Trigger
Pierre Fierz
Berner Fachhochschule
Technik und Informatik
8.1
Die Lernziele dieses Kapitels.
Routinen und Trigger
8.2
Outline
Pierre Fierz
Pierre Fierz
Routinen
(SQL-invoked routine)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
1
Syntax der Erweiterung von SQl
2
Kreieren von Prozeduren und Funktionen
3
Kreieren von Triggers
4
Korrektes Anwenden von Prozeduren und Triggers
Routinen und Trigger
Trigger
2 Prozedurale Erweiterung von SQL
3 Trigger
8.3
8.4
Routinen
Routinen und Trigger
Routinen und Trigger
Routinen (2)
Pierre Fierz
• Benutzerdefinierte-Routinen ermöglichen das Speichern
und Ausführen von Programmcode in der Datenbank.
• Der Begriff Benutzerdefinierte-Routine umfasst
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
• Nachfolgend die Syntax für die Definition von Prozeduren
SQL Syntax (CREATE PROCEDURE)
Trigger
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Prozeduren und Funktionen.
<procedure>
::= CREATE PROCEDURE
<prozedur-name> ([<Parameterliste>])
[<Routinencharakteristik>]...
<Routine-Body>
<Parameterliste>
::= <Parameterdef> [,<Parameterdef>]...
<Parameterdef>
::= [IN | OUT | INOUT] <parameter-name> <data-type>
• 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.
• Die Schlüsselworte IN, OUT und INOUT stehen für
• Der Rumpf enthält die Implementation der Routine in SQL
Eingabeparameter, Ausgabeparameter bzw. kombinierte
Ein- und Ausgabeparameter.
• Hier können die prozeduralen Erweiterungen von SQL
verwendet werden.
8.5
Routinen (3)
Routinen und Trigger
8.6
Routinen und Trigger
Funktionen
Pierre Fierz
• Im folgenden Beispiel ist eine SQL-Prozedur angegeben,
die das Ergebnis einer Berechnung über einen Parameter
zurückgibt.
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
• Nachfolgend die Syntax für die Definition von Funktionen
SQL Syntax (CREATE FUNCTION)
Trigger
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>]...
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
<Parameterdef>
::= <parameter-name> <data-type>
<table-type>
::= TABLE( <Column-Element> [,<Column-Element>]...)
<Column-Element> ::= <column-name> <data-type>
8.7
8.8
Funktionen (2)
Routinen und Trigger
Routinen und Trigger
Funktionen (3)
Pierre Fierz
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
• Prozeduren werden mit dem CALL Statement aufgerufen.
• Sie werden meistens von anderen Prozeduren oder
Funktionen aufgerufen.
Trigger
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
• Funktionen werden in einem Ausdruck aufgerufen
SQL Funktion
• Insbesondere können Sie in einem SELECT Befehl
CREATE FUNCTION StundenFunc (pNr INTEGER)
RETURNS FLOAT
BEGIN
DECLARE WO FLOAT DEFAULT 0.0;
verwendet werden.
Aufruf einer Funktion
SELECT
FROM
GROUP BY
HAVING
SELECT SUM(Wochenstunden) INTO WO
FROM MitProj m
WHERE pNr = m.pNr;
SUM(Wochenstunden) WO
MitProj m
pNr
StundenFunc(7) < WO
RETURN WO;
END
8.9
Weitere Prozeduren
Routinen und Trigger
8.10
Routinencharakteristika
Pierre Fierz
• Neben den Prozeduren und Funktionen existieren im
Standard auch noch Methoden.
• Dies sind spezielle Funktionen, die an einen
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
• Eine Routine kann verschiedene Charakteristika besitzen.
• Diese Beschreiben verschiedene Merkmale einer Routine.
• Im folgenden sind die wichtigsten Charakteristika
Trigger
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
aufgeführt.
Benutzerdefinierten Datentyp gebunden sind.
• Die Methoden gehören zur objektrelationalen Erweiterung
des Standards.
• Die Sprachklausel gibt die Sprache der Implementation
an
• Der SQL-Standard lässt neben SQL-Routinen auch
SQL Syntax (LANGUAGE)
sogenannte externe Routinen zu.
• Externe Routinen werden nicht in SQL implementiert.
LANGUAGE {ADA | C | COBOL | FORTRAN | JAVA
| MUMPS | PASCAL | PLI | SQL}
• Sie können zum Beispiel in Java oder C implementiert
werden.
8.11
8.12
Routinen und Trigger
Routinencharakteristika (2)
Routinencharakteristika (3)
Pierre Fierz
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.
Routinen und Trigger
Prozedurale
Erweiterung von SQL
Routinen
(SQL-invoked routine)
• Die Zugriffsklausel
Trigger
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (DATA ACCESS)
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.
NO SQL
SQL Syntax (DETERMINISTIC)
CONTAINS SQL
[NOT DETERMINISTIC | DETERMINISTIC]
READS SQL DATA
MODIFIES SQL DATA
• Aufrufe von nicht deterministischen Routinen dürfen nicht
Teil von Check-Klauseln oder Case-Ausdrücken sein.
8.13
Routinen und Trigger
Routinencharakteristika (4)
8.14
Routinencharakteristika (5)
Pierre Fierz
• Die Nullaufrufsklausel bestimmt, ob die Funktion
aufrufbar ist, wenn einer der Parameter NULL ist.
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
• Die Security Klausel
SQL Syntax (SECURITY CLAUSE)
SQL SECURITY INVOKER
Trigger
SQL Syntax (NULL CALL)
RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
Routinen und Trigger
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.
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
• Achtung: Nur eine Funktionsdeklaration darf eine
privilegiert werden.
Nullaufrufsklausel enthalten.
• Man kann z.B. erzwingen, dass ein Benutzer eine Tabelle
nur über eine Routine verändern kann.
8.15
8.16
Routinencharakteristika (6)
Routinen und Trigger
Tabellen als Rückgabewert
Pierre Fierz
Routinen und Trigger
Pierre Fierz
• Ab SQL:2003 können SQL-Funktionen Tabellentypen
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
zurückliefern.
Routinen
(SQL-invoked routine)
• Eine Tabellenfunktion kann in einer Anfrage als
Tabellenreferenz verwendet werden.
Prozedurale
Erweiterung von SQL
Trigger
Tabellenfunktion
• Defaultmässig sind die folgenden Charakteristica
eingestellt:
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);
LANGUAGE SQL, NOT DETERMINISTIC, CONTAINS SQL, CALLED ON NULL
INPUT, SQL SECURITY DEFINER
Die gerade definierte Funktion kann nun als Tabellenreferenz
verwendet werden.
• Für normale SQL-Routinen kann meistens die
Default-Einstellung gewählt werden.
SELECT s.pNr, s.Stunden, s.aNr, a.AName
FROM
StundenProc() s NATURAL JOIN Abteilung a
WHERE s.Stunden > 30.0
8.17
Outline
Routinen und Trigger
8.18
Routinen und Trigger
Block Statement
Pierre Fierz
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
• 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>;]...
2 Prozedurale Erweiterung von SQL
END
3 Trigger
• 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.19
8.20
Lokale Variablen
Routinen und Trigger
Bedingte Ausführung
Pierre Fierz
Routinen und Trigger
Pierre Fierz
• Zur bedingten Ausführung stehen zwei Kontrollstrukturen
zur Verfügung.
• Lokale Variablen müssen wie in einer
Programmiersprache deklariert und initialisiert werden.
SQL Syntax (DECLARATION)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Routinen
(SQL-invoked routine)
SQL Syntax (IF)
<ifstmt> ::= IF <conditional-expression> THEN <SQLStatement>
[ELSEIF <conditional-expression> THEN <SQLStatement>]...
[ELSE <SQLStatement>]
Prozedurale
Erweiterung von SQL
Trigger
END IF
<declaration> ::= DECLARE <variablen-name> <daten-typ>
[DEFAULT <literal>]
Verschiedene Selects
• Mit dem SET Befehl kann einer Variablen einen Wert
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
zugewiesen werden.
SQL Syntax (ASSIGNMENT)
<assignment> ::= SET <variablen-name> = <expression>
8.21
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
8.22
Schleifen
• SQL kennt vier verschiedene Arten von Schleifen
Routinen und Trigger
Pierre Fierz
SQL Syntax (LOOP)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
<loopstmt> ::= [<label>:]
LOOP
<SQLStatement>
END LOOP [<label>]
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
<conditional-expression> THEN <SQLStatement>
[WHEN <conditional-expression> THEN <SQLStatement>]
[ELSE <SQLStatement>]
WHEN
SQL Syntax (WHILE)
END CASE
<whilestmt> ::= [<label>:]
WHILE <conditional-expression> DO
<SQLStatement>
END WHILE [<label>]
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
SQL Syntax (REPEATE)
<repeatstmt> ::= [<label>:]
REPEAT
<SQLStatement>
UNTIL <conditional-expression>
END REPEAT [<label>]
Falls die Variable Test nicht 0 oder 1 ist, so wird eine Exception
ausgelöst.
8.23
8.24
Schleifen (2)
• Eine For-Schleife iteriert über alle Elemente der
Ergebnismenge einer Anfrage bzw. eines Cursors.
SQL Syntax (FOR)
Routinen und Trigger
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
<forstmt> ::= [<label>:]
FOR <loopvariable> AS
[<cursor-name> CURSOR FOR]
<select-befehl> DO
<SQLStatement>
END FOR [<label>]
Verlassen von Schleifen
Pierre Fierz
• Mit Hilfe der Label kann eine Iteration einer Schleife
abgebrochen werden oder die Schleife ganz verlassen
werden.
Prozedurale
Erweiterung von SQL
Trigger
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
• Abbrechen der aktuellen Iteration
SQL Syntax (ITERATE)
<iteratestmt> ::= ITERATE <label-name>
Wochenstunden aller Projekte
• Abbrechen der Schleife
CREATE FUNCTION TestCurs()
RETURNS FLOAT
BEGIN
DECLARE wo FLOAT DEFAULT 0;
SQL Syntax (LEAVE)
<leavestmt> ::= LEAVE <label-name>
FOR proj AS
SELECT Wochenstunden FROM MitProj
DO
SET wo = wo + proj.Wochenstunden;
END FOR;
END
8.25
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
8.26
Fehlerbehandlung (2)
Pierre Fierz
Pierre Fierz
• SQLSTATE ist ein String der Länge 5.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
• Die zwei ersten Zeichen bezeichnen die Fehlerklasse
• Die 3 letzten Zeichen die Fehlersubklasse.
• in der nachfolgenden Tabelle sind einige Beispiele
Trigger
Prozedurale
Erweiterung von SQL
Trigger
Fehlercodes
Klasse
00
01
01
::= 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.
Routinen
(SQL-invoked routine)
angegeben
::= 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>
Routinen und Trigger
02
02
08
08
08
08
8.27
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
Fehlerbehandlung (4)
Pierre Fierz
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
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
SQL Syntax (DECLARE CONDITION)
eine Exception auslösen
<condiditiondeclare> ::= DECLARE <condition-name>
FOR SQLSTATE <sqlstate-value>
SQL Syntax (SIGNAL)
<signalstmt> ::= SIGNAL {<exception-name>|<sqlstate-decl>}
[SET <informationsliste>]
• 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.
• Die Informationsliste kann beispielsweise eine Zuweisung
der Form MESSAGE.TEXT = <information> enthalten.
8.29
Fehlerbehandlung (5)
Routinen und Trigger
8.30
Outline
Pierre Fierz
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)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Prozedurale
Erweiterung von SQL
1 Routinen (SQL-invoked routine)
Trigger
Trigger
DECLARE curs CURSOR FOR
SELECT Wochenstunden FROM MitProj;
2 Prozedurale Erweiterung von SQL
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;
3 Trigger
END
8.31
8.32
Trigger
Routinen und Trigger
Trigger (2)
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 und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
SQL Syntax (CREATE TRIGGER)
• 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.
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>
• Aktivierungsereignis
• Ein Trigger kann durch eine auf eine Basistabelle
ausgeführten Datenmanipulationsanweisung INSERT,
UPDATE oder DELETE aktiviert werden.
8.33
Trigger (3)
Routinen und Trigger
8.34
Trigger (4)
Pierre Fierz
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.
Routinen und Trigger
Trigger
• Granularität
• FOR EACH ROW: Der Trigger wird für jedes Tupel
aufgerufen
• FOR EACH STATEMENT: Der Trigger wird nur einmal pro
SQL Syntax (TRANSITION)
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
Update Anweisung aufgerufen.
<transitionvariable> ::= OLD [ROW] [AS] <variable-name>
NEW [ROW] [AS] <variable-name>
<transitiontable>
::= OLD TABLE [AS] <variable-name>
NEW TABLE [AS] <variable-name>
• 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
• OLD und NEW bestimmen den Zustand der Zeilen vor bzw.
zusammengesetzten SQL-Anweisung die atomar sein
muss.
nach der Ausführung der Anweisung.
8.35
8.36
Reihenfolge der Aktivierung
Routinen und Trigger
Transitionale Integritätsbedingungen
Pierre Fierz
Routinen und Trigger
Pierre Fierz
• Trigger eignen sich gut für die Umsetzung sogenannter
transitionaler Integritätsbedingungen.
Routinen
(SQL-invoked routine)
• Dasselbe Ereignis kann mehrere Triggers besitzen
Prozedurale
Erweiterung von SQL
Trigger
• 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.
• In diesem Fall werden die Triggers nacheinander
aufgerufen
Gehalt testen
• Jeder Trigger besitzt intern einen Zeitstempel, der den
In dieser Firma darf das Gehalt eines Mitarbeiters nicht
verkleinert werden.
Zeitpunkt der Erzeugung des Triggers angibt.
• Die Zeitstempel legen die Aktivierungsreihenfolge fest.
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’;
• Es werden zuerst die Trigger mit dem ältesten Zeitstempel
aktiviert.
IF (neu.Gehalt $<$ old.Gehalt)
THEN
SIGNAL gehaltexception;
END IF;
END
8.37
Triggers und Assertion
Routinen und Trigger
8.38
Triggers und Assertion (2)
Pierre Fierz
Pierre Fierz
Routinen
(SQL-invoked routine)
• Assertions können häufig mit Hilfe von Triggers ersetzt
werden.
Routinen und Trigger
Prozedurale
Erweiterung von SQL
Trigger
• Als erstes definieren wir eine Funktion, die die
Wochenstunden eines Mitarbeiters bestimmt.
Kontrolle der Anzahl Wochenstunden (2)
• Wir wollen die folgende Assertion mit Hilfe von Triggers
ersetzen.
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
CREATE FUNCTION maxStunden (mNr INTEGER)
RETURNS FLOAT
BEGIN
DECLARE st FLOAT DEFAULT 0.0;
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))
SELECT
FROM
WHERE
RETURN
SUM(Wochenstunden) INTO st
MitProj m
mNr = m.mNr;
st;
END
8.39
8.40
Triggers und Assertion (3)
Routinen und Trigger
Triggers und Assertion (4)
Pierre Fierz
• Nun definieren wir einen Insert trigger für die Tabelle
MitProj;
Routinen und Trigger
Pierre Fierz
Routinen
(SQL-invoked routine)
Prozedurale
Erweiterung von SQL
Trigger
• Als letztes definieren wir einen Update trigger für die
Tabelle MitProj;
Kontrolle der Anzahl Wochenstunden (4)
Kontrolle der Anzahl Wochenstunden (3)
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’;
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;
IF (maxStunden(alt.mNr) > 35) THEN
SIGNAL stundenexception;
END IF;
END
END
8.41
8.42