Hi, obviously you are not familiar with the feature of referential constraints, which are very common and implemented in MaxDB, too. These referential constraints assure that only such values can be used for one column of table x which are stored in some other column in another table. This is exactly what you want.
And, what is used in the example-database with hotel and city when creating the table hotel, where in the end this line can be found: FOREIGN KEY hotel_zip_in_city (zip) REFERENCES city ON DELETE RESTRICT saying that only zips stored in city's keycolumn are allowed to be used in hotel's column zip. --> The error coming in your first try (invalid zip) is not the result of your trigger, but the result of this referential constraint. Now to your trigger: you are selecting ALL zips from city, but only fetching the first one and only checking this. --> if at least one city is stored, your select and fetch will result in $RC = 0 --> after your one fetch you will stop with 0, no matter if your zip is in or not. 1. you should use foreign keys (named referential constraints) for checking this. 2. if you (for an unknown reason) do not like referential constraints you have to delete the pre-defined one and your trigger should ask for select ... from hotel.city where zip = new.zip and then check $rc for 0 (YES, there is one) / $rc = 100 (NO, no such zip found). They way you are trying it, is (despite the missing loop for fetch) the loooong way round. Elke SAP Labs Berlin Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269 Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank. This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation. > -----Original Message----- > From: Linos [mailto:[EMAIL PROTECTED] > Sent: Montag, 6. August 2007 22:20 > To: maxdb@lists.mysql.com > Subject: [LIKELY JUNK]Problem with trigger > > Hello all, > i am trying to do a trigger that permits me be sure > that when the users > inserts a row use a list of possible good values to any of the columns > from other table where i maintain this list, i would like to have this > list dynamic instead of a constraint because i dont want to touch the > constraint every time an item it is added, i have added this > trigger in > the hotel schema to test the idea: > > CREATE TRIGGER prueba_zip FOR HOTEL.HOTEL AFTER INSERT EXECUTE > (VAR > LISTA CHAR(5); > DECLARE HOTEL_ZIP_CURSOR CURSOR FOR > SELECT ZIP FROM HOTEL.CITY; > TRY > FETCH HOTEL_ZIP_CURSOR INTO :LISTA; > IF NEW.ZIP NOT IN (LISTA) > THEN STOP ($rc, 'unexpected error'); > CATCH > STOP ($rc, 'unexpected error segunda parte'); > CLOSE HOTEL_ZIP_CURSOR;) > > > when i try to do an insert with a zip not in hotel.city i have this: > > ---- Error ------------------------------- > Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed > Integrity constraint violation;350 POS(1) Referential integrity > violated:HOTEL_ZIP_IN_CITY,HOTEL,HOTEL > INSERT INTO HOTEL.HOTEL VALUES ('2445','test','14011','test','test') > > when i try with a valid zip i get this: > > ---- Error ------------------------------- > Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed > General error;-28910 STOP(0) not allowed > INSERT INTO HOTEL.HOTEL VALUES ('2445','test','10019','test','test') > > I suppose i have a problem in my trigger code but i cant > understand very > well the maxdb procedure language, i think it would be very > good to have > more usage examples in the maxdb documentation, thanks in advance. > > Best Regards, > Miguel angel. > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
