Linos wrote :
> -----Original Message-----
> From: Linos [mailto:[EMAIL PROTECTED]
> Sent: Montag, 6. August 2007 22:20
> To: [email protected]
> 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: [EMAIL PROTECTED]
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/[EMAIL PROTECTED]
