martinbulinski.de

Das Foreign Key Constraint in ORACLE kennt nur zwei mögliche Erweiterungen: ON DELETE CASCADE oder ON DELETE SET NULL, um mit abhängigen Datensätzen bei einer Änderung des Master-Datensatzes umzugehen. Andere DB-Produkte (sogar MS ACCESS!) kennen auch ein ON UPDATE CASCADE, das manchmal recht praktisch ist, aber in ORACLE fehlt. Es ist allerdings mit Hilfe von Triggern und einer Package "nachbaubar".

Was macht ein ON UPDATE CASCADE

Diese Art von Fremdschlüssel soll dafür sorgen, dass bei einer Änderung des referenzierten Wertes in der Master-Tabelle diese Veränderung auch in den Child-Records nachgezogen wird.

Beispiel bei den Tabellen EMP und DEPT: Wird der Primärschlüssel der Tabelle DEPT verändert (Abteilungsnummer 10 ändert sich auf 15), dann sollen die Mitarbeiter aus der Abteilung 10 ebenfalls in der Abteilung 20 arbeiten, die Tabelle EMP muss also ebenfalls angepaßt werden.

Ohne ON UPDATE CASCADE kann diese Operation in ORACLE nur in Teilschritte zerlegt werden. (1) Wir fügen eine neue Abteilung 15 ein. (2) Wir updaten die Mitarbeiter aus Abteilung 10 und lassen sie nun in der neuen Abteilung 15 arbeiten. (3) Wir löschen die Abteilung 10, was geht, weil ja keine Mitarbeiter mehr tätig sind.

Die Alternative ist die Möglichkeit, die Constraint-Prüfung an das Ende der Transaktion zu legen. Diese Technik nennt man DEFERRABLE CONSTRAINTS.

Vorschlag zur Realisierung mit PL/SQL

Die hier beschrieben Variante arbeitet mit Triggern und Packages. Die Idee ist, mit Hilfe eines ROW-Triggers festzustellen, welche Abteilung gerade verändert wird. Der ROW-Trigger protokolliert den alten und neuen Abteilungsnummern-Wert in einer Package und stellt diese Informationen damit weiteren Trigger zur Verfügung. Weiterhin macht der ROW-Trigger die Veränderung des Primärschlüssels einfach rückgängig. Tatsächlich bleibt also alles beim alten.

Im Anschluss feuert ein STATEMENT-Trigger. Dieser schnappt sich die protokollierten Werte aus der Package, fügt eine neue Abteilung ein, zieht die Mitarbeiter aus der alten Abteilung in die neue um und löscht die alte Abteilung. Er macht also genau das, was wir sonst händisch erledigen sollten.

Hier ein Code-Vorschlag:

-- Simuliert ON UPDATE CASCADE auf dept und emp
create or replace package memo is
 alte_abtnr number;
 neue_abtnr number;
end;
/

create or replace trigger bu_dept 
before update of deptno on dept
for each row
begin
 memo.alte_abtnr := :old.deptno;
 memo.neue_abtnr := :new.deptno;
 :new.deptno := :old.deptno;
end;
/

create or replace trigger au_dept 
after update on dept 
declare
 neue_deptno number;
 alte_deptno number;
begin
 neue_deptno := memo.neue_Abtnr;
 alte_deptno := memo.alte_abtnr;
 insert into dept select neue_deptno, dname, loc 
 from dept where deptno = alte_deptno;
 update emp set deptno=neue_deptno 
 where deptno=alte_deptno;
 delete from dept where deptno=alte_deptno;
end;
/