20150328_2_Trigger - home.hs

Übung PL/SQL Trigger
Dr. Christian Senger
DB & IS II, SS 15
28.03.2015
1) Gebe das aktuelle Datum aus. Wofür steht „dual“? Ändere das Datum für Deine aktuelle
Session auf das Format „Jahr (4 Stellen) – Monat (2 Stellen) – Tag (2 Stellen)[Leerzeichen]Stunde (2 Stellen) : Minute (2 Stellen) : Sekunde (2 Stellen).
2) Ausgangslage. Erstelle die Tabelle stadt_temperatur_fahrenheit, die View
berlin_temperatur_celsius und den Trigger berlin_temperatur_hinzufuegen wie in der Vorlesung beschrieben. Fülle die Tabelle mit mind. 2 Datensätzen, mindestens eine Temperaturmessung sollte in Berlin gewesen sein.
3) Update-Trigger, View. Erstelle eine Update-Trigger, mit dem man auf die View berlin_temperatur_aendern die Updates nach dem Schema:
 UPDATE berlin_temperatur_celsius SET celcius = 11 WHERE
timestamp = '2009-09-07 14:51:47'
 UPDATE berlin_temperatur_celsius SET celcius = 10,
timestamp = '2009-09-07 15:51:47' WHERE timestamp =
'2009-09-07 14:51:47'
 UPDATE berlin_temperatur_celsius SET timestamp = '200909-07 16:51:47' WHERE celcius = 10
durchführen kann.
4) UNIQUE. Trigger. Jeder Beliebtheitsrang darf nur einmal vorkommen. Verändere Deine
Datensätze in der Tabelle entsprechend, beginnend mit Beliebtheitsrang 1 (2, 3, 4… ohne
Lücke folgend).
 Verändere die Tabelle so, dass jeder Beliebtheitsrang nur einmal vorkommen kann
(Hinweis: UNIQUE).
 Du lernst Lily Allen/London oder Peter Fox/Berlin kennen. Füge sie/ihn auf Rang 2
nach unserem besten Freund/unserer besten Freundin (spielt ja auch ganz gut Gitarre).
Erstelle einen Trigger „beliebtheit_rang_verschieben“ mit dem folgendes
Statement ausfühbar wird, in dem alle Einträge, die den gleichen oder einen schlechteren Beliebtheitsrang haben einen um 1 schlechteren Rang erhalten:
INSERT INTO schlafplatz VALUES(schlafplatz_seq.nextval,
'Lily Allen', 'London', 1,2,1);
5) Mutating table. Heidelberg ist doof, deshalb willst Du alle Schlafplätze in Heidelberg aus
der Tabelle schlafplatz entfernen. Dabei sollen aber die Beliebtheitsränge nicht durcheinander geraten. Erstelle (wenn möglich erstmal ohne die Lösung aus dem Anhang) einen
Trigger „beliebtheit_neu_berechnen“, der nach dem Löschen des Datensatzes alle
weniger beliebten Schlafplätze um 1 beliebter macht.
Lösche alle Schlafplätze in Heidelberg. Du bekommst eine Fehlermeldung. Warum?
Lösche den Trigger.
Du schreibst die Daten erst mit einem Row-Trigger in eine temporäre Tabelle schlafplatz_beliebtheit_temp mit den Spalten schlafplatz_id und beliebt-
heit_rang. Du erstellst einen Row-Trigger beliebtheit_neu_berechnen1 mit dem
schlafplatz_id und beliebtheit_rang jedes gelöschten Datensatzes in die temporäre Tabelle geschrieben werden. Du erstellst einen Statement-Trigger beliebtheit_neu_berechnen2, der nach dem Löschen mit allen Datensätzen in schlafplatz_beliebtheit_temp die Beliebtheitsränge in schlafplatz entsprechend verschiebt. Lösche alle Schlafplätze in Heidelberg.
.
Übung PL/SQL II Anhang
CREATE OR REPLACE TRIGGER beliebtheit_neu_berechnen
AFTER DELETE ON schlafplatz
FOR EACH ROW
BEGIN
UPDATE schlafplatz
SET beliebtheit_rang=beliebtheit_rang-1
WHERE beliebtheit_rang > :new.beliebtheit_rang;
END;
DELETE FROM schlafplatz WHERE stadt = 'Heidelberg';
--SQL-Fehler: ORA-04091: Tabelle SENGER.SCHLAFPLATZ wird gerade geändert, Trigger/Funktion
sieht dies möglicherweise nicht
--ORA-06512: in "SENGER.BELIEBTHEIT_NEU_BERECHNEN", Zeile 2
--ORA-04088: Fehler bei der Ausführung von Trigger 'SENGER.BELIEBTHEIT_NEU_BERECHNEN'
--04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
--*Cause:
A trigger (or a user defined plsql function that is referenced in
-this statement) attempted to look at (or modify) a table that was
-in the middle of being modified by the statement which fired it.
--*Action:
Rewrite the trigger (or function) so it does not read that table.
DROP TRIGGER beliebtheit_neu_berechnen;
---------------------------------------------------------------------------------CREATE TABLE schlafplatz_beliebtheit_temp (
schlafplatz_id NUMBER,
geloeschter_beliebtheit_rang NUMBER
);
CREATE OR REPLACE TRIGGER beliebtheit_neu_berechnen1
AFTER DELETE ON schlafplatz
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('schlafplatz_id ' ||:old.schlafplatz_id || ',' ||
:old.beliebtheit_rang);
INSERT INTO schlafplatz_beliebtheit_temp
VALUES(:old.schlafplatz_id, :old.beliebtheit_rang);
END;
CREATE OR REPLACE TRIGGER beliebtheit_neu_berechnen2
AFTER DELETE ON schlafplatz
DECLARE
CURSOR cur_beliebtheit IS
SELECT *
FROM schlafplatz_beliebtheit_temp
ORDER BY geloeschter_beliebtheit_rang DESC;
BEGIN
FOR temp_daten IN cur_beliebtheit LOOP
UPDATE schlafplatz
SET beliebtheit_rang = beliebtheit_rang-1
WHERE beliebtheit_rang > temp_daten.geloeschter_beliebtheit_rang;
END LOOP;
DELETE
FROM schlafplatz_beliebtheit_temp;
END;
DELETE FROM schlafplatz WHERE stadt = 'Heidelberg';