Oracle 10g: via Trigger elegant mehrere Insert auslösen

[dexit2k]

Folgendes Problem:

ich habe eine Tabelle, auf der via ERP-System nen Artikel angelegt wird. Danach springt der Trigger an und schaut nach, ob dieser in den anderen Werken (Dantenpools) auch vorhanden ist und legt ihn ggf. dort auch an.

Das ganze funktioniert soweit und alles kein problem aber, was mich aufregt (ich progge erst seit 2 Wochen SQL und komme von C) ist das ich des : new nicht dem insert übergeben kann bzw dem Objekt Datensatz nicht das : new zuweisen kann. Ich hab bislang noch nicht begriffen was das new eigentlich ist, vom Bauchgefühl her würde ich sagen ne Referenz (in SQL heißt das glaube ich CURSOR) auf die geänderte Tabellenzeile.

Aktuell stehen da halt ca 200 Zeilen wie im unten aufgeführten Code, was mich nicht sonderlich glücklich macht.

Meine bisherigen Lösungsansätze:

A)

INSERT INTO A VALUES :new;

INSERT INTO S VALUES :new;

INSERT INTO D VALUES :new;

DECLARE

cursor CURSOR_AC is 

  SELECT distinct  column_name 

  FROM all_tab_columns 

  WHERE table_name = 'X';

...

BEGIN

...

  open CURSOR_AC;

    loop

      fetch CURSOR_AC into columnX;

      v_SQL :=  'Datensatz.' || columnX || ' := :NEW.' || columnX;

      EXECUTE IMMEDIATE v_SQL;

    end loop;

  close CURSOR_AC;

...

Ich bin mit meinem Latein am Ende und bin für jeden Tip Dankbar! Viele Grüße dexit2k

create or replace

TRIGGER AFTER_ACTION_X_TO_ASD

AFTER  UPDATE OR INSERT ON X

REFERENCING OLD AS OLD NEW AS NEW 

FOR EACH ROW


DECLARE

  Datensatz       X%ROWTYPE;

  temp              X%ROWTYPE;


  cursor CURSOR2 is SELECT * FROM A  WHERE artikel_nr = :NEW.artikel_nr;

  cursor CURSOR3 is SELECT * FROM S WHERE artikel_nr = :NEW.artikel_nr;

  cursor CURSOR4 is SELECT * FROM D WHERE artikel_nr = :NEW.artikel_nr;


BEGIN


[COLOR="Red"]----->Hier fehlt was<-------

oder  ~200 Spalten einzeln zuweisen a la:


Datensatz.xy := :new.xy;

----->Hier fehlt was<-------[/COLOR]


  -- Verarbeitung Werk2

  OPEN  CURSOR2; 

    FETCH CURSOR2 INTO temp;

    IF CURSOR2%NOTFOUND THEN

      INSERT INTO A VALUES Datensatz;

    END IF;

  CLOSE CURSOR2;


  -- Verarbeitung Werk3

  OPEN CURSOR3;

    FETCH CURSOR3 INTO temp;

    IF CURSOR3%NOTFOUND THEN

      INSERT INTO S VALUES Datensatz;

    END IF;

  CLOSE CURSOR3;


  -- Verarbeitung Werk4

  OPEN  CURSOR4; 

    FETCH CURSOR4 INTO temp;

    IF CURSOR4%NOTFOUND THEN 

      INSERT INTO D VALUES Datensatz;

    end if;

  CLOSE CURSOR4;

END;

[dr.dimitri]

Hi,

ich würde von dem Triggeransatz weggehen, denn es ist im allgemeinen schlechter Stil fachliche Logik in einem Trigger zu verstecken.

Wenn es nicht anders geht, dann würde ich es (exemplarisch) so machen:

MERGE INTO A 

USING (SELECT :new.spalte1,:new.spalte2,... FROM DUAL) sub

ON(a.artikel_nr=sub.spalteX)

WHEN NOT MATCHED THEN

INSERT INTO A (spalte1,spalte2,...) VALUES(sub.spalte1,sub.spalte2,...)

Ich hab bislang noch nicht begriffen was das new eigentlich ist, vom Bauchgefühl her würde ich sagen ne Referenz (in SQL heißt das glaube ich CURSOR) auf die geänderte Tabellenzeile.

Eine Referenz und Cursur sind nicht identisch. Ein Cursor ist eine Ergebnismenge, eine gefüllte Datenstruktur.

new und old sind Strukturen, die nur in Triggern verfügbar sind und den Zugriff auf die entsprechenden Datensatz ermöglichen. Damit bist Du jedoch in PL/SQL (die prozedurale Programmiersprache). Du kannst das nicht einfach so mit SQL (die mengenorientierte Abfragesprache) verbinden.

(ich progge erst seit 2 Wochen SQL und komme von C)

Du programmierst in PL/SQL In SQL kann man ebensowenig programmieren wie in HTML

Dim

[dexit2k]

Hi Dimitri!

Danke für die Antwort. Der Trigger muss sein, da ich schlecht auf den Code des ERP-Systems zugreifen kann .

Wenn ich deinen Code richtig verstehe, muss ich alle 197 Spaltennamen 3x eingeben -> dann wäre ich wieder bei ~50 Seiten (jede spalte ne eigene Zeile, zwecks Übersicht).

Genau das will ich ja vermeiden.

ich würde von dem Triggeransatz weggehen, denn es ist im allgemeinen schlechter Stil fachliche Logik in einem Trigger zu verstecken.

Wenn es nicht anders geht, dann würde ich es (exemplarisch) so machen:

Code:

MERGE INTO A 

USING (SELECT :new.spalte1,:new.spalte2,... FROM DUAL) sub

ON(a.artikel_nr=sub.spalteX)

WHEN NOT MATCHED THEN

INSERT INTO A (spalte1,spalte2,...) VALUES(sub.spalte1,sub.spalte2,...)

Mir ist aber noch ein Gedanke gekommen: Da es sich um einen After Trigger handelt sollte der Datensatz ja schon in die Tabelle geschrieben sein. Könnte man da nicht nen select auf die tabelle machen? Leider wirft mit Oracle nen Systemfehler, wenn ich den Cursor öffne. Es funktionier übrigens auch nicht wenn ich nur ein update des Datensatzes mache, folglich muss es ja grundsätzlich nicht gehen, ich frag mich nur warum? Bsp.:

Datensatz       X%ROWTYPE;

cursor CURSOR_AC is SELECT * FROM X WHERE artikel_nr = :NEW.artikel_nr;

...

open CURSOR_AC;

   fetch CURSOR_AC into Datensatz;

    ....

       INSERT INTO A VALUES Datensatz;

    ....

       INSERT INTO S VALUES Datensatz;

    ....

       INSERT INTO D VALUES Datensatz;

    ....

close CURSOR_AC;

...   

[dr.dimitri]

Naja, dann generierst dir das Statement eben

declare

 l_stmt varchar2(5000);

 l_columns varchar2(2400);

 l_insertColumns varchar2(2400);

begin

 l_stmt:='MERGE INTO ###TAB### a USING(SELECT '; 

 for i in(select column_name from user_tab_cols where table_name='X' order by column_id) loop

  l_columns:=l_columns||':new.'||i.column_name||',';

  l_insertColumns:=l_insertColumns||'b.'||i.column_name||',';

 end loop;

 l_columns:=substr(l_columns,1,length(l_columns)-1); --Letztes , abschneiden.

 l_insertColumns:=substr(l_insertColumns,1,length(l_insertColumns)-1); --Letztes , abschneiden.

 l_stmt:=l_stmt||l_columns||' FROM src) b ON(a.artikel_nr=b.artikel_nr) WHEN NOT MATCHED THEN INSERT VALUES('||l_insertColumns||')'; 

 dbms_output.put_line(l_stmt); --Nur zu Testzwecken ausgeben


execute immediate replace(l_stmt,'###TAB###','A');

execute immediate replace(l_stmt,'###TAB###','S');

execute immediate replace(l_stmt,'###TAB###','D');

end;

/

Allerdings wird das auf einem stark ausgelastetem System sicherlich nicht zu Geschwindgkeitsrekorden führen.

Eine Möglichkeit wäre noch, dass man das SQL einmalig generiert und dann in einer Tabelle abspeichert. Bei Strukturänderungen muss das natürlich nachgezogen werden.

Könnte man da nicht nen select auf die tabelle machen? Leider wirft mit Oracle nen Systemfehler, wenn ich den Cursor öffne. Es funktionier übrigens auch nicht wenn ich nur ein update des Datensatzes mache, folglich muss es ja grundsätzlich nicht gehen, ich frag mich nur warum? Bsp.:

Richtig. ORA-04091 table XY is mutating, trigger/function may not see it.

Damit verhindert Oracle, dass Du in eine noch nicht fertige Transaktion hinsiehst. Oracle garantiert nämlich, dass eine Transaktion atomar ist, sie also ganz oder garnicht durchgeführt wird. Selektierst Du in einem Trigger auf die Tabelle die zu dem Trigger gehört, dann würdest Du quasi einen Zwischenstand sehen, der aber nicht sichtbar sein darf - daher dieser Fehler.

Dim

[dexit2k]

Hi Dimitri,

Dein Vorschlag klingt recht cool. Ich hab es versucht zum laufen zu bringen aber leider klapps ned ganz. Das Problem liegt hier:

l_stmt:=l_stmt||l_columns||' FROM src) b ON(a.artikel_nr=b.artikel_nr)

FROM src ist keine gültige Tabelle. Wenn ich src durch X ersetze geht es zwar, jedoch wird dann b nicht als Bezeichner erkannt ( bei a funktionierts).

Wenn des mit merge into ned klappt mach ichs auch wieder mit den cursorn aber diesmal dynamisch, wie im merge into bsp.

Da bei uns die Perfomence 0 Problem ist und in den nächsten par Jahren auch ausreichen wird, spiel ich mit dem Gandanken, den Code (egal ob dann mit merge into oder cursorn) 1:1 so im Trigger stehen zu lassen. Richtig formatiert ist das ganze Kompakt, Übersichtlich und leicht zu ändern - genau das was ich erreichen wollte. Sollte Performance dennoch nen Problem werden, wanderts dann halt in ne Tabelle.

Mein Problem ist somit fast gelößt (hab schon zuvor in 2 anderen Foren versucht das Problem zu lösen). Ich würde mich aber dennoch freuen wenn das mit merge into klappen würde.

Schonmal vielen Dank für deine Hilfe!

Viele Grüße

Dexit2k

[dr.dimitri]

FROM src ist keine gültige Tabelle.

Stimmt, das ist noch ein Überbleibsel von meinem Test

Hat den X (ihr habt Tabellennamen...) eine Spalte namens artikel_nr?

Dim

[dexit2k]

ja x hat: x_artikel_nr

die feinen namen rühren von testtabellen her, da mich mein chef ned auf dem produktiv system arbeiten läßt - warum nur :hells:

aber ganz anderes problem. execute geht ned, des benutzt varchar2 als parameter und ich bin mit allen 200 : new.XYZ über der maximalen größe von varchar2 und der schneidet den string ab. -> geht so ned

zumindest denk ich mir des, ich kann ja leider ned im trigger debuggen aber in ner prozedur hab ichs nachgestellt und da war columns voll. also hab ich erst mit clob experementiert, dann mit columns1 und columns2 und die im execute per || verknüpft aber immer das selbe problem...

[dr.dimitri]

über der maximalen größe von varchar2 und der schneidet den string ab

In PL/SQL kann ein VARCHAR2 32767 Byte lang sein (nicht nur 4000 wie in SQL). Selbst wenn deine 200 Spalten die maximal zulässige Länge hätten, würdest nicht mal in die Nähe dieser Obergrenze kommen.

Dim

[dbwizard]

In PL/SQL kann ein VARCHAR2 32767 Byte lang sein (nicht nur 4000 wie in SQL). Selbst wenn deine 200 Spalten die maximal zulässige Länge hätten, würdest nicht mal in die Nähe dieser Obergrenze kommen.

Dim

- Ansonsten könntest du auch ins Auge zu fassen, die Parameter in eine Collection zu packen, ist eh eleganter um nachher das Statement aufzubauen

Gruss

[dexit2k]

Ich weis ned woher der Fehler kommt und hab deswegen versucht das Ganze erstmal so simpel wie möglich zu machen:

ich hab mir ne tabelle (marceltest) mit einer spalte (c1, typ clob) angelegt.

hab die dann copiert und diese dann marceltest3 genannt.

ich rufen dann folgendes auf:

INSERT INTO marceltest VALUES ('test1218' );

im trigger wird ledeglich folgendes aufgerufen:

create or replace

TRIGGER test

AFTER  UPDATE OR INSERT ON marceltest

REFERENCING OLD AS OLD NEW AS NEW 

FOR EACH ROW 


begin

  execute immediate  'MERGE INTO marceltest3 a USING (select :new.C1 from marceltest)  b ON ( a.C1 = b.C1 ) WHEN NOT MATCHED THEN INSERT VALUES ( b.C1 )';

END; 

Es wird dann der Fehler "not all variables bound" gemeldet. ich schätze mal dass folgendes ned richtig ist:

USING (select :new.C1 from marceltest)

[dr.dimitri]

Es wird dann der Fehler "not all variables bound" gemeldet. ich schätze mal dass folgendes ned richtig ist:

Ach verd... ist ja dynamisches SQL. Alles was mit : beginnt, wird als Bindvariable angesehen, die mit einem Wert in der USING Klausel gefüllt werden muss. USING ist Teil von execute immediate und kann daher nicht generiert wird.

Mein Rat: Hau den ganzen Schmarrn aus dem Trigger raus und logge statt dessen nur den PK in eine Zwischentabelle. Dann legst Du dir per dbms_scheduler einen Job an, der, sagen wir alle 5 Sekunden läuft und eine PL/SQL Prozedur aufruft.

Diese Prodezur setzt die Session zuerst in den serializable mode und kopiert anschließend anhand der gespeicherten PKs die Werte aus der Originaltabelle in die anderen Tabellen. Danach löscht Du die Zwischentabelle und das Spiel beginnt von vorne.

Das hat den Vorteil, dass es a) einfacher und für den User viel schneller ist, da das Kopieren asynchron läuft.

Dim

[dexit2k]

Puh - Wenigstens bin ich ned zu doof. Beruhigend das es allein vom Konzept her ned geht.

Ich werd deinen Vorschlag mit dem Async Insert mal testen. Dürfte wesentlich einfacher sein, da ich mich ned mit dem ***** : new rumschlagen muss. Dazu kann ich aber erst Mitte nächste Woche was sagen, da ich mich jetzt erstmal mit Cristal Reports rumschlagen muss...

Naja thats life - Hab zu meinem Chef schon gesagt, im Studium hab ich bei SQL ned sonderlich aufgepasst, war haltn Pflichfach. Meine Gedanke war nur - Im Auto und embedded systems Bereich braucht man kein SQL. Jaja jetzt arbeite ich als ERP Fuzzi und VB Muggl.

Trotzdem vielen Dank für Deine Mühe und nen schönes Wochenende!

[dr.dimitri]

Na du kannst ja auch in Oracle mit C/C++ programmieren.

Mach nur nicht den fatalen Fehler, Aufgaben die man besser mit reinem SQL erledigt mit PL/SQL oder C nachzuprogrammieren.

Dim

[dexit2k]

Es funktioniert endlich! Aber auf die Lösung via Merge wäre ich nie gekommen, ehrlichgesagt versteh ich ned ganz wieso des überhaupt funktioniert:

merge into WM71W02T.PAWS dest

        using(select col1,col2,col3,...     -- Select * geht auch

        from WM71W01T.PAWS where paws_artikel_nr in(select ArtikelNr from temp100 where progress='N')) src

        ON (src.PAWS_ARTIKEL_NR = dest.PAWS_ARTIKEL_NR)

        when matched then 

          UPDATE SET 

            ARTIKELART=(SELECT ARTIKELART FROM WM71W01T.PAWS WHERE WM71W01T.PAWS.paws_artikel_nr = ArtikelNr ),

            BESCHAFFUNGSKENNER=(SELECT BESCHAFFUNGSKENNER FROM WM71W01T.PAWS WHERE WM71W01T.PAWS.paws_artikel_nr = ArtikelNr)

            WHERE EXISTS (SELECT * FROM WM71W02T.PAWS WHERE WM71W02T.PAWS.paws_artikel_nr = ArtikelNr )

        when not matched then 

          INSERT (col1,col2,col3,...)

          values (src.col1,src.col2,src.col3,...);

ON (src.PAWS_ARTIKEL_NR = dest.PAWS_ARTIKEL_NR)

Als ich deine Musterlösung angepasst hab kam die Fehlermeldung, dass ON Fehlt. Ich hab jetzt einfach mal die PKs verglichen. Da es funktioniert scheint es richtig zu sein? Hätte da noch etwas Klärungebedarf:

using(select col1,col2,col3,...

   from WM71W01T.PAWS where paws_artikel_nr

   in(select ArtikelNr from temp100 where progress='N'))

In TEMP100 stecken mehrere Datenzätzen, die diese Bedinung erfüllen. Werden a nicht alle diese Datensätze zurückgegeben? Bedeutet dies, das des merge mit einem aufruf alle Datensätze auf einmal ändert? oder immer nur einen? wenn es alle auf einmal ändert, würde es sinn manen, "WM71W02T.PAWS.paws_artikel_nr = ArtikelNr" durch "WM71W02T.PAWS.paws_artikel_nr = (select ArtikelNr from temp100 where progress='N')" zu ersetzen? edit: Wieso kamm man beim Merge Insert geigentlich nicht folgendes machen?

INSERT (SELECT * FROM WM71W01T.PAWS WHERE WM71W01T.PAWS.paws_artikel_nr = ArtikelNr );

Bearbeitet 19. März 2009 von dexit2k

[dr.dimitri]

In TEMP100 stecken mehrere Datenzätzen, die diese Bedinung erfüllen. Werden a nicht alle diese Datensätze zurückgegeben? Bedeutet dies, das des merge mit einem aufruf alle Datensätze auf einmal ändert? oder immer nur einen?

SQL ist grundsätzlich immer mengenorientiert. In einem Statement können also 10 oder auch 10 Mrd Sätze geändert werden.

Wieso kamm man beim Merge Insert geigentlich nicht folgendes machen?

Naja Du kannst ja mal die Oracle Entwickler fragen Grundsätzlich wäre das aber schlechter Stil, denn was passiert, wenn die beiden Tabellen plötzlich nicht mehr identisch sind? Was passiert wenn die Spalten unterschiedliche Reihenfolgen haben aber rein technisch zueinander passen würden? Man kann auch in einem Programm alle Variablen a1 a2 a3 etc. nennen aber man macht es nicht. Und der MERGE Befehl erlaubt es eben nicht einmal, sondern Du musst explizit angeben welche Spalte in welche geschrieben werden soll. Macht das ganze übersichtlicher und sicherer auch wenns erst mal ein bissl mehr Schreibarbeit ist.

Dim

[dexit2k]

SQL ist grundsätzlich immer mengenorientiert. In einem Statement können also 10 oder auch 10 Mrd Sätze geändert werden.

dann würde ich ja mit:

ARTIKELART=(SELECT ARTIKELART FROM WM71W01T.PAWS WHERE WM71W01T.PAWS.paws_artikel_nr = ArtikelNr ),

alle Artikel, die in Temp100 auf 'N' stehen auf die Artikelart setzten, welche vom Datensatz mit der paws_artikel_nr, die in der variablen ArtikelNr enthalten ist, oder? -wird langsam komplizier :uli

Das wäre ja falsch!

Also nochmal für dummis (mich):

Ich mach oben nen select und bekomm alle Datensätze, die einem Update bedürfen. Jetzt mal als Beispiel 3 Datensätze:

Werk 100:

ArtikelNr:     Beschafungskenner:         Artikelart:

1234-1                F                    Teil

1234-2                F                    Produkt

1234-3                E                    Baugruppe

Dann kommt das Update und möchte bei diesen Datensätzen die Artikelart und den Beschafungskenner setzen.

Wie selle ich dann sicher, dass zur jeweiligen Zeile auch der passende Beschaffungskenn und Artikelart zugewiesen wird? Nicht das in Werk 200 zu ArtikelNr 1234-1 die Werte E und Baugruppe zugewiesen werden.

[dr.dimitri]

dann würde ich ja mit:

ARTIKELART=(SELECT ARTIKELART FROM WM71W01T.PAWS WHERE WM71W01T.PAWS.paws_artikel_nr = ArtikelNr ),

alle Artikel, die in Temp100 auf 'N' stehen auf die Artikelart setzten, welche vom Datensatz mit der paws_artikel_nr, die in der variablen ArtikelNr enthalten ist, oder? -wird langsam komplizier

Das wäre ja falsch!

Diesr Update würde fehlschlagen, wenn das select mehr als eine Zeile liefert.

Wie selle ich dann sicher, dass zur jeweiligen Zeile auch der passende Beschaffungskenn und Artikelart zugewiesen wird?

Über die Joinbedingung:

ON (src.PAWS_ARTIKEL_NR = dest.PAWS_ARTIKEL_NR)

Hier werden die beiden Mengen gemachted - bedeutet implizit natürlich, dass artikel_nr ein passender Zugriffsschlüssel sein muss (=eindeutig).

Dim