Musterlösung - home.hs

Übung PL/SQL Trigger Lösungen
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).
SELECT sysdate FROM dual
-- Die "dual" Tabelle wird in Oracle gebraucht, wenn das Statement logisch keinen Tabellenna
-- men hat.
ALTER session SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
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.
CREATE TABLE stadt_temperatur_fahrenheit (
timestamp DATE,
stadt VARCHAR2(60),
fahrenheit NUMBER(4,1),
PRIMARY KEY (timestamp, stadt)
);
CREATE OR REPLACE
VIEW berlin_temperatur_celsius (timestamp, celcius)
AS
SELECT timestamp, (fahrenheit-32) * 5.0/9.0
FROM stadt_temperatur_fahrenheit
WHERE stadt='berlin';
CREATE OR REPLACE TRIGGER berlin_temperatur_hinzufuegen
INSTEAD OF INSERT ON berlin_temperatur_celsius
FOR EACH ROW
BEGIN
INSERT INTO stadt_temperatur_fahrenheit
VALUES(:new.timestamp, 'berlin', :new.celcius * 9.0/5.0 + 32);
END;
SHOW ERRORS;
INSERT INTO stadt_temperatur_fahrenheit
VALUES('2009-09-15 11:00:00', 'berlin', 57);
INSERT INTO berlin_temperatur_celsius
VALUES('2009-09-14 11:00:00', 10.2);
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.
CREATE OR REPLACE TRIGGER berlin_temperatur_aendern
INSTEAD OF UPDATE ON berlin_temperatur_celsius
FOR EACH ROW
BEGIN
UPDATE stadt_temperatur_fahrenheit
SET
fahrenheit = :new.celcius * 9.0/5.0 + 32, timestamp = :new.timestamp
WHERE
timestamp = :old.timestamp AND
stadt = 'berlin';
END;
SHOW ERRORS;
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 = '2009-09-07 16:51:47'
WHERE celcius = 10
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);
ALTER TABLE schlafplatz ADD CONSTRAINT beliebtheit_rang_unique UNIQUE(beliebtheit_rang);
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Lily Allen',
'London',
1,
2,
1);
-- SQL-Fehler: ORA-00001: Unique Constraint (SENGER.BELIEBTHEIT_RANG_UNIQUE) verletzt
CREATE OR REPLACE TRIGGER beliebtheit_rang_verschieben
BEFORE INSERT ON schlafplatz
FOR EACH ROW
BEGIN
UPDATE schlafplatz
SET beliebtheit_rang = beliebtheit_rang+1
WHERE beliebtheit_rang >= :new.beliebtheit_rang;
END;
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 beliebtheit_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.
Siehe Aufgabenblatt.
Übung PL/SQL Trigger 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';