db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Derby and Triggers
Date Thu, 01 Sep 2005 23:48:52 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Jean,<br>
<br>
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 <a
 href="http://issues.apache.org/jira/browse/DERBY-551">Derby-551</a>
enhancement request. I believe Derby can be easily changed to support
stored procedures in a trigger.<br>
<br>
Satheesh<br>
<br>
Jean T. Anderson wrote:<br>
<blockquote cite="mid43172E30.5000601@bristowhill.com" type="cite"><br>
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:
  <br>
  <br>
<a class="moz-txt-link-freetext" href="http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3c42A0E031.60003@bristowhill.com%3e">http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3c42A0E031.60003@bristowhill.com%3e</a>
  <br>
  <br>
Often when I'm looking for an example on how to do something, I look at
the functional tests:
  <br>
  <br>
<a class="moz-txt-link-freetext" href="http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/">http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/</a>
  <br>
  <br>
If none of this information helps, please feel free to post more
questions.&nbsp; 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.
  <br>
  <br>
&nbsp;-jean
  <br>
  <br>
  <br>
  <br>
Joachim G Stumpf wrote:
  <br>
  <blockquote type="cite"><br>
Hi,
    <br>
i have to convert SQL Syntax to DERBY from Interbase.
    <br>
I use Derby 10 and customer uses 10.1 . So i downloaded Alpha version
of reference Doku.
    <br>
    <br>
Now i have to define a trigger
    <br>
    <br>
Original
    <br>
CREATE TRIGGER "ATV_tr1" FOR "tab1"
    <br>
ACTIVE BEFORE INSERT POSITION 0
    <br>
as
    <br>
declare variable bId integer;
    <br>
begin
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select id_&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
from tab2 where bez_ = user into :bId;
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if ( bId IS NULL )
then bId = 0;
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.xn_b_id_ = bId;
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.xad_b_id_ = bId;
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.neu_date_&nbsp;&nbsp;&nbsp;
= 'now';
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.aend_date_&nbsp;&nbsp;
= 'now';
    <br>
end
    <br>
&nbsp;;
    <br>
    <br>
I found db2 syntax which is similar to Derby.
    <br>
    <br>
create trigger atv_basis
    <br>
&nbsp; no cascade before
    <br>
&nbsp; insert on ADM_TR_VORGABE_BASIS
    <br>
&nbsp; referencing new as new
    <br>
&nbsp; for each row mode db2sql
    <br>
&nbsp; begin atomic
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
set new.neu_datum_&nbsp;&nbsp;&nbsp; = CURRENT_DATE;
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set new.aend_datum_&nbsp;&nbsp;
= CURRENT_DATE;
    <br>
    <br>
end;
    <br>
    <br>
This isn't working too.
    <br>
Can somebody help me out?
    <br>
    <br>
    <br>
mfg
    <br>
Joachim Stumpf&nbsp;&nbsp; DB2 Technical presales support
    <br>
Tel.: (+49) -7034-15-3276&nbsp;&nbsp;&nbsp;&nbsp; Fax:&nbsp;&nbsp;
(+49)-7034-15-3400
    <br>
Internet: <a class="moz-txt-link-abbreviated" href="mailto:stumpfj@de.ibm.com">stumpfj@de.ibm.com</a>&nbsp;&nbsp;&nbsp;
Mobil: (+49)-172-733 9453
    <br>
    <br>
Developersite: <a class="moz-txt-link-freetext" href="http://www.ibm.com/software/data/developer">http://www.ibm.com/software/data/developer</a>
    <br>
Forum:
<a class="moz-txt-link-freetext" href="http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19">http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&amp;cat=19</a>
    <br>
  </blockquote>
  <br>
  <br>
  <br>
</blockquote>
</body>
</html>


Mime
View raw message