Announcement

**defo** · 19.01.2015, 16:41

Ohne die Angabe der Trigger kann man da wohl kaum helfen.
Zunächst mal würde ich untersuchen, ob die Trigger an den entscheidenden Stellen mit

ld/:new Notation die Werte abgreifen und an die Funktion übergeben.
Ansonsten gab's/gibt laut meiner Erinnerung mal sowas, das sich Mutating Tables Problem nannte/nennt. Weiß aus dem Kopf nicht, ob das versionsspezifisch war.
Dann fällt mir noch ein, dass ein solcher Effekt evtl. auch durch gut gemeinte, aber unsinnige Commits entstehen kann. Sowas hat m.E. innerhalb solcher Transaktionen nicht zu suchen und kann auch unbeabsichtigt ausgelöst werden (Alter statements, Truncate, ..)

**Urwi** · 20.01.2015, 08:16

Hallo Defo,

Danke für Deine Antwort!

Hier der Trigger:

Code:

CREATE OR REPLACE TRIGGER trg_suche
 AFTER 
 INSERT OR DELETE OR UPDATE OF name
 ON Tabelle_B
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
  sText VARCHAR2( 1000 );
  
BEGIN

   sText := fn_Suche( :NEW.id, NULL, NULL, NULL );

END;
/

Die Function sieht im Prinzip so aus:

Code:

FUNCTION fn_Suche(  pId IN NUMBER, ...
  RETURN  VarChar2 IS       
    sText   VarChar2(1000) := ''; 
    PRAGMA AUTONOMOUS_TRANSACTION;
    ...
    CURSOR cur IS
           SELECT name
             FROM Tabelle_B
            WHERE Tabelle_A_Id = pId;  
BEGIN 
     FOR curs IN cur LOOP
         sText := sText || ' ' || name;
     END LOOP;  
     
     UPDATE Tabelle_A
        SET suche = sText
      WHERE Id = pId ;
     COMMIT;
   RETURN sText;
END;

Da ich in der Testphase bin, verwende ich den SQLNavigator um die Daten zu ändern. Der Trigger wird ausgelöst, die Funktion ordnungsgemäß ausgeführt, aber es werden die "alten" Daten ausgelesen.
Da Du meinst, dass eventuell unsinnige "Commit" daran Schuld sein könnten, habe ich versucht dieses in der Function wegzulassen. Dann erhalte ich eine Fehlermeldung. Mit funktioniert es.

Das von Dir angesprochene "Mutating Tables Problem" scheint hier nicht zu greifen. Eine entsprechende Fehlermeldung bei der Ausführung des Triggers kommt nicht.

vg
Urwi

**Wernfried** · 20.01.2015, 09:16

Ob das PRAGMA AUTONOMOUS_TRANSACTION bzw. COMMIT nötig ist kommt auf deine Anforderungen an. Soll Tabelle_A upgedated werden wenn nach dem auslösenden UPDATE/INSERT/DELETE ein ROLLBACK gemacht wird? Wenn ja sind AUTONOMOUS_TRANSACTION und COMMIT nötig, andernfalls musst du sie (beide) weglassen.

Bei "Mutating Tables Problem" gibt es eine (wenig bekannte) Ausnahme, siehe Oracle Dokumentation Using Triggers: "There is an exception to this restriction: For a single row INSERT, constraining tables are mutating for AFTER row triggers, but not for BEFORE row triggers. INSERT statements that involve more than one row, such as INSERT INTO Emp_tab SELECT..., are not considered single row inserts, even if they only result in one row being inserted. "

Auf dieser Seite haben sie sich genauer mit dem Problem auseinander gesetzt: Why am I NOT getting a mutating table error in trigger?

Gruss

**Urwi** · 20.01.2015, 09:27

Hallo Wernfried!

Genau, Tab A soll ein Update nach der Änderung der Tab B erhalten.

Mein Halbwissen in Oracle reicht leider nicht aus, um das "Mutating Tables Problem" gänzlich zu verstehen. Nach meinem Verständnis dürfte dieser Fehler keine Auswirkung auf mein Beispiel haben, da das Update ja auf eine "fremde" Tabelle angewendet wird.

Für mich sieht es so aus, als ob der Trigger mit seiner Funktion vor dem Update von Tab A durchgeführt wird. Daher ist noch der alte Datensatz vorhanden!

Danke
Urwi

**defo** · 20.01.2015, 09:30

Originally posted by Urwi View Post

Da ich in der Testphase bin, verwende ich den SQLNavigator um die Daten zu ändern. Der Trigger wird ausgelöst, die Funktion ordnungsgemäß ausgeführt, aber es werden die "alten" Daten ausgelesen.
Da Du meinst, dass eventuell unsinnige "Commit" daran Schuld sein könnten, habe ich versucht dieses in der Function wegzulassen. Dann erhalte ich eine Fehlermeldung. Mit funktioniert es.
Das von Dir angesprochene "Mutating Tables Problem" scheint hier nicht zu greifen. Eine entsprechende Fehlermeldung bei der Ausführung des Triggers kommt nicht.

Hallo Urwi,
es ist vollkommen egal, womit Du die Daten änderst, wäre es anders, würde ich Oracle sofort wegwerfen.
Die "alten" Daten werden vermutlich gelesen, weil Du eine autonome Transaktion in die Funktion eingebaut hast. Die hat hier und sonst meist nichts zu suchen. Baust Du sie aus, erhälst Du ..
.. "eine Fehlermeldung"..
Wie lautet die Fehlermeldung?
ORA 08091 ?
Das ist das Mutating Tables Problem.

Zum Sortieren:
Deine Statements sehen so aus, als ob Du entweder noch sehr in der Testphase bist oder mit hoher Geheimhaltungsstufe arbeitest.
Was soll die automome Transaktion?
Was soll das referencing old new?
Was soll die Sammlung von Werten nach einem Delete?
Was soll das Update von Tabelle A nach einem Insert auf B, woher sollen die Referenz ID kommen?
Was soll der ganze Mechanismus?

Mir ist es in der letzten Decade nicht gelungen, einen solchen Fehler zu produzieren. Dabei habe ich es natürlich nicht drauf angelegt, aber auch nicht extra vermieden, aber das muss nichts heißen.

Also Du hast m.E. 2 Möglichkeiten:
a) Du behälst Deinen Mechanismus und bläst ihn so auf, dass Du das Mutating Table Problem umgehst (natürlich ohne autonome Transaktionen)
b) Du überdenkst, was Du eigentlich erreichen möchtest und suchst nach anderen Strategien

**jum** · 20.01.2015, 09:34

Schön wäre es gewesen, Du hättest uns einen Testfall gebaut, so musste ich das erledigen.
Technisch klappt Dein Trigger - ob diese Lösung fachlich sinnvoll ist, steht auf einem anderen Blatt.
Ich teile hier die Bedenken von defo (inzwischen gelesen)!

Code:

DROP   TABLE tmp_Tabelle_A;

CREATE TABLE tmp_Tabelle_A (
  id   NUMBER,
  name VARCHAR2(4000)); 

INSERT INTO tmp_Tabelle_A (id, name) VALUES (1, '###');  
  

DROP   TABLE tmp_Tabelle_B;

CREATE TABLE tmp_Tabelle_B (
  id   NUMBER,
  aId  NUMBER,
  name VARCHAR2(4000)); 

INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (7001, 1, 'Eins');  
INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (8001, 1, 'Uno');  
INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (9001, 1, 'One');  


CREATE OR REPLACE FUNCTION fn_Suche(  pId IN NUMBER)
  RETURN  VarChar2 IS       
    sText   VarChar2(1000) := ''; 
    PRAGMA AUTONOMOUS_TRANSACTION;
    CURSOR cur IS
           SELECT name 
             FROM tmp_Tabelle_B
            WHERE aId = pId;  
BEGIN 
     FOR curs IN cur LOOP
         sText := sText || ' ' || curs.name;
     END LOOP;  
     
     UPDATE tmp_Tabelle_A
        SET name = sText
      WHERE Id = pId ;
     COMMIT;

   RETURN sText;
END;


CREATE OR REPLACE TRIGGER tmp_trg_suche
 AFTER 
 INSERT OR DELETE OR UPDATE OF name
 ON tmp_Tabelle_B
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
  sText VARCHAR2( 1000 );
  
BEGIN
   sText := fn_Suche( :NEW.id);
END;

SELECT name vorher FROM tmp_tabelle_A;

VORHER                                                                          
--------------------------------------------------------------------------------
###                                                                             
1 row selected.

SELECT fn_suche(1) FROM dual;

FN_SUCHE(1)                                                                     
--------------------------------------------------------------------------------
 Eins Uno One                                                                   
1 row selected.

SELECT name nachher FROM tmp_tabelle_A;

NACHHER                                                                         
--------------------------------------------------------------------------------
 Eins Uno One                                                                   
1 row selected.

**defo** · 20.01.2015, 09:45

äh, aber Du erzeugst den Trigger erst nach den Inserts?
Technisch gesehen klappt also nur die Funktion (unter gewissen Umständen) oder?

PS zu meinem vorigen Beitrag:

Originally posted by defo View Post

Mir ist es in der letzten Decade nicht gelungen, einen solchen Fehler zu produzieren. Dabei habe ich es natürlich nicht drauf angelegt, aber auch nicht extra vermieden, aber das muss nichts heißen.

Nicht dass ich hier falsch verstanden werde und wegen arroganten Verhaltens eine schlechte Reputation bekomme...

Die Tatsache, dass ich mich an das Problem erinnere, zeigt ja, dass ich es auch mal hatte.
Die ganze Konstellation deutet auf ein konzeptionelles Problem hin, redundante Datenhaltung, glaub ich. Das sollte man vermeiden.

**defo** · 20.01.2015, 10:00

Hab das während meiner Antwort übersehen.

Originally posted by Urwi View Post

Nach meinem Verständnis dürfte dieser Fehler keine Auswirkung auf mein Beispiel haben, da das Update ja auf eine "fremde" Tabelle angewendet wird.

Du verwendest in der Funktion ein Select auf Table B, das ist glaube ich das Problem.

**Urwi** · 20.01.2015, 10:05

Hallo Defo,

ich weiß, dass es vollkommen egal ist womit ich die Daten ändere. Ich wollte damit nur unterstreichen, dass das nicht aus einem Programmcode kommt, wo eventuell Deine Vermutung mit unsinnigen Befehlen passieren könnten.

Wenn ich das PRAGMA oder Commit ausbaue bekomme ich folgenden Fehler:
ORA-00900: Ungültige SQL-Anweisung
ORA-06512: in ".........", Zeile 6
ORA-04088: Fehler bei der Ausführung von Trigger '.........'
ORA-04091: Tabelle .........wird gerade geändert, Trigger/Funktion sieht dies möglicherweise nicht
ORA-06512: in ".........", Zeile 28
ORA-06512: in ".........", Zeile 95

"Deine Statements sehen so aus, als ob Du entweder noch sehr in der Testphase bist oder mit hoher Geheimhaltungsstufe arbeitest."
Das liegt daran, dass die Funktion noch viel anderen Programmcode zur Aufbereitung von Daten enthält und ich niemanden mit unseren Standards volllabern will. Deshalb eine vereinfachte Darstellung der Funktion.

"Was soll die automome Transaktion?"
Ich war oder bin der Meinung, damit die oben genannte Fehlermeldung zu umgehen.

"Was soll das referencing old new?"
Meines Wissens nach, kann ich mit new und old auf den aktuelle Datensatz zugreifen. Da brauche ich z.B. die aktuelle Id um die Tab_B auszulesen

"Was soll die Sammlung von Werten nach einem Delete?"
Das Suchfeld besteht nicht nur aus Tab A und B sondern aus vielen Tabellen. Beim Delete sollen eben die Werte des gelöschten Datensatzes nicht mehr im Suchfeld sein

"Was soll das Update von Tabelle A nach einem Insert auf B, woher sollen die Referenz ID kommen?"
Ja, aus :New.Tabelle_A_Id und wird in die Function per pId übergeben (wie übrigens noch eine Reihe von Parametern die ich nicht aufgeführt habe)

Was soll der ganze Mechanismus?
Ich habe verschiedene Tabellen (hier symbolisch Tabelle_B), aus denen bestimmte Werte in einem String (Tabelle_A.Suche) gesammelt werden sollen um sie später schnell auswerten zu können.

Danke
Ewald

**Urwi** · 20.01.2015, 10:09

Originally posted by defo View Post

Hab das während meiner Antwort übersehen.

Du verwendest in der Funktion ein Select auf Table B, das ist glaube ich das Problem.

Genau, um alle Datensätze die die Tabelle_A_Id betreffen auslesen und aufbereiten zu können.

vg

**Wernfried** · 20.01.2015, 10:37

Originally posted by Urwi View Post

Mein Halbwissen in Oracle reicht leider nicht aus, um das "Mutating Tables Problem" gänzlich zu verstehen. Nach meinem Verständnis dürfte dieser Fehler keine Auswirkung auf mein Beispiel haben, da das Update ja auf eine "fremde" Tabelle angewendet wird.

Für mich sieht es so aus, als ob der Trigger mit seiner Funktion vor dem Update von Tab A durchgeführt wird. Daher ist noch der alte Datensatz vorhanden!

Das "Mutating Tables Problem" bedeutet vereinfacht, dass du eine Tabelle selektieren möchtest während sie verändert wird - das geht nicht.

In der Funktion "fn_Suche" wird der alte Datensatz gelesen, weil du ein PRAGMA AUTONOMOUS_TRANSACTION machst.
PRAGMA AUTONOMOUS_TRANSACTION bedeutet im Prinzip dass du für diese Funktion eine neue Oracle Session erzeugst.
Wenn sich ein anderer User auf die DB einloggt, kann er deine Änderungen auch nicht sehen bevor du ein COMMIT gemacht hast. Der autonomen Funktion "fn_Suche" ergeht es genauso.

Gruss

**Wernfried** · 20.01.2015, 10:44

Originally posted by Urwi View Post

"Was soll das referencing old new?"
Meines Wissens nach, kann ich mit new und old auf den aktuelle Datensatz zugreifen. Da brauche ich z.B. die aktuelle Id um die Tab_B auszulesen

Simmt aber OLD und NEW sind default, die kannst du weglassen.

Sinnvoller(?) wäre z.B.

Code:

REFERENCING OLD AS ALT NEW AS NEU
...
BEGIN
   sText := fn_Suche( :NEU.id);
END;

Gruss

**Urwi** · 20.01.2015, 10:45

Originally posted by jum View Post

Schön wäre es gewesen, Du hättest uns einen Testfall gebaut, so musste ich das erledigen.
Technisch klappt Dein Trigger - ob diese Lösung fachlich sinnvoll ist, steht auf einem anderen Blatt.
Ich teile hier die Bedenken von defo (inzwischen gelesen)!

Code:

DROP   TABLE tmp_Tabelle_A;

CREATE TABLE tmp_Tabelle_A (
  id   NUMBER,
  name VARCHAR2(4000)); 

INSERT INTO tmp_Tabelle_A (id, name) VALUES (1, '###');  
  

DROP   TABLE tmp_Tabelle_B;

CREATE TABLE tmp_Tabelle_B (
  id   NUMBER,
  aId  NUMBER,
  name VARCHAR2(4000)); 

INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (7001, 1, 'Eins');  
INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (8001, 1, 'Uno');  
INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (9001, 1, 'One');  


CREATE OR REPLACE FUNCTION fn_Suche(  pId IN NUMBER)
  RETURN  VarChar2 IS       
    sText   VarChar2(1000) := ''; 
    PRAGMA AUTONOMOUS_TRANSACTION;
    CURSOR cur IS
           SELECT name 
             FROM tmp_Tabelle_B
            WHERE aId = pId;  
BEGIN 
     FOR curs IN cur LOOP
         sText := sText || ' ' || curs.name;
     END LOOP;  
     
     UPDATE tmp_Tabelle_A
        SET name = sText
      WHERE Id = pId ;
     COMMIT;

   RETURN sText;
END;


CREATE OR REPLACE TRIGGER tmp_trg_suche
 AFTER 
 INSERT OR DELETE OR UPDATE OF name
 ON tmp_Tabelle_B
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
  sText VARCHAR2( 1000 );
  
BEGIN
   sText := fn_Suche( :NEW.id);
END;

SELECT name vorher FROM tmp_tabelle_A;

VORHER                                                                          
--------------------------------------------------------------------------------
###                                                                             
1 row selected.

SELECT fn_suche(1) FROM dual;

FN_SUCHE(1)                                                                     
--------------------------------------------------------------------------------
 Eins Uno One                                                                   
1 row selected.

SELECT name nachher FROM tmp_tabelle_A;

NACHHER                                                                         
--------------------------------------------------------------------------------
 Eins Uno One                                                                   
1 row selected.

Hallo jam,

Du hast recht, ich dachte zuerst, da kann es nur eine Kleinigkeit haben, deshalb habe ich gar nicht dran gedacht, ein Beispiel hinzuzufügen. Interessanterweise habe ich Deinen Code probiert, aber: Trigger wird ausgelöst, Funktion wird ausgeführt, aber in Tabelle_A steht nichts drinnen!

Ich habe den Code ein klein wenig abegändert.

Code:

CREATE TABLE tmp_Tabelle_A (
  id   NUMBER,
  name VARCHAR2(40),
  suche VARCHAR2(1000) );

INSERT INTO tmp_Tabelle_A (id, name) VALUES (1, '###');


CREATE TABLE tmp_Tabelle_B (
  id   NUMBER,
  aId  NUMBER,
  name VARCHAR2(100));

INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (7001, 1, 'Eins');
INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (8001, 1, 'Uno');
INSERT INTO tmp_Tabelle_B (id, aid, name) VALUES (9001, 1, 'One');

CREATE OR REPLACE FUNCTION fn_Suche(  pId IN NUMBER)
  RETURN  VarChar2 IS
    sText   VarChar2(1000) := '';
    PRAGMA AUTONOMOUS_TRANSACTION;
    CURSOR cur IS
           SELECT name
             FROM tmp_Tabelle_B
            WHERE aId = pId;
BEGIN
     FOR curs IN cur LOOP
         sText := sText || ' ' || curs.name;
     END LOOP;

     UPDATE tmp_Tabelle_A
        SET suche = sText
      WHERE Id = pId ;
     COMMIT;

   RETURN sText;
END;

CREATE OR REPLACE TRIGGER TMP_TRG_SUCHE
 AFTER 
 INSERT OR DELETE OR UPDATE OF NAME
 ON TMP_TABELLE_B
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
  sText VARCHAR2( 1000 );

BEGIN
   sText := fn_Suche( :NEW.id);
END;
/

SELECT * FROM tmp_Tabelle_B;
SELECT * FROM tmp_Tabelle_a;

Steht bei Dir etwas in Tabelle_A_Suche drinnen?

Danke
Urwi

**Urwi** · 20.01.2015, 10:48

Originally posted by Wernfried View Post

Simmt aber OLD und NEW sind default, die kannst du weglassen.

Sinnvoller(?) wäre z.B.

Code:

REFERENCING OLD AS ALT NEW AS NEU
...
BEGIN
   sText := fn_Suche( :NEU.id);
END;

Gruss

Wenn ich das richtig verstehe, handelt es sich hier um eine "kosmetische Operation" oder könnte ich mit new/old in einen Fehler aufen?

vg

Announcement

Datenänderung in Trigger noch nicht fertig

Datenänderung in Trigger noch nicht fertig

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment