Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

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

Dieses Thema im Forum "Oracle" wurde erstellt von MisterB, 12 Dezember 2011.

  1. MisterB

    MisterB Neuer Benutzer

    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. :)
     
  2. ukulele

    ukulele Datenbank-Guru

    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.
     
  3. PLSQL_SQL

    PLSQL_SQL Datenbank-Guru

    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
     
  4. akretschmer

    akretschmer Datenbank-Guru

    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 ...
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden