Problem with trigger

From: Linos <info(at)linos.es>
Date: Mon Aug 06 2007 - 16:19:53 EDT

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/maxdb?unsub=lists@pantek.com

Received on Mon Aug 6 16:20:10 2007