RE: Problem with trigger

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 =3D 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 =3D new.zip
  and then check $rc for 0 (YES, there is one) / $rc =3D 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
=20
Sitz der Gesellschaft/Registered Office: Walldorf, Germany

Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), L=E9o =
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=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.

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

=20

> -----Original Message-----
> From: Linos [mailto:[email protected]]=20
> Sent: Montag, 6. August 2007 22:20
> To: [email protected]
> Subject: [LIKELY JUNK]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
> --=20
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:   =20
> http://lists.mysql.com/[email protected]
>=20
>=20

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