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 Fri, 02 Sep 2005 17:10:18 GMT
Hi, Joachim,

Sorry for the confusion. A trigger can't call a procedure yet -- 
Satheesh logged an enhancement request to add that support ( 
http://issues.apache.org/jira/browse/DERBY-551 ).

I don't believe there is a way *today* to do what you want from inside 
the trigger. Alternatives might be:

1) The trigger inserts the data into a separate table, then move that 
data back to the right table. This strikes me as not reliable since it 
requires a separate step.

2) Perform inserts via an application that sets those values to what you 
want.

any other ideas, anyone?

  -jean

Joachim G Stumpf wrote:
> 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: 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
> 
>>Please respond to "Derby Discussion" <derby-user@db.apache.org>
>>
>>To:        Derby Discussion <derby-user@db.apache.org>
>>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/%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_ <mailto: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_
> 
> <http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19>
> 
>>
>>
>>
> 


Mime
View raw message