Please respond to "Derby Discussion" <firstname.lastname@example.org>
Derby Discussion <email@example.com>
Subject: Re: Derby and Triggers
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.
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:
Often when I'm looking for an example on how to do something, I look at the functional tests:
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.
Joachim G Stumpf wrote:
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
CREATE TRIGGER "ATV_tr1" FOR "tab1"
ACTIVE BEFORE INSERT POSITION 0
declare variable bId integer;
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';
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
set new.neu_datum_ = CURRENT_DATE;
set new.aend_datum_ = CURRENT_DATE;
This isn't working too.
Can somebody help me out?
Joachim Stumpf DB2 Technical presales support
Tel.: (+49) -7034-15-3276 Fax: (+49)-7034-15-3400
Internet: firstname.lastname@example.org Mobil: (+49)-172-733 9453