Re: Derby and Triggers

[Satheesh Bandaram]

Hi Jean, You are correct about Derby triggers not being able to execute a procedure. Since I have seen this requested many times before and since this is the only way to have multiple SQL statements in a trigger (that modify data), I have filed Derby-551 enhancement request. I believe Derby can be easily changed to support stored procedures in a trigger. Satheesh Jean T. Anderson wrote: Next, a trigger can't execute a procedure, but it can execute a function; however, functions are "read only" (can't update data in the database). Here's one very simple example that shows the syntax for a trigger that invokes a function implemented in Java: http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/[EMAIL PROTECTED] Often when I'm looking for an example on how to do something, I look at the functional tests: http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ If none of this information helps, please feel free to post more questions.  I'm interested in this area and will try to assemble a more helpful writeup on the topic that consolidates the information in all these various posts.  -jean Joachim G Stumpf wrote: Hi, i have to convert SQL Syntax to DERBY from Interbase. I use Derby 10 and customer uses 10.1 . So i downloaded Alpha version of reference Doku. Now i have to define a trigger Original CREATE TRIGGER "ATV_tr1" FOR "tab1" ACTIVE BEFORE INSERT POSITION 0 as declare variable bId integer; begin         select id_        from tab2 where bez_ = user into :bId;         if ( bId IS NULL ) then bId = 0;         new.xn_b_id_ = bId;         new.xad_b_id_ = bId;         new.neu_date_    = 'now';         new.aend_date_   = 'now'; end  ; I found db2 syntax which is similar to Derby. create trigger atv_basis   no cascade before   insert on ADM_TR_VORGABE_BASIS   referencing new as new   for each row mode db2sql   begin atomic            set new.neu_datum_    = CURRENT_DATE;         set new.aend_datum_   = CURRENT_DATE; end; This isn't working too. Can somebody help me out? mfg Joachim Stumpf   DB2 Technical presales support Tel.: (+49) -7034-15-3276     Fax:   (+49)-7034-15-3400 Internet: [EMAIL PROTECTED]    Mobil: (+49)-172-733 9453 Developersite: http://www.ibm.com/software/data/developer Forum: http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19