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/db4940f0030272e10000000a155106/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."
---------------------------------
Be smarter than spam. See how smart SpamGuard is at giving junk email the boot
with the All-new Yahoo! Mail
