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 > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
