Hi Jean,
Do we change something there in the future?
I used the link you provided to testing.
Is it allowed to call a stored procedure within a trigger in Derby?
Do i have to update to Cloudscape V10.1 to do more testing on Triggers? Is
it needed or was this only a problem of documentation.
-Joachim
jean wrote:
Hi, Joachim,
You can't modify the data being inserted into the table on which the
trigger has been created -- in your case, the trigger can't update the
values being inserted into ATV_BASE because the trigger was defined for
ATV_BASE. However, the trigger can insert the data into a different table.
Satheesh, would support for stored procedures allow Joachim to do what
he wants?
-jean
Joachim G Stumpf wrote:
>
> Hi Jean , Hi Satheesh,
> what i really want to know is the available syntax how to change data or
> add data in the actual insert.
> This is why i need the syntax to do this.
> Here are my tests . as you can see nearly everything is working , which
> leads me to the conclusion that there must be another way to set the
> values for the current insert statement.
>
> CREATE TABLE "ATV_BASE"
> ( "AT_ID_" NUMERIC(10, 0) NOT NULL,
> "V_ID_" NUMERIC(10, 0) NOT NULL,
> ....
> "N_DATE" DATE DEFAULT '1970-01-01' NOT NULL,
> "XN_BID_" NUMERIC(10, 0) DEFAULT 0 NOT NULL,
> "CHG_DATE_" DATE DEFAULT '1970-01-01' NOT NULL,
> "XCHG_BID_" NUMERIC(10, 0) DEFAULT 0 NOT NULL,
> ..
> PRIMARY KEY ("AT_ID_", "V_ID_")
> );
>
> ij> create trigger atv_base1
> no cascade before
> insert on ATV_BASE
> referencing new as new
> for each row mode db2sql
> set new.n_date_ = CURRENT_DATE; /*here i want to set a date . I will
> do it in a default value*/
> ERROR 42X01: Syntax error: Encountered "set" at line 6, column 3.
> ij> create trigger atv_base2
> no cascade before
> insert on ATV_BASE
> referencing new as new
> for each row mode db2sql
> set new.xn_bid_ = (select id_ from btab where bez_ = user);
> /*dependant on the user value which is char i want to get a number out
> of a separate table which i need for the current insert otherwise
> insert,which caused the trigger will fail*/
> ERROR 42X01: Syntax error: Encountered "set" at line 6, column 3.
>
> What i would like to have is to use this in one trigger.
>
> 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
>
> Please respond to "Derby Discussion" <[email protected]>
>
> To: Derby Discussion <[email protected]>
> cc:
> Subject: Re: Derby and Triggers
>
>
> 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_
> <http://issues.apache.org/jira/browse/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] <mailto:[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_
>
<http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19>
>
>
>
>
