RE: Problem with trigger

Linos wrote :

> -----Original Message-----
> From: Linos [mailto:[email protected]]=20
> Sent: Montag, 6. August 2007 22:20
> To: [email protected]
> Subject: Problem with trigger
>=20
> Hello all,
> 	i am trying to do a trigger that permits me be sure=20
> 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=20
> trigger in
> the hotel schema to test the idea:
>=20
> 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;)
>=20
>=20
> when i try to do an insert with a zip not in hotel.city i have this:
>=20
> ---- 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')
>=20
> when i try with a valid zip i get this:
>=20
> ---- 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')
>=20
> I suppose i have a problem in my trigger code but i cant=20
> understand very
> well the maxdb procedure language, i think it would be very=20
> good to have
> more usage examples in the maxdb documentation, thanks in advance.
>=20
> Best Regards,
> Miguel angel.
>=20

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=20
   in HOTEL.CITY.=20

2. You called the STOP method with $rc, which is 0. STOP(0) ist not =
allowed. =20

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 =3D 0 DO
    BEGIN
    FETCH HOTEL_ZIP_CURSOR INTO :LISTA;
    IF NEW.ZIP =3D LISTA
       THEN BREAK;
    END;
 CATCH
    IF $rc =3D 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: [email protected]
www.sap.com
Sitz der Gesellschaft/Registered Office: Walldorf, Germany
Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), L=E9o =
Apotheker (stellvertretender Sprecher / Deputy CEO), Werner Brandt, =
Claus Heinrich, Gerhard Oswald, Peter Zencke
Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: =
Hasso Plattner=20
Registergericht/Commercial Register Mannheim No HRB 350269

Diese E-Mail kann Betriebs- oder Gesch=E4ftsgeheimnisse oder sonstige =
vertrauliche Informationen enthalten. Sollten Sie diese E-Mail =
irrt=FCmlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, =
eine Vervielf=E4ltigung oder Weitergabe der E-Mail ausdr=FCcklich =
untersagt.
Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. =
Vielen Dank.
=20
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.=20

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[email protected]