RE: Problem with trigger

Linos wrote :

> -----Original Message-----
> From: Linos [mailto:info@linos.es]
> Sent: Montag, 6. August 2007 22:20
> To: maxdb@lists.mysql.com
> Subject: 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.
>

Hi Miguel,
as far as I understand there are 2 problems :

1. You compare NEW.ZIP to the first row of HOTEL.CITY. If the result is not equal you return an error. This probably is not what you wanted to do, because you have to check NEW.ZIP against all rows stored in HOTEL.CITY.
2. You called the STOP method with $rc, which is 0. STOP(0) ist not allowed.

The correct solution could look as follows :

CREATE TRIGGER prueba_zip FOR HOTEL.HOTEL AFTER INSERT EXECUTE (VAR
   LISTA CHAR(5);
 TRY
   DECLARE HOTEL_ZIP_CURSOR CURSOR FOR
   SELECT ZIP FROM HOTEL.CITY;
   WHILE $rc = 0 DO
    BEGIN
    FETCH HOTEL_ZIP_CURSOR INTO :LISTA;
    IF NEW.ZIP = LISTA
       THEN BREAK;
    END;
 CATCH
    IF $rc = 100
    THEN
      STOP (-31001, 'value not in list');     ELSE
      STOP (-31002, 'unexpected error segunda parte');  CLOSE HOTEL_ZIP_CURSOR;) Best Regards,
Thomas

---

Thomas Anhaus
Development Architect
MaxDB&liveCache
SAP AG
mailto: thomas.anhaus@sap.com
www.sap.com
Sitz der Gesellschaft/Registered Office: Walldorf, Germany
Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Léo Apotheker (stellvertretender Sprecher / Deputy CEO), 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. 

-- 
MaxDB Discussion Mailing List
For list archives: 
http://lists.mysql.com/maxdb
To unsubscribe:    
http://lists.mysql.com/maxdb?unsub=lists@pantek.com