version: 7.5.00.34 platform: Windows 32 This is the same problem I am still working on. I found that the 'IGNORE TRIGGER' compiles or works in a trigger statement. Question: Is the IGNORE TRIGGER syntax supposed to work in a procedure? Merely to be OO principled I put my code in a procedure and was calling my procedure from a Insert and an Update trigger. My workaround at this point is to embed the same code in a my update and insert triggers but I would prefer the code in the procedure.
Code and error message below: CREATE DBPROC CMA.UPDATESEARCHKEY (IN IDNUM fixed(8) ) AS TRY UPDATE CMA.CUSTOMERS SET CONTACT_SEARCH_KEY = UPPER(CUST_NO)||' '||UPPER(CUST_NAME) || ' ' || UPPER(CUSTOMERS.CUST_ADDRESS3)||' ' || ' ' || UPPER(CUSTOMERS.CUST_ZIP_CODE) WHERE ID = :IDNUM IGNORE TRIGGER ; CATCH IF $RC<>100 THEN STOP ($RC,'error') ; This yields the following error: Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed Syntax error or access violation;-3014 POS(259) Invalid end of SQL statement Thanks in Advance, Homer "Zabach, Elke" <[EMAIL PROTECTED]> wrote: H [mailto:[EMAIL PROTECTED] wrote: > > > I am having trouble writing a successful trigger according to > the sample code. > > (Gosh I am hoping the Forum isnt cross-posted to the list > because I put this in the forum as well). > > Below you will see: > a) A simple schema creation statement > b) A simple insert statement for some sample data > c) A simply written trigger that doesnt execute as expected > d) A sql statement written to exercise the trigger and the > resulting Error message > e) Cut and pasted MAXDB documentation from the TUTORIAL part > (example code of the online help) > > What I would like: > Someone to rewrite the trigger so it works or better yet > explain why it isnt working? > > Thanks in advance, > > Homer > > a) CREATE TABLE "CM"."TEST" > ( > "ID" Fixed (8,0) NOT NULL, > "FLD1" Varchar (25) ASCII DEFAULT 'USER', > "FLD2" Varchar (22) ASCII DEFAULT 'USER', > "RCVAL" Integer, > PRIMARY KEY ("ID") > ) > > b) Insert into test values (1,'a','b',99) > > c) CREATE TRIGGER UPDATE FOR CM.TEST > AFTER UPDATE > EXECUTE > ( > TRY > > UPDATE CM.TEST SET FLD1 = 'After Update Worked' WHERE TEST.ID > = :OLD.ID ; > > CATCH > IF $RC <> 0 THEN STOP ($RC,' unexpected error') ; > ) > > d) update test set fld2 = 'g' where id = 1 > ---- Error ------------------------------- > Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed > General error;-918 POS(1) unexpected error > update test set fld2 = 'g' where id = 1 > > > > e)The cut and paste from the documentation > > Source Link > http://dev.mysql.com/doc/maxdb/en/08/db4940f0030272e10000000a1 > 55106/content.htm > > The sample code is as follows, note the inconsistancy: 100 in > the code indicates success yet the notes clearly state that 0 > indicates success: > > CREATE TRIGGER hotel_update FOR hotel.hotel AFTER UPDATE EXECUTE > (TRY > IF NEW.hno <> OLD.hno > THEN UPDATE hotel.room SET hno = :NEW.hno WHERE hno = :OLD.hno; > CATCH > IF $rc <> 100 > THEN STOP ($rc, 'unexpected error');) > > > " The $rc variable returns a numeric error code after the > UPDATE statement has been executed. The value 0 means that > the statement was successfully executed." > > Hi, your trigger causes an recursiveness, meaning that your update causes the trigger to fire, causing an update which causes the trigger to fire causing an update .... up to the limit of stack. You missed the syntax clause IGNORE TRIGGER in the update in the trigger to avoid firing the trigger when updating in the trigger and you missed the chance to specify those columns causing the trigger to fire, in your case fld2, but not fld1 (out of the trigger). http://dev.mysql.com/doc/maxdb/en/34/ee7fba293911d3a97d00a0c9449261/fram eset.htm http://dev.mysql.com/doc/maxdb/en/a7/41ee0b605911d3a98800a0c9449261/fram eset.htm One of these syntax clauses you should use and your problem will be solved. Elke SAP Labs Berlin > --------------------------------- > Be smarter than spam. See how smart SpamGuard is at giving > junk email the boot with the All-new Yahoo! Mail > --------------------------------- The best gets better. See why everyone is raving about the All-new Yahoo! Mail.
