Mutating-Table-Problem trotz "after update"-Klausel

MisterB

Neuer Benutzer
Beiträge
1
Hallo zusamen,

ich möchte eigentlich, auch wenn es sich für einen Neuling vielleicht nicht gehören mag, ohne Umschweife direkt zum Kern meines Problems kommen.

Ich habe eine Tabelle in welcher neben einigen Daten unter anderem zwei Datumsangaben für die Bildung eines Zeitraums gespeichert werden. Ferner ist in dieser Tabelle ein Fremdschlüssel gespeichert der auf eine Tuple in einer anderen Tabelle verweist, um so den Zeitraum z.B. einer Person zuordnen zu können. Nun möchte/muss ich mittels Trigger die Überschneidung zweier Zeiträume einer Person verhindern. Sprich, weder Anfangsdatum noch Enddatum einer Person, dürfen in einem bereits in der Tabelle existierenden Zeitraum der betreffenden Person liegen. Wenn also bereits ein Datensatz der Art "PersonA vom 10.Dezember.2011 bis 20.Dezember.2011" existiert darf ich keinen weiteren Datensatz mit PersonA einfügen, in welcher weder Anfangs- noch das Enddatum im Zeitraum vom 10.Dezember.2011 bis 20.Dezember.2011 liegen darf.

Meine eigene Recherche hat bisher zu Tage gefördert, dass ich keinen "before update"-Trigger verwenden darf. Ich habe auch verstanden das es etwas mit der Reihenfolgenunabhängigkeit zutun hat, wieso das nun so ist jedoch leider nicht. Ich habe jetzt auch desöfteren gelesen das Problem durch einen "after-update"-Trigger umgehen zu können, dies leider ohne bisherigen Erfolg.

Meine bisherige Idee bestand vor allem darin:
1) Cursor für die Datumsangaben einer Person anlegen.
2) Den Cursor durchlaufen un prüfen ob es eine Datumüberschneidung gibt.
3) Gibt es einer Überschneidung, rollback starten. (Noch ohne eigene Exception)

Das unter Insert-Statement gehört eigentlich nicht dazu, die Tabelle "trigger_log"ist rein experimenteller Natur. Dort speicher ich derzeit nur die :eek:ld... und :new... Variablen umzusehen, was wann wo drin steht.

Code:
create or replace trigger trgprojektarbeitafterupdate after update on projektarbeit
for each row
declare
  intprojektarbeit integer := 0;
  cursor crsprojektarbeit is select anfangsdatum, enddatum from projektarbeit
    where (matrikelnr = :new.matrikelnr or (thema like :new.thema and DozKn like :new.DozKn));
  datumanfang Projektarbeit.anfangsdatum%type;
  datumende projektarbeit.enddatum%type;
begin
  open crsprojektarbeit;
  loop
    fetch crsprojektarbeit into datumanfang, datumende;
    if ((:new.anfangsdatum between datumanfang and datumende) or (:new.enddatum between datumanfang and datumende)) then
      raise_application_error(-20001, 'FEHLER!!!');
    end if;
  end loop;
  close crsprojektarbeit;
 insert into trigger_log (oldanfangsdatum, newanfangsdatum, oldenddatum, newenddatum, oldmatrikelnr, newmatrikelnr, oldthema, newthema)
values (:old.anfangsdatum, :new.anfangsdatum, :old.enddatum, :new.enddatum, :old.matrikelnr, :new.matrikelnr, :old.thema, :new.thema);
end trgUpdateProjektarbeit;
/

Leider erhalte ich auch hier eine Mutating-Table-Warnung und weiss so langsam nicht mehr was ich tun soll noch warum.

Jemand nettes hier der mir meinen Fehler erklären mag? Wäre wirklich nett.

Schonmal Danke im voraus. :)
 
Werbung:

ukulele

Datenbank-Guru
Beiträge
4.702
Ich bin nicht so fit was Cursor angeht aber ich hätte es auch ohne gelöst. (Es sei angemerkt das ich nur MSSQL nutze)

In MSSQL gibt es die Tabellen INSERTED und DELETED, so das ich bei einem AFTER Trigger mir 4 Variablen aus INSERTED hole, DatensatzID, Benutzer, AnfangZeitraum, EndeZeitraum. Dann gucke ich mit EXISTS ob in der Tabelle, ausgenommen dem Datensatz mit der DatensatzID ein Zeitraum existiert, in dem entweder mein Anfangs- oder mein Enddatum liegt. ...WHERE ( AnfangZeitraum BETWEEN datum1 AND datum2 OR EndZeitraum BETWEEN datum1 AND datum2 ) AND ID != DatensatzID...

Wenn dem so ist, lösche ich den Datensatz mit der DatensatzID oder kürze ihn oder sonstwas.
 

PLSQL_SQL

SQL-Guru
Beiträge
176
Hy,

probier mal das hier:

Code:
create or replace trigger trgprojektarbeitafterupdate after update on projektarbeit
for each row
declare
  intprojektarbeit integer := 0;
  cursor crsprojektarbeit is select anfangsdatum, enddatum from projektarbeit
    where (matrikelnr = :new.matrikelnr or (thema like :new.thema and DozKn like :new.DozKn));
  datumanfang Projektarbeit.anfangsdatum%type;
  datumende projektarbeit.enddatum%type;
begin
  /*open crsprojektarbeit;
  loop
    fetch crsprojektarbeit into datumanfang, datumende;
--ODER
EXIT WHEN crsprojektarbeit%NOTFOUND;
 
    if ((:new.anfangsdatum between datumanfang and datumende) or (:new.enddatum between datumanfang and datumende)) then
      raise_application_error(-20001, 'FEHLER!!!');
    end if;
  end loop;
  close crsprojektarbeit;*/
  
  for r_shortcursor IN (
select anfangsdatum, enddatum 
from projektarbeit
where (matrikelnr = :new.matrikelnr 
or 
(thema like :new.thema 
and 
DozKn like :new.DozKn
)
 )
)
  loop
      if ((:new.anfangsdatum between datumanfang and datumende) 
  or 
 (:new.enddatum between datumanfang and datumende)
) 
then
raise_application_error(-20001, 'FEHLER!!!');
     end if;
  end loop;
 insert into trigger_log (oldanfangsdatum, newanfangsdatum, oldenddatum, newenddatum, oldmatrikelnr, newmatrikelnr, oldthema, newthema)
values (:old.anfangsdatum, :new.anfangsdatum, :old.enddatum, :new.enddatum, :old.matrikelnr, :new.matrikelnr, :old.thema, :new.thema);
end trgUpdateProjektarbeit;

Lg
 
Werbung:

akretschmer

Datenbank-Guru
Beiträge
9.846
Hallo zusamen,

ich möchte eigentlich, auch wenn es sich für einen Neuling vielleicht nicht gehören mag, ohne Umschweife direkt zum Kern meines Problems kommen.

Ich habe eine Tabelle in welcher neben einigen Daten unter anderem zwei Datumsangaben für die Bildung eines Zeitraums gespeichert werden. Ferner ist in dieser Tabelle ein Fremdschlüssel gespeichert der auf eine Tuple in einer anderen Tabelle verweist, um so den Zeitraum z.B. einer Person zuordnen zu können. Nun möchte/muss ich mittels Trigger die Überschneidung zweier Zeiträume einer Person verhindern. Sprich, weder Anfangsdatum noch Enddatum einer Person, dürfen in einem bereits in der Tabelle existierenden Zeitraum der betreffenden Person liegen. Wenn also bereits ein Datensatz der Art "PersonA vom 10.Dezember.2011 bis 20.Dezember.2011" existiert darf ich keinen weiteren Datensatz mit PersonA einfügen, in welcher weder Anfangs- noch das Enddatum im Zeitraum vom 10.Dezember.2011 bis 20.Dezember.2011 liegen darf.

Weil ich das grad sehe: dafür gibt es in PostgreSQL eine sehr elegante Lösung:

Code:
test=*# create table mister_b (person_id int, zeitraum daterange, exclude using gist (person_id with =, zeitraum with &&));
NOTICE:  CREATE TABLE / EXCLUDE will create implicit index "mister_b_person_id_zeitraum_excl" for table "mister_b"
CREATE TABLE
Time: 151,479 ms
test=*# insert into mister_b values (1, '[2013-08-01, 2013-08-10)');
INSERT 0 1
Time: 11,027 ms
test=*# insert into mister_b values (1, '[2013-09-01, 2013-09-10)');
INSERT 0 1
Time: 0,174 ms
test=*# insert into mister_b values (1, '[2013-08-09, 2013-08-12)');
ERROR:  conflicting key value violates exclusion constraint "mister_b_person_id_zeitraum_excl"
DETAIL:  Key (person_id, zeitraum)=(1, [2013-08-09,2013-08-12)) conflicts with existing key (person_id, zeitraum)=(1, [2013-08-01,2013-08-10)).
Time: 0,344 ms

Vielleicht hilft es ja, falls noch jemand über den Thread stolpert ...
 
Oben