Trigger -> Mutierende Tabelle

[Mediax]

Hallo alle zusammen,
ich sitze gerade an einer Datenbankaufgabe und komme seit vielen Stunden einfach nicht vorran. Es geht eigentlich nur um einen kleinen Trigger, der die Tabelle ORD aktualisieren soll, wenn ein ITEM gelöscht, hinzugefügt oder geändert wird. Und zwar enthalten alle ITEMS das Feld ITEMTOT, was den gesamtpreis der ITEMs beinhaltet (menge*preis). der Trigger soll alle Itemtotalpreise der gleichen ORDID zusammenzählen, damit der Preis der Order insgesamt immer korrekt ist.

Hier die Tabellen
ITEM----------------------------------
ORDID number (4) PK, FK
ITEMID number (4) PK
PRODID number (6)
ACTUALPRICE number (8,2)
QTY number (8)
ITEMTOT number (8,2)

ORD-----------------------------------
ORDID number(4) PK
ORDERDATE date
COMMPLAN varchar (1)
CUSTID number (5)
SHIPDATE date
TOTAL number (2)

Mein Trigger, der mutating table exception auslöst:

SHOW ERRORS;
CREATE OR REPLACE TRIGGER orderTotal
AFTER UPDATE OR INSERT OR DELETE ON ITEM
FOR EACH ROW
DECLARE
ORDERTOTAL NUMBER;
BEGIN
	IF UPDATING THEN
	select sum(itemtot) INTO ORDERTOTAL from item where ordid=:NEW.ordid;
	UPDATE ord
	SET TOTAL = ORDERTOTAL WHERE ordid=:NEW.ordid;	
	END IF;
	IF DELETING THEN
	select sum(itemtot) INTO ORDERTOTAL from item where ordid=:OLD.ordid;
	UPDATE ord
	SET TOTAL = ORDERTOTAL WHERE ordid=:OLD.ordid;
	END IF;
	IF INSERTING THEN
	select sum(itemtot) INTO ORDERTOTAL from item where ordid=:NEW.ordid;
	UPDATE ord
	SET TOTAL = ORDERTOTAL WHERE ordid=:NEW.ordid;	
	END IF;
END;

Ich weiss, warum der "mutating table" Fehler auftritt, aber ich habe keinen Ansatz wie ich diesen beheben kann. Ich habe schon ein wenig im Internet gesucht und viel von Packages, Hilfstabellen und Hilfstriggern gelesen. Leider weiss ich trotzdem nicht, wie ich meinen Trigger umschreiben kann, so das er funktioniert
Ich bin für jede Hilfe sehr dankbar,
MFG Mediax

 

[Exceptionfault]

Eine Möglichkeit ein "MUTATING TABLE" in den Griff zu bekommen ist die Anweisung

(...)
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
(...)

Dies bewirkt dass dein Trigger in einer eigenen Transaktion läuft. Man sollte aber sicher wissen ob es "datentechnisch" ein Problem geben könnte wenn man die Transaktionen trennt. Also was passiert z.B. wenn der Trigger auf ein Fehler läuft ? Soll dann die komplette Änderung an der ITEM Tabelle auch zurückgerollt werden ?

 

[Mediax]

Vielen Dank für deinen Tipp. Ich habe diesen in meinem Trigger umgesetzt, leider kam es danach sofort zu einer neuen Fehlermeldung. Kann man mit " PRAGMA AUTONOMOUS_TRANSACTION;" tatsächlich das Problem beheben ? Ich habe schon so viele Lösungen über das "mutating table" Problem aber es scheint nicht auf mein Problem zu passen. Oder ich habe einfach Verständnisprobleme....
Mfg Mediax

 

[Mediax]

Ich habe es nun folgendermaßen versucht:

SHOW ERRORS;
CREATE OR REPLACE TRIGGER orderTotal
AFTER UPDATE OR INSERT OR DELETE ON ITEM
FOR EACH ROW
DECLARE
ORDERTOTAL NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	IF UPDATING THEN
	select sum(itemtot) INTO ORDERTOTAL from item where ordid=:NEW.ordid;
	UPDATE ord
	SET TOTAL = ORDERTOTAL WHERE ordid=:NEW.ordid;	
                --codify data record
                COMMIT;
	END IF;
	IF DELETING THEN
	select sum(itemtot) INTO ORDERTOTAL from item where ordid=:OLD.ordid;
	UPDATE ord
	SET TOTAL = ORDERTOTAL WHERE ordid=:OLD.ordid;
                --codify data record
                COMMIT;
	END IF;
	IF INSERTING THEN
	select sum(itemtot) INTO ORDERTOTAL from item where ordid=:NEW.ordid;
	UPDATE ord
	SET TOTAL = ORDERTOTAL WHERE ordid=:NEW.ordid;	
               --Codify data record
               COMMIT;
	END IF;
END;

Ich bekomme nun keine Fehlermeldungen mehr, allerdings funktioniert der Update Befehl überhaupt nicht. Ich weiss momentan wirklich nicht, was ich modifizieren muss, damit der Trigger endlich anständig läuft.

 

[Nico Graichen]

Hi

Ist zwar Offtopic, aber trotzdem die Frage:
Wieso speicherst du das in der Datenbank?
Die Werte kannst du mit einem Query berechnen, wenn du sie brauchst und musst nicht jedes Mal über alle Datensätz laufen, wenn sich was ändert.

 

[Mediax]

Das ist in der Aufgabe definiert, das so in der Datenbank abzuspeichern. Eine Vorgabe, hätte glaube ich sowieso die ganze Datenbank anders angelegt wenn ich sie erstellt hätte...

 

[Exceptionfault]

Wie wärs denn hiermit ?

CREATE TABLE ORDERS (
   orderid  NUMBER,
   summe    NUMBER
)
/

INSERT INTO ORDERS VALUES ( 1, 0 );
INSERT INTO ORDERS VALUES ( 2, 0 );
INSERT INTO ORDERS VALUES ( 3, 0 );

CREATE TABLE ITEMS (
   orderid   NUMBER,
   itemid    NUMBER,
   amount    NUMBER,
   price     NUMBER,
   total     NUMBER	
)
/

CREATE OR REPLACE PROCEDURE recalc_order ( n_orderid IN ORDERS.ORDERID%TYPE,
                                           n_modify IN ORDERS.SUMME%TYPE )
IS
BEGIN

   UPDATE   ORDERS
   SET      SUMME   = SUMME + n_modify
   WHERE    orderid = n_orderid;

EXCEPTION
   WHEN OTHERS THEN 
      RAISE;
END;
/

CREATE OR REPLACE TRIGGER modify_order
  BEFORE UPDATE OR INSERT OR DELETE ON ITEMS
  FOR EACH ROW
DECLARE
BEGIN

   IF INSERTING THEN
      :new.total := :new.price * :new.amount;
      recalc_order( :new.ORDERID, :new.total );

   ELSIF UPDATING THEN
      :new.total := :new.price * :new.amount;
      recalc_order( :new.ORDERID, :new.total - :old.total );
				
   ELSIF DELETING THEN
      recalc_order( :old.ORDERID, :old.total * -1 );
   END IF;

END;
/

Mit folgendem Ergebnis:

   ORDERID      SUMME
---------- ----------
         1          0
         2          0
         3          0

SQL> insert into items values ( 1, 25, 2, 15, 0 );

1 Zeile wurde erstellt.

SQL> select * from orders;

   ORDERID      SUMME
---------- ----------
         1         30
         2          0
         3          0

SQL> insert into items values ( 2, 35, 1, 7.5, 0 );

1 Zeile wurde erstellt.

SQL> select * from orders;

   ORDERID      SUMME
---------- ----------
         1         30
         2        7,5
         3          0

SQL> update items set amount = 10 where itemid=35;

1 Zeile wurde aktualisiert.

SQL> select * from orders;

   ORDERID      SUMME
---------- ----------
         1         30
         2         75
         3          0

SQL> delete from items;

1 Zeile wurde geloscht.

   ORDERID      SUMME
---------- ----------
         1          0
         2          0
         3          0