Problem with trigger 2007-08-06 - By Linos
Back
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=mysql@(protected)
|
|
