Oracle Trigger

MisterB

Mitglied
Hallo zusammen,

ich habe eine Frage bzgl. der Arbeitsweise von Triggern bei Oracle Datenbanken, genau Oracle XE 11g Datenbanken.

Ich soll aus Übungsgründen für die Uni eine DB aufsetzen mit welcher Projektarbeiten verwaltet werden können. Neben einigen Tabellen, habe ich unteranderem die Tabellen Dozent, Student und Projektarbeit.
Die Tabelle Projektarbeit verweist mit ihren Fremdschlüsseln auf die Tabellen Dozent und und Student. Dann gestalte ich mittels Constraints einige der Integritätsbedingungen, soweit auch alles kein Problem.
Nun lautet eine der Bedingungen das ein Student zu einem Zeitpunkt immer nur eine Projektarbeit anfertigen kann. In einer kurzen Rücksprache mit meinem Dozenten, sagte er mir das ich diesen Punkt noch nicht bearbeiten kann, da wir im Stoff noch nicht weit genug sind und wir diese Anforderungen mit einfachen Constraints so nicht implementieren können. Sprich das Thema Trigger kommt erst noch, wir sollten nur erkennen das wir es mit Constraints nicht machen können.

Nun möchte ich einfach etwas vorarbeiten und es mit Triggern machen.
In der Tabelle Projektarbeit sind zwei Datumsangaben enthalten, ein Anfangsdatum sowie ein Enddatum. Ich möchte mit dem Trigger nun das Einfügen und Ändern von Datensätzen je nach Auswertung verhindern oder erlauben. Sprich ich muss vor dem Einfügen/Ändern prüfen ob der Student derzeit schon eine Projektarbeit anfertigt.
Dafür prüfe ich ob das Anfangsdatum und/oder Enddatum zwischen zwei Datumangaben liegt, und ob es ein Anfangsdatum ohne Endatum gibt und das einzufügen Anfangsdatum/Enddatum größer ist. Ich prüfe also jegeliche Art von Überschneidung der Intervalle. Sollte sich so eine Überschneidung finden lassen, muss das insert abgebrochen werden.
Beim Update-Statement ist es so ziemlich das selbe.

So sieht mein Trigger derzeit aus
SQL:
create or replace trigger trgprojektarbeit 
before insert or update on projektarbeit
for each row
  begin
    declare Intprojektarbeit integer;
    begin
      Intprojektarbeit := 0;
      select count (*) into Intprojektarbeit
        from projektarbeit
        where ((matrikelnr = :new.matrikelnr)
          and ((:new.anfangsdatum between anfangsdatum and enddatum)
          or (:new.enddatum between anfangsdatum and enddatum)
          or (anfangsdatum is not null and enddatum is null and (:new.anfangsdatum >= anfangsdatum or :new.enddatum >= anfangsdatum))));
      if inserting and (intprojektarbeit > 0) then
        raise_application_error(-200001, 'Der Datensatz konnte aufgrund einer Datumsüberschneidung nicht EINGEFÜGT werden!');
      end if;
      if updating and (intprojektarbeit > 0) then
        raise_application_error(-200001, 'Der Datensatz konnte aufgrund einer Datumsüberschneidung nicht GEÄNDERT werden!');
      end if;
    end;
  end;
/

Im Select prüfe ich die Datumsangaben, natürlich nur für den betreffenen Studenten. Anschließend schreibe ich den Wert der Menge in die Variabel Intprojektarbeit und prüfe im Anschluss ob es mehr als 0 Einträge gibt.

Ein Insert in der Form
SQL:
insert into projektarbeit (projektid, anfangsdatum, enddatum, thema, note, matrikelnr, dozkn) values (5, null, null, 'NoSQL', null, null, 'Ac');
funktioniert auch tadellos.

Ich Trage in der Tabelle Projektarbeit mit diesem Statement ein Thema ein welchem vom Dozeten Ac angeboten wird.

Mit diesem Update nun
SQL:
update projektarbeit set matrikelnr = 4711, anfangsdatum = '11-SEP-2011' where thema like 'NoSQL';
soll eingetragen werden das der Student mit der Martikelnummer 4711 am 11.11.11 die Projektarbeit angenommen hat und dieses Thema bearbeiten wird. Ohne Trigger funktioniert das auch.

Leider erhalte ich mit Trigger die folgende Fehlermeldung
Fehlerbericht:
SQL-Fehler: ORA-04091: table SYSTEM.PROJEKTARBEIT is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TRGPROJEKTARBEIT", line 5
ORA-04088: error during execution of trigger 'SYSTEM.TRGPROJEKTARBEIT'
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

Die Fehlermeldung widerspricht eigentlich meinem Verständnis vom Nutzen eines Triggers. Ich darf nicht auf die Tabelle welche den Trigger ausgelöst hat, bzw. auf die Tabelle welche durch das auslösende Update-Statement verändert wurde, zugreifen? :autsch:
Ich dachte bisher eigentlich das ich gerade das damit machen soll. Die Integritätsregeln einer Tabelle noch besser kontrollieren können.

Wäre wirklich nett wenn mich jemand erleuchten würde.

Danke schonmal :)
 

Deros

Bekanntes Mitglied
du kannst halt auf keinen Datensatz zugreifen, den du gerade änderst. Die einfachste Art dieses Problem zu umgehen ist halt ein after_statement-trigger
 

MisterB

Mitglied
Zunächst einmal Danke für die schnelle Antwort.:)

Irgendwie erschließt sich mir Sinn und Zweck des Triggers jetzt noch viel weniger. o_O
Ich dachte der "before" Modifikator sagt aus, dass der Trigger vorher ausgeführt werden soll, und nicht während die Änderung in die DB geschrieben wird.
 

Deros

Bekanntes Mitglied
puhhh also vielleicht noch zur Erklärung, das Problem liegt im "Grundsatz der Reihenfolgeunabhängigkeit" bei Relationalendatenbanken, sprich theoretisch muss es egal sein in welcher reihenfolge deine statements abgefeuert werden. Standard SQL verbietet grundsätzlich DML-Anweisungen in before-triggern, oracle ist da etwas "offener" aber du kannst halt nicht auf die Tabelle zugreifen die du gerade ändern willst. Aber du könntest halt die Id's in Hilfstabellen ablegen und das ganze in einem After-Trigger abarbeiten.
 

Ähnliche Java Themen

Neue Themen


Oben