Andreas Goldstein wrote : Gesendet: Montag, 17. Oktober 2005 08:06 An: * Betreff: [FSID] Triggers & 9111
Hi Listmembers, what is wrong here: CREATE TABLE WWIND.Kunden ( KundenNr FIXED(10) DEFAULT SERIAL PRIMARY KEY, Firma VARCHAR(40) NOT NULL, Kontaktperson VARCHAR(30), Funktion VARCHAR(30), Strasse VARCHAR(60) NOT NULL, Ort VARCHAR(15) NOT NULL, Region VARCHAR(15), PLZ VARCHAR(10) NOT NULL, Land VARCHAR(15) NOT NULL, Telefon VARCHAR(24), Telefax VARCHAR(24)) // CREATE TRIGGER it_kontaktinfo FOR WWIND.KUNDEN AFTER INSERT EXECUTE ( IF (KONTAKTPERSON IS NULL OR KONTAKTPERSON = '') AND FUNKTION IS NOT NULL THEN STOP (30001, 'Funktion ohne Kontaktperson ist nicht erlaubt'); ) // CREATE TRIGGER ut_kontaktinfo FOR WWIND.KUNDEN AFTER UPDATE EXECUTE ( IF (NEW.KONTAKTPERSON IS NULL OR NEW.KONTAKTPERSON = '') AND NEW.FUNKTION IS NOT NULL THEN STOP (30002, 'Funktion ohne Kontaktperson ist nicht erlaubt'); ) // INSERT INTO KUNDEN (KundenNr, Firma, Kontaktperson, Funktion, Strasse, Ort, Region, PLZ, Land, Telefon, Telefax) VALUES (1,'Alfreds Futterkiste','Maria Anders','Vertriebsmitarbeiterin','Obere Str. 57','Berlin', '','12209','Deutschland','030-0074321','030-0076545') Execution of the above statements on MaxDB 7.6.00.12 (Windows XP) results in the following errormessage: ---- Error ------------------------------- Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed General error;-9111 INSERT INTO KUNDEN (KundenNr, Firma, Kontaktperson, Funktion, Strasse, Ort, Region, PLZ, Land, Telefon, Telefax) VALUES (1,'Alfreds Futterkiste','Maria Anders','Vertriebsmitarbeiterin','Obere Str. 57','Berlin', '','12209','Deutschland','030-0074321','030-0076545') Marked in red are 'NSERT' of the INSERT-Statement (Yes, the 'I' is not marked!). The reason for the error seems to be caused by the triggers as the error will not occur when creating the table and inserting the record without having defined the triggers. This example demonstrates the problem with showing defined triggers I mentioned some days before (see '[FSID] DOMAIN.TRIGGERS always empty!?'): Execute the above statements without the INSERT-Statement. On my MaxDB-installations this will not result in an errormessage and the table will be shown in the catalog-manager of SQL Studio while the triggers are not shown there. SELECT COUNT(*) FROM TRIGGERS resuults in '0', but when I try to define a INSERT-Trigger on WWIND.KUNDEN the system states a "6006 - Duplicate name" Who can explain these phenomenons? THX & Regards --- Ursprüngliche Nachricht --- Von: "Anhaus, Thomas" <*> An: "Andreas Goldstein" <*>, <*> Betreff: AW: [FSID] Triggers & 9111 Datum: Mon, 17 Oct 2005 17:04:09 +0200 The error -9111 is a bug which is caused by the combination of the default serial and the trigger. We will fix it as soon as possible. Unfortunately I have not been able to reproduce the hidden trigger problem yet. Best Regards, Thomas -----Ursprüngliche Nachricht----- Von: Andreas Goldstein [mailto:*] Gesendet: Dienstag, 18. Oktober 2005 14:57 An: Anhaus, Thomas Betreff: Re: AW: [FSID] Triggers & 9111 Hi Thomas! Thanks for your response. How is sould this information be interpreted? Never use triggers on tables that use default serial attributes? Is there any workaround for this issue? This seems to be a serious bug once again, as the trigger-routine has been changed according to the LENGTH-bug yet and does not work at all. :( --- Ursprüngliche Nachricht --- Von: "Anhaus, Thomas" <*> An: "Andreas Goldstein" <*> Betreff: AW: AW: [FSID] Triggers & 9111 Datum: Tue, 18 Oct 2005 15:19:07 +0200 Hi Andreas, the problem actually has nothing to do with the trigger and the code inside the trigger is irrelevant. The problem is caused by the serial and you will also be able to run into the problem without any trigger. However, I don't know the exact circumstances because I'm not familiar with that part of the code. But as far as I understood the problem, the following workaround should help : CREATE TRIGGER it_kontaktinfo FOR WWIND.KUNDEN AFTER INSERT EXECUTE ( IF (KONTAKTPERSON IS NULL OR KONTAKTPERSON = '') AND FUNKTION IS NOT NULL THEN STOP (30001, 'Funktion ohne Kontaktperson ist nicht erlaubt'); ) whenever 1 = 1 Best Regards, Thomas Hi Listmembers, by a mistake I did not respond to the list so I fix this mistake by this mail that contains a workaraound suggested by Thomas. Best regards Hi again, I tried to identify the bug in the MaxDB-PTS (http://www.sapdb.org/webpts) - can someone please state the PTS-number of the bug that causes the problem mentioned above. THX & Best regards -- Highspeed-Freiheit. Bei GMX supergünstig, z.B. GMX DSL_Cityflat, DSL-Flatrate für nur 4,99 Euro/Monat* http://www.gmx.net/de/go/dsl -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
