# Oracle Trigger - After Insert - Feld aktualisieren



## warface (8. Juni 2017)

Hallo zusammen,

ich möchte einen Trigger erstellen der bei neu angelegten Datensätzen anhand eines Kriteriums und dem Herstelldatum ein Mindesthaltbarkeitsdatum hinterlegt.

hat jemand einen Tipp für mich 


```
create Trigger MHD
    AFTER Insert
    ON Artikel_Charge
        for each row
        Begin
            Update
                Artikel_Charge
            set
                Artikel_Charge.Mindesthaltbarkeit = (CASE
                            when Artikel.art IN ('1','9')   THEN to_char(Artikel_Charge.Herstelldatum+700, 'YYYY-MM-DD')
                            when Artikel.art IN ('2','8')   THEN to_char(Artikel_Charge.Herstelldatum+1000, 'YYYY-MM-DD')
                            when Artikel.art IN ('3','A')   THEN to_char(Artikel_Charge.Herstelldatum+1400, 'YYYY-MM-DD')
                            when Artikel.art IN ('4','5','6','7')   THEN to_char(Artikel_Charge.Herstelldatum+2000, 'YYYY-MM-DD')
                            else 'YYYY-MM-DD' end)
            where
                Artikel_Charge.ID=new.ID
                END
```


----------



## Yaslaw (9. Juni 2017)

Da bietet sich doch der Befor INSERT an.
Dann musst du nicht im Nachhinein korrigieren.

Warum ist das Datum als String definiert? Und warum ist die art als String definiert? Das sind Zahlen und Datum!

```
create or replace trigger TBI_ARTIKEL_CHARGE
before insert
    on ARTIKEL_CHARGE
    for each row
begin
    case :new.art
        case 1 THEN :new.Mindesthaltbarkeit = :new.herstelldatum +700;
        case 2 THEN :new.Mindesthaltbarkeit = :new.herstelldatum +1000;
        case 8 THEN :new.Mindesthaltbarkeit = :new.herstelldatum +1000;
        case 9 THEN :new.Mindesthaltbarkeit = :new.herstelldatum +700;
        -- TODO weitere Fälle ausprogrammieren
    end case;

end;
/
```


----------



## Biber3 (10. Juni 2017)

Na, da fehlt aber nach eine Ergänzung.

@Yaslaw Alles richtig, alles gut.
Aber: dieses MHD wird bei jedem neuen Satz  gesetzt, sogar dann, wenn ein neuer Satz mit einem explizit angegebenen eingefügt werden soll.
Daraus schliesse ich, dass es immer  automatisch berechnet werden soll und kann.

Dann muss  also auch ein Updatetrigger dazu für Änderung der  Felder, aus denen berechnet wird ODER besser noch, es  wird ganz verzichtet auf die redundante Speicherung.
Das MHD lässt sich immer in einer Abfrage bilden.

Grüße 
Biber


----------



## warface (12. Juni 2017)

Danke für die Unterstützung 

@Yaslaw ich habe mal deinen Vorschlag übernommen.
Leider klappt es nicht irgendwie komm ich nicht drauf was.


```
CREATE OR REPLACE TRIGGER DEMO.CHARGE_MHD
BEFORE INSERT
    ON DEMO.Artikel_Charge
    FOR each ROW
BEGIN
    CASE DEMO.Artikel.Art
        CASE 1 THEN :NEW.MHD = :NEW.datherst +700;
        CASE 2 THEN :NEW.MHD = :NEW.datherst +1065;
        CASE 3 THEN :NEW.MHD = :NEW.datherst +1430;
        CASE 4 THEN :NEW.MHD = :NEW.datherst +1796;
        CASE 5 THEN :NEW.MHD = :NEW.datherst +1796;
        CASE 6 THEN :NEW.MHD = :NEW.datherst +1796;
        CASE 7 THEN :NEW.MHD = :NEW.datherst +1796;
        CASE 8 THEN :NEW.MHD = :NEW.datherst +1065;
        CASE 9 THEN :NEW.MHD = :NEW.datherst +700;
        CASE A THEN :NEW.MHD = :NEW.herstelldatum +1430;
     
    END CASE;

END;
```


----------



## Yaslaw (12. Juni 2017)

Was heisst das? Wie irgendwie?
Achsbruch, Vollbrand, Weltuntergang? Trump wurde gewählt?

Was sicher ist, das A sollte als String stehen. Und da das Feld Art ein String zu sein scheint (wegen dem A), dann sollten alle Anderen Cases auch in ' gesetzt werden.

Bitte in Zukunft aussagekräftige Problembeschreibungen. Fehlermeldungen, Fehlverhalten etc.


----------



## warface (12. Juni 2017)

@Yaslaw 
Das ist mein Select-Befehl mit dem ich es vorher getestet hab, dieser funktioniert.

```
select
Artikel.Art,
CASE
        WHEN Artikel.Art = '1' THEN Artikel_Charge.datherst +700
        WHEN Artikel.Art = '2' THEN Artikel_Charge.datherst +1065
        WHEN Artikel.Art = '3' THEN Artikel_Charge.datherst +1430
        WHEN Artikel.Art = '4' THEN Artikel_Charge.datherst +1796
        WHEN Artikel.Art = '5' THEN Artikel_Charge.datherst +1796
        WHEN Artikel.Art = '6' THEN Artikel_Charge.datherst +1796
        WHEN Artikel.Art = '7' THEN Artikel_Charge.datherst +1796
        WHEN Artikel.Art = '8' THEN Artikel_Charge.datherst +1065
        WHEN Artikel.Art = '9' THEN Artikel_Charge.datherst +700
        WHEN Artikel.Art = 'A' THEN Artikel_Charge.datherst +1430
        END as MHD,
   
Artikel_charge.*
from DEMO.Artikel, DEMO.Artikel_Charge

where
Artikel.ArtikelID = Artikel_Charge.ArtikelID
```


das ist mein aktueller Trigger

```
CREATE OR REPLACE TRIGGER DEMO.CHARGE_MHD
BEFORE INSERT
    ON DEMO.Artikel_Charge
    FOR each ROW
BEGIN
    CASE
        WHEN DEMO.Artikel.Art = '1' THEN :NEW.MHD := :NEW.datherst +700;
        WHEN DEMO.Artikel.Art = '2' THEN :NEW.MHD := :NEW.datherst +1065;
        WHEN DEMO.Artikel.Art = '3' THEN :NEW.MHD := :NEW.datherst +1430;
        WHEN DEMO.Artikel.Art = '4' THEN :NEW.MHD := :NEW.datherst +1796;
        WHEN DEMO.Artikel.Art = '5' THEN :NEW.MHD := :NEW.datherst +1796;
        WHEN DEMO.Artikel.Art = '6' THEN :NEW.MHD := :NEW.datherst +1796;
        WHEN DEMO.Artikel.Art = '7' THEN :NEW.MHD := :NEW.datherst +1796;
        WHEN DEMO.Artikel.Art = '8' THEN :NEW.MHD := :NEW.datherst +1065;
        WHEN DEMO.Artikel.Art = '9' THEN :NEW.MHD := :NEW.datherst +700;
        WHEN DEMO.Artikel.Art = 'A' THEN :NEW.MHD := :NEW.datherst +1430;
    
    END CASE;
END;
```

die Fehlermeldung lautet 

Fehler(1,5): PL/SQL: Statement ignored
Fehler(2,25): PLS-00357: Table-, View- oder Sequence-Referenz 'DEMO.ARTIKEL.ART' in diesem Kontext nicht gültig


----------



## Yaslaw (12. Juni 2017)

Wozu ein = nach dem case?
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems004.htm

Mir ist aber noch ein Fehlerunterlaufen, da ich seit etwa 3 Jahren kein PL/SQL mehr programmiert habe
Die Zuweisung von Werten ist natürlich mit := und nicht mit =

```
CREATE OR REPLACE TRIGGER DEMO.CHARGE_MHD
BEFORE INSERT
    ON DEMO.Artikel_Charge
    FOR each ROW
BEGIN
    CASE DEMO.Artikel.Art
        CASE '1' THEN :NEW.MHD := :NEW.datherst +700;
        CASE '2' THEN :NEW.MHD := :NEW.datherst +1065;
        CASE '3' THEN :NEW.MHD := :NEW.datherst +1430;
        CASE '4' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '5' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '6' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '7' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '8' THEN :NEW.MHD := :NEW.datherst +1065;
        CASE '9' THEN :NEW.MHD := :NEW.datherst +700;
        CASE 'A' THEN :NEW.MHD := :NEW.herstelldatum +1430;
    END CASE;
END;
```


----------



## Biber3 (12. Juni 2017)

Moin warface,

na ja, die Sysnatx im Trigger sollte auch eher so lauten:


```
CREATE OR REPLACE TRIGGER DEMO.CHARGE_MHD

  BEFORE INSERT
     ON DEMO.Artikel_Charge
     FOR each ROW
     BEGIN

       CASE :new.Art


        WHEN '1' THEN :NEW.MHD = :NEW.datherst +700;
        WHEN '2' THEN :NEW.MHD = :NEW.datherst +1065;
   --  ....
   END CASE;

END;
```

Und ja, die CASE-Syntax im Test-SELECT ist eine andere.

P.S. hast du meinen Kommentar gelesen, dass eigentlich die redundante Speicherung des MHD für die Tonne ist, da du es a) ja jederzeit berechnen kannst b) der Benutzer es ja doch nicht ändern kann, weil es immer automatisch gemäß Schema F berechnet wird?

Grüße
Biber


----------



## warface (12. Juni 2017)

das Feld "Art" ist in der Tabelle Artikel_Charge nicht vorhanden, daher wird NEW.Art nicht funktionieren ;-)
Ich greife auf die Artikel-Tabelle zu um herauszufinden welche Art der Artikel hat, dann berechne ich anhand der Art das MHD.


----------



## warface (12. Juni 2017)

@Biber3 danke für deine Unterstützung.
Ich deinen Vorschlag gelesen und finde den Sinnvoll, aber ich will erst einmal den "before insert"-Trigger hinkriegen.

ich kenne mich eigentlich ganz gut mit SQL aus, aber mit Triggern hab ich noch nicht ganz so oft gearbeitet.
Leider konnte ich auch keine Vergleichbares Beispiel finden wo ich ableiten kann wie man das umsetzen kann.


@Yaslaw bei deinem Beispiel bekomme ich den folgenden Fehler

Fehler(2,9): PLS-00103: Fand das Symbol "CASE" als eines der folgenden erwartet wurde: . ( * @ % & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset Das Symbol "when wurde vor "CASE" eingefügt, um fortzufahren.
Fehler(3,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(4,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(5,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(6,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(7,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(8,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(9,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(10,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(11,18): PLS-00103: Fand das Symbol "THEN" als eines der folgenden erwartet wurde: * & = - + < / > at in is mod remainder not rem when <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset
Fehler(12,5): PLS-00103: Fand das Symbol "CASE" als eines der folgenden erwartet wurde: ;  current delete exists prior  Das Symbol "CASE" wurde ignoriert.


----------



## Yaslaw (12. Juni 2017)

Oh, Art hat eine andere Quelle....

```
CREATE OR REPLACE TRIGGER DEMO.CHARGE_MHD
BEFORE INSERT
    ON DEMO.Artikel_Charge
    FOR each ROW
DECLARE
    artikel_art Artikel.Art%type;
BEGIN

    SELECT art INTO artikel_art
    FROM Artikel
    WHERE Artikel.ArtikelID = :new.ArtikelID;

    CASE artikel_art
        CASE '1' THEN :NEW.MHD := :NEW.datherst +700;
        CASE '2' THEN :NEW.MHD := :NEW.datherst +1065;
        CASE '3' THEN :NEW.MHD := :NEW.datherst +1430;
        CASE '4' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '5' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '6' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '7' THEN :NEW.MHD := :NEW.datherst +1796;
        CASE '8' THEN :NEW.MHD := :NEW.datherst +1065;
        CASE '9' THEN :NEW.MHD := :NEW.datherst +700;
        CASE 'A' THEN :NEW.MHD := :NEW.herstelldatum +1430;
    END CASE;
END;
```


----------



## warface (12. Juni 2017)

@Yaslaw vielen Dank für deine Hilfe.

Hier der fertige Code
Ich habe mich aber für When statt Case entschieden, da Case nicht klappte 
Und bei der Berechnung des MHD hab ich mich für Monate statt Tage entschieden.


```
CREATE OR REPLACE TRIGGER DEMO.CHARGE_MHD
BEFORE INSERT
    ON DEMO.Artikel_Charge
    FOR each ROW
DECLARE
    artikel_art Artikel.Art%TYPE;
BEGIN
    SELECT art INTO artikel_art
    FROM Artikel
    WHERE Artikel.ArtikelID = :NEW.ArtikelID;
    CASE
    WHEN Artikel_Art = '1' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 23 );
        WHEN Artikel_Art = '2' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 35 );
        WHEN Artikel_Art = '3' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 47 );
        WHEN Artikel_Art = '4' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 59 );
        WHEN Artikel_Art = '5' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 59 );
        WHEN Artikel_Art = '6' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 59 );
        WHEN Artikel_Art = '7' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 59 );
        WHEN Artikel_Art = '8' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 35 );
        WHEN Artikel_Art = '9' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 23 );
        WHEN Artikel_Art = 'A' THEN :NEW.MHD := add_months( to_date(:NEW.datherst,'DD.MM.YYYY'), 47 );

    END CASE;

END;
```


----------



## Biber3 (12. Juni 2017)

Moin warface,

so weit, so gut, aber...

Egal,  ob du jetzt erwägst, das MHD über Trigger zu errechnen oder immer in Views/Abfragen zu ermitteln:
Der Algorithmus deutet stark darauf hin, dass du dir das Leben einfacher machen solltest mit einer einer neuen Hilfstabelle mit den zwei Spalten "Artikel_Art" und "MHDDauerInMonaten" (oder so ähnlich).
Bzw. deine bestimmt vorhandene Stammdaten-Tabelle "ArtikelArten" um dieses Feld "MHDDauerInMonaten" erweitern.
Besser noch mit zwei weiteren zusätzlichen Feldern "gültig_von"/Gültig_bis"

Denn diesen Algorithmus in einem Trigger zu vergraben... das wird auf Dauer nicht praktikabel.

Spätestens wenn sich bei Artikel_Art 'A' die DauerInMonaten von 47 auf 48 ändert, werden deine Datensätze widersprüchlich.

Aber was soll's... bis hierhin war es ein amüsanter Thread.
Ab jetzt stehen eher fachlich zu beurteilende Anforderungen im Raum und nicht mehr handwerkliche.
Da können wir weniger unterstützen, denke ich.

Grüße
Biber


----------

