I can not use foreign keys because i want to use this database in replication, thanks by the new idea for the trigger :).
Zietlow, Elke escribió: > 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]
