ID innerhalb Einer Auftragsnummer (mutating table - Problem)

[Fuechslet]

Hallo zusammen,

ich brüte heute den ganzen Tag über ein Problem. Folgende Tabelle habe ich:

CREATE TABLE TABLATT_ELEMENT 
(	ABLATT VARCHAR2(11 CHAR) NOT NULL,
	ABLATTELEMENT NUMBER(11,0) DEFAULT 0 NOT NULL, 
	MENGE FLOAT(126) DEFAULT 0, 
	CONSTRAINT PK_ELEMENT PRIMARY KEY (ABLATT, ABLATTELEMENT),
        CONSTRAINT FKC_ELEMENT3 FOREIGN KEY (ABLATT) REFERENCES TABLATT (ABLATT) 
);

Nun möchte ich bei jedem Einfügen von Datensätze in die Tabelle im Feld ABLATTELEMENT die nächste freie Nummer ermitteln. Dazu habe ich einen Trigger erstellt:

create TRIGGER TR_ABLATTELEMENT BEFORE INSERT ON TABLATT_Element
FOR EACH ROW
DECLARE 
  v_newVal NUMBER(11,0) := 0;
BEGIN
    IF :new.ABLATTELEMENT is NULL THEN
       SELECT max(ABLATTELEMENT) INTO v_newVal FROM TABLATT_ELEMENT 
           WHERE ablatt = :new.Ablatt;
 
      IF v_newVal IS NULL THEN 
        v_newVal := 0;
      END IF;
      v_newVal := v_newVal + 1;

      :new.ABLATTELEMENT := v_newVal;
    END IF;
END;

Wenn ich ein Datensatz einfüge (natürlich ohne Vorgabe ABLATTELEMENT), funktioniert es wunderbar, bei mehreren Datensäte kommt as mutating table - Problem zu tragen.

ORA-04091: Tabelle wird gerade geändert

Gibt es überhaupt irgendein Lösungsansatz, wie ich eine eindeutige Nummer pro Feldinhalt ABLATT erzeugen kann?

Hoffe, mir kann jemand einen helfen. Gruß
Thomas

[o-sarusan]

Ich würde dazu eine Oracle-Sequence einsetzen.

Beispiele:
Einfach, zählt von 1 bis xxx, ohne den Sequencecache zu benutzen, wenn man fortlaufende IDs haben möchte.:

create sequence bib_zitat_id_seq start with 1 nocache

Bei Angabe des Cache oder eines Wertes für den Cache ( z.B. Cache 50) werden bei jedem Aufruf n Werte (nextval) bereitgestellt. Bei Programmunterbrechung, Shutdown o.a. gehen ungenutzte Werte verloren.

oder mit mehr Parametrn eine Sequenz die cyclisch von 1 bis 100 läuft:

create sequence schemaname.sequencename minvalue 1 maxvalue 100 increment by 1 start with 1 nocache noorder cycle

Einsatz einer Sequence:

select bib_zitat_id_seq.nextval into bib_zitat_id from dual ;

siehe auch Oracle-Docu unter Sequence

mfg

[dimitri]

Hi,

zur Erklärung was es mit dem Mutating Table Problem auf sich hat, kannst dir den Artikel von mir durchlesen:
http://www.sql-tips.de/mediawiki/ind...Mutating_Table

Was Sequenzen sind und wie man eine Auto-ID realisiert findest Du hier:
http://www.sql-tips.de/mediawiki/ind...nzen_in_Oracle

Dim

[Fuechslet]

Vielen Dank für den Hinweis, aber wenn ich das recht verstehe, generiert die Sequence eine fortlaufende Nummer für die ganze Tabelle.

Ich möchte ja eine Fortlaufende Nummer in Abhängigkeit des Felder ABLATT. Die Tabelle sollte dann so aussehen:

ABLATT ABLATTELEMENT
A1 1
A1 2
A2 1
A3 1

Was das Mutating Table Problem ist, habe ich verstanden, jedoch frage ich mich, wie man eine solche Aufgabenstellung (Automatische Generierung einer eindeutigen ID in Abhängigkeit eines Feldinhaltes) realisieren könnte.
Gruß

[dimitri]

Hi,

so etwas ist nicht möglich - zumindest nicht wie Du es vorhast.
Stell dir vor, es fügen zwei Benutzer zum (fast) gleichen Zeitpunkt etwas ein. Da eine Session nicht sehen kann was eine andere Session geändert hat solange diese ihre Änderungen nicht committet, würden beide den gleichen max Wert ermitteln.

Gleiches gilt bei einer Löschung - hier müssten dann wieder alle anderen Werte angepasst werden. Und was passiert, wenn mehrere user gleichzeitig löschen?

Du siehst schon, dass das nicht zu realisieren ist.

Daher werden fortlaufende Nummern dynamisch beim Select generiert. Dazu kannst Du in deinem Fall die analytische Funktion row_number() verwenden:

SELECT ablatt, row_number() over (partition by ablatt order by ablatt) ABLATTELEMENT
FROM tabelle

Dim