SQL Oracle INSERT TRIGGER

[Helios co.]

Hallo @ all,

mal wieder muss ich euch mit SQL Fragen belästigen. Diesmal schreibe ich einen Trigger, der bei einem INSERT feuern soll.

Und zwar habe ich eine Tabelle mit 2 numerischen Spalten der Art (ID | WERT). Bei einem INSERT soll nun geschaut werden, ob die ID bereits vorhanden ist. Falls dem so ist, soll der alte wert auf wert+1 gesetzt werden. Falls nicht, soll der INSERT ganz normal ausgeführt werden.

Was ich bis jetzt ausprobiert habe ist folgender Trigger:

create or replace trigger t4_test BEFORE INSERT OR UPDATE OF id ON t4
FOR EACH ROW DECLARE

BEGIN

IF :new.id = 3 then :old.wert := :old.wert+1;
    END IF;
END;

/

Da kriege ich aber einen ORA-04085 Fehler.

Auch bringt mir das nicht so viel, da id fest gesetzt ist. Das soll natürlich der Wert aus dem INSERT rein.

Ich bin dankbar für jeden Rat!

 

[Digital_D99]

nimm den oracle sql developer, mit dem kannste dir ganz einfach nen trigger erstellen lassen

oder frag google

http://www.google.de/#hl=de&source=hp&q=oracle+trigger&aq=f&aqi=g10&aql=&oq=&fp=5f8a01f469bf0ecf

 

[Helios co.]

Hmm, schon mal vielen Dank für die Antwort. Ich benutze den bereits, bzw. jetzt aktuell Hora. Da kann man auch Trigger (zumindest das Gerüst) automatisch erstellen lassen.

Was mir in diesem Fall jedoch nicht wirklich weiterhilft.

Habe mir jetzt folgenden Trigger gebacken, leider funktioniert der Trigger nicht, da beim Insert ein Fehler geworfen wird (Trigger invalide. kompiliert wird er aber schon).

create or replace trigger t4_test BEFORE INSERT OR UPDATE OF id ON t4
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW DECLARE
v_zaehler NUMBER(9,0):=0;

BEGIN

    SELECT COUNT(1) INTO v_zaehler FROM t4 t WHERE t.id = NEW.id

    IF v_zaehler = 0 THEN NULL;
    ELSE IF v_zaehler = 1 THEN UPDATE t4 t SET t.wert = t.wert+1 where t.id = :NEW;
    END IF;


END;
/

Wo ist mein Denkfehler?

 

[carom]

Das geht so leider nicht, denn in dem Moment, in dem du in den trigger gehst ist t4 gelockt, du kannst dann innerhalb keine selects mehr auf t4 machen.

edit: wobei - habe im Hinterkopf, dass das nur für after row trigger gilt, müsste da nochmal nachschauen.

 

[AlbertLast]

ich hab leider gerade keine Oracle Instanz in test reichweite aber vieleicht klapts auch
im blind flug:

Nachtrag: Ich muss gerade feststellen das du ein Insert trigger machst .. daher ist Old immer null.
Daher wäre die frage was dein Ziel ist,
willst du aus den Insert Quasie ein Update machen und den Wert um eins erhöhen?

 

[kressi]

bin auch der meinung, dass das so nicht geht. du moechtest entweder ein update oder ein insert durchfuehren. soweit ich verstanden habe werden trigger durch ereignisse ausgeloest, verhindern diese jedoch nicht. so verursacht der insert, der nach dem trigger trotzdem durchgefuehrt wird, einen fehler. in mysql gibt es dafuer 'on duplicate'.

schau dir mal 'instead of' trigger (soweit ich verstanden habe nur fuer views) und 'merge' an.
http://www.java2s.com/Tutorial/Orac...aluewithNEWinabeforeinsertorupdatetrigger.htm (was du suchst, jedoch fuer views)
http://www.sqlrecipes.com/sql_questions_answers/insert_update_record_if_already_exists-6/ (was du suchst ohne trigger)

 

[AlbertLast]

was in Mysql on Duplicate ist in oracle Merge : http://psoug.org/reference/merge.html
instead of trigger ist nach meiner ansicht schlechte idee.
Du hast halt die möglichkeitn dein SQL mittels Merge zu verbessern und dort
werte um 1 steigen zu lassen.
Oder du musst ein After Row Insert Trigger machen, der
denn Insert löscht und ein Update macht, wobei hier bei zu einer
Mutate Exception kommen kan.

 

[Rossibaer]

Möchtest du eine Versionierung deiner Datensätze erreichen? z.B. Die Tabelle Adressen hat eine Adresse mehrmals unter der gleichen ID, jedoch gibt es nur eine "freigegebene" Adresse, die verwendet werden soll, dennoch sollen/müssen alle Versionen der Adresse gespeichert sein, sodaß dies später nachvollzogen werden kann.

Wäre nur ne Variante ohne Trigger:

INSERT INTO ADDRESSEN(ID, VERSION, NAME, STRASSE, PLZ, ORT) 
VALUES(1, (SELECT COUNT(*) FROM ADRESSEN WHERE ID=1),
'Dagobert Goldspeicher', 'Sonnenallee 1', 'D-99999', 'Entenhausen')
/
INSERT INTO ADDRESSEN(ID, VERSION, NAME, STRASSE, PLZ, ORT) 
VALUES(1, (SELECT COUNT(*) FROM ADRESSEN WHERE ID=1),
'Dagobert Goldspeicher', 'Am Hügel 1', 'D-99999', 'Entenhausen')
/
INSERT INTO ADDRESSEN(ID, VERSION, NAME, STRASSE, PLZ, ORT) 
VALUES(1, (SELECT COUNT(*) FROM ADRESSEN WHERE ID=1),
'Dagobert Goldspeicher', 'Auf dem Hügel 1', 'D-99999', 'Entenhausen')
/

PS: Wie schon einige vor mir bemerkten, im Row Trigger egal ob Before oder After:
1. hast du keinen Zugriff auf die Tabelle selbst -> Table is mutating
2. im After Each Row Trigger kannst du keine Werte des Datensatzes ändern, weils einfach zu spät ist, die Daten sind schon in der Tabelle
3. Im Before Row Trigger kannst du zwar Daten ändern, aber nur im NEW Record, OLD bleibt OLD und kann nicht verändert werden.
4. INSERT BEFORE EACH ROW: OLD.XYZ ist NULL, da es die Zeile noch nicht in der Tabelle gegeben hat.

 

[Helios co.]

Vielen vielen Dank euch allen für die wirklich qualifizierte Hilfe!

Habe es jetzt tatsächlich mittels Merge realisiert:

MERGE INTO t4 t USING dual ON (t.id = 1)
WHEN MATCHED THEN UPDATE SET t.wert = t.wert+1
WHEN NOT MATCHED THEN INSERT (id, wert) VALUES (2,1);

Das müsste zu klappen.

Nochmals einen großen Dank an alle und auf bald!