Tabelle wird gerade geändert, Trigger/Funktion sieht dies möglicherweise nicht

[MarcJ]

Hallo zusammen,

folgendes Problem stellt sich mir und ich sehe den Fehler leider nicht. Über Hinweise/Tipps würde ich mich sehr freuen.

Aus Tabelle "A" schreibe ich per AFTER EACH ROW Trigger bei einem Update auf Tabelle A eine Datenmenge in die Tabelle "A-Temp". In Tabelle "A-Temp" feuert dann ein After Statement Trigger welcher auf ein Insert in Tabelle "A-Temp" reagiert. Der Trigger in Tabelle "A-Temp" soll wiederum ein Insert in Tabelle "A" durchführen.

Meines erachtens nach Rufe ich somit kein Update oder Insert aus einem Trigger auf die selbe Tabelle auf. Leider bekomme ich trotzdem folgende Fehlermeldung.

"Tabelle CRM5.APPOINTMENT wird gerade geändert, Trigger/Funktion sieht dies möglicherweise nicht"

Wenn ich aus Tabelle "A" per BEFORE EACH ROW Trigger bei einem Insert auf Tabelle A eine Datenmenge in Tabelle "A-Temp" schreibe funktioniert der Trigger in Tabelle "A-Temp" und führt den INSERT befehl durch den Trigger in Tabelle "A-Temp" vorschriftsmäßig durch.

Wo liegt hier der Fehler ? Und wieso funktionierts bei INSERT aber nicht bei UPDATE?

Vielen Dank im Vorraus
Marc

[Falk Prüfer]

Hallo,

mal abgesehen von der Sinnhaftigkeit dieser Triggerorgie - hast du noch irgendwelche SELECTs in den Triggern auf die gerade geänderten Tabellen?

Gruß Falk

[MarcJ]

Hallo Falk,

dann hab ich wohl etwas falsch verstanden. Hab noch nicht viel im Bereich "Oracle" und DB Triggern gemacht. Ich dachte das man keine Inserts oder Updates auf die Tabelle absetzen kann auf der ich den Trigger gesetzt habe. Aus diesem Grund meine Triggerorgie ;-) So kann ich das Problem ja Theoretisch umgehen. Für saubere Alternativen welche den Umfang der Aufgabe nicht sprengen bin ich immer dankbar.

Ich habe beide Trigger nochmal überprüft.
----------------------------------------------
Trigger auf Tabelle: CRM5.APPOINTMENT
When AfterUpdate Of column PRIORITY_IDX
Trigger für jede Zeile true
Referenzieren von Alt als OLD
Referenzieren von Neu als NEW


BEGIN
DELETE FROM crm5.appointment_temp;
INSERT INTO CRM5.appointment_temp
(
APPOINTMENT_TEMP_ID,
APPOINTMENT_ID,
CONTACT_ID,
PERSON_ID,
ASSOCIATE_ID,
GROUP_IDX,
REGISTERED,
REGISTERED_ASSOCIATE_ID,
DONE,
DO_BY,
LEADTIME,
TASK_IDX,
PRIORITY_IDX,
TYPE,
STATUS,
PRIVATE,
ALARM,
TEXT_ID,
PROJECT_ID,
MOTHER_ID,
DOCUMENT_ID,
COLOR_INDEX,
OPPORTUNITY_ID,
INVITEDPERSONID,
ACTIVEDATE,
ENDDATE,
LAGTIME,
SOURCE,
USERDEF_ID,
USERDEF2_ID,
UPDATED,
UPDATED_ASSOCIATE_ID,
UPDATEDCOUNT,
ACTIVELINKS,
RECURRENCERULEID,
LOCATION,
ALLDAYEVENT,
FREEBUSY,
REJECTCOUNTER,
EMAILID,
REJECTREASON,
HASALARM,
ASSIGNEDBY,
PREFERREDTZLOCATION
)
VALUES
(
1,
:OLD.appointment_id + 1,
:NEW.contact_id,
:NEW.person_id,
:NEW.associate_id,
(SELECT group_idx FROM crm5.associate WHERE associate_id = :NEW.associate_id),
:NEW.registered,
:NEW.associate_id,
0,
(SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual),
0,
6,
:NEW.PRIORITY_IDX,
2,
2,
0,
0,
(SELECT MAX(text_id)+1 FROM crm5.text),
0,
0,
0,
0,
0,
0,
(SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual),
(SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual),
0,
0,
0,
0,
0,
0,
0,
0,
0,
'',
0,
0,
0,
0,
'',
1,
:NEW.associate_id,
0
);
END;
----------------------------------------------
BEGIN
Trigger auf Tabelle: CRM5.APPOINTMENT_TEMP
When AfterInsert
Trigger für jede Zeile false

INSERT INTO CRM5.APPOINTMENT
(
APPOINTMENT_ID,
CONTACT_ID,
PERSON_ID,
ASSOCIATE_ID,
GROUP_IDX,
REGISTERED,
REGISTERED_ASSOCIATE_ID,
DONE,
DO_BY,
LEADTIME,
TASK_IDX,
PRIORITY_IDX,
TYPE,
STATUS,
PRIVATE,
ALARM,
TEXT_ID,
PROJECT_ID,
MOTHER_ID,
DOCUMENT_ID,
COLOR_INDEX,
OPPORTUNITY_ID,
INVITEDPERSONID,
ACTIVEDATE,
ENDDATE,
LAGTIME,
SOURCE,
USERDEF_ID,
USERDEF2_ID,
UPDATED,
UPDATED_ASSOCIATE_ID,
UPDATEDCOUNT,
ACTIVELINKS,
RECURRENCERULEID,
LOCATION,
ALLDAYEVENT,
FREEBUSY,
REJECTCOUNTER,
EMAILID,
REJECTREASON,
HASALARM,
ASSIGNEDBY,
PREFERREDTZLOCATION
)
VALUES
(
(SELECT COUNT(appointment_id)+2 FROM crm5.appointment),
(SELECT contact_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT person_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT associate_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT group_idx FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT registered FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT associate_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0,
(SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual),
0,
6,
0,
2,
2,
0,
(SELECT alarm FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT MAX(text_id)+1 FROM crm5.text),
0,
0,
0,
0,
0,
0,
(SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual),
(SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual),
0,
0,
0,
0,
0,
0,
0,
0,
0,
'',
0,
0,
0,
0,
'',
1,
(SELECT associate_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0
);
END;
----------------------------------------------
Die genaue Meldung lautet:
Tabelle crm5.appointment wird gerade geändert, Trigger/Funktion sieht dies möglicherweise nicht

[Falk Prüfer]

Hallo MarcJ,

du kannst in einem Oracle-Trigger durchaus ein UPDATE, INSERT oder DELETE auf die gerade geänderte Tabelle absetzen. Man sollte dabei nur beachten, dass dies ggfs. wiederum einen Trigger auslöst der dann ein Update macht welches einen Trigger auslöst der dann ....
Was du in einem (ON EACH ROW) Trigger nicht machen kannst, ist ein SELECT auf die gerade geänderte Tabelle. Dies führt zur genannten Fehlermeldung. Dabei ist es auch völlig irrelevant ob dieses Select im ursprünglichen oder in irgendeinem von kaskadierenden Triggern passiert.

Gruß Falk

[MarcJ]

Hallo Falk,

vielen Dank für die Erklärung.

MfG
Marc

[MarcJ]

Hallo Falk,

vielleicht kannst du mir doch nochmal einen Tipp geben.

Die Trigger habe ich natürlich weiterentwickelt und auch weitere Funktionen und Prozeduren habe ich hinzugefügt. Aber im Grunde bleibt das Problem bestehen.

Das Insert aus dem Trigger auf Tabelle appointment_temp scheint den Fehler zu erzeugen. Wenn ich stattdessen einen Dummy - Update auf appointment_temp ausführe tritt der Fehler nicht auf.

Nochmal zum Verständnis.

Im Moment gibt es drei Trigger

UpdateAppointment (Before Insert, Tabelle : appointment , Feld: Priority, ON EACH ROW)
InsertAppointment (Before Update of column, Tabelle: appointment, ON EACH ROW )
UpdateOrInsertOnAppointment(After Inser, Tabelle: appointment_temp)

Die Inhalte des Triggers von UpdateAppointment und InsertAppointment sind die selben.
Seltsam finde ich das es bei einem Insert auf Appointment ohne Probleme funktioniert aber bei einem Update auf ein Feld in Tabelle Appointment nicht funktioniert.

Im Trigger UpdateAppointment möchte ich einen Datensatz in Appointment_temp schreiben. Beim Insert Trigger funktioniert dies beim Update Trigger leider nicht. Die Trigger sollten sich nicht gegenseitig aufrufen da ich beim Insert oder Update auf das Feld priority überprüfe und dies beim Insert in Appointment aus dem Trigger UpdateOrInsertOnAppointment auf 0 gesetzt wird. Somit sollte der Insert Trigger nicht erneut auslösen.

Anbei nochmal das Insert welches aus dem Trigger auf Appointment_temp ausgeführt wird.


INSERT INTO CRM5.APPOINTMENT
(
APPOINTMENT_ID,
CONTACT_ID,
PERSON_ID,
ASSOCIATE_ID,
GROUP_IDX,
REGISTERED,
REGISTERED_ASSOCIATE_ID,
DONE,
DO_BY,
LEADTIME,
TASK_IDX,
PRIORITY_IDX,
TYPE,
STATUS,
PRIVATE,
ALARM,
TEXT_ID,
PROJECT_ID,
MOTHER_ID,
DOCUMENT_ID,
COLOR_INDEX,
OPPORTUNITY_ID,
INVITEDPERSONID,
ACTIVEDATE,
ENDDATE,
LAGTIME,
SOURCE,
USERDEF_ID,
USERDEF2_ID,
UPDATED,
UPDATED_ASSOCIATE_ID,
UPDATEDCOUNT,
ACTIVELINKS,
RECURRENCERULEID,
LOCATION,
ALLDAYEVENT,
FREEBUSY,
REJECTCOUNTER,
EMAILID,
REJECTREASON,
HASALARM,
ASSIGNEDBY,
PREFERREDTZLOCATION
)
VALUES
(
(SELECT appointment_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT contact_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT person_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT associate_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT group_idx FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT registered FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT associate_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0,
(SELECT DO_BY FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0,
6,
0,
(SELECT type FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT status FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0,
(SELECT alarm FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT text_fu_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0,
0,
0,
0,
0,
0,
(SELECT ACTIVEDATE FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
(SELECT ENDDATE FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0,
0,
0,
0,
0,
0,
0,
0,
0,
'',
0,
(SELECT freebusy FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0,
0,
'',
1,
(SELECT associate_id FROM crm5.appointment_temp WHERE appointment_temp_id = 1),
0
);

END IF;

[Falk Prüfer]

Hallo,

...Beim Insert Trigger funktioniert dies beim Update Trigger leider nicht.

Beim Insert wird ja auch EIN Datensatz angelegt, der die bestehende Datenmenge nicht verändert, während bei einem Update durchaus Zeilen betroffen sein können, die in dem Select abgefragt werden und deren Konsistenz der Server dann nicht sicherstellen kann.
Du solltest dir Überlegen auf die Trigger zu verzichten, deine Logik in SPs zu packen und Insert-/Updateoperationen dann über diese SPs abzuwickeln.

Gruß Falk

[TomT]

Beim Insert wird ja auch EIN Datensatz angelegt, der die bestehende Datenmenge nicht verändert,

Das gilt auch nur, wenn man nur einen einzelnen Satz mittles der VALUES Klausel einfügt. Ein INSERT INTO ... SELECT bringt ebenfalls einen Mutating Table Error

Ansonsten kann ich mich nur anschließen: Logik in Trigger zu packen ist müßelig, und macht das ganze Programm schwer wartbar, weil viele Dinge auf wundersame Weise passieren.

Schreib dir einen Trigger, der nur die Änderungen wegschreibt und lass dann deine Logik anhand dieser Änderungshistorie loslaufen.