db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: Derby and Triggers
Date Thu, 01 Sep 2005 16:37:04 GMT
The docs are really light on triggers (and functions and procedures).
Information about triggers in the reference guide is here and was 
updated (slightly) for 10.1:

   http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html

When implementing triggers, here are a couple things to keep in mind.

First, the body of the trigger can execute just one SQL statement. 
Here's an example of someone who wrote a trigger that inserted data into 
a shadow table, and used a CASE statement that needed some help to get 
working (and I think the URL below points to the post that indicates 
that problem was resolved):

http://mail-archives.apache.org/mod_mbox/db-derby-user/200508.mbox/%3c74130ca105081221081ad4a563@mail.gmail.com%3e

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/%3c42A0E031.60003@bristowhill.com%3e

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: stumpfj@de.ibm.com    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


Mime
View raw message