Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of jta@bristowhill.com designates
 66.75.162.133 as permitted sender)
Message-ID: <43186056.8080709@bristowhill.com>
Date: Fri, 02 Sep 2005 07:23:18 -0700
From: "Jean T. Anderson" <jta@bristowhill.com>
User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Derby and Triggers
References: 
 <OF5E4F2092.307EE085-ONC1257070.0028395D-C1257070.002B4B80@de.ibm.com>
In-Reply-To: 
 <OF5E4F2092.307EE085-ONC1257070.0028395D-C1257070.002B4B80@de.ibm.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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> 
> 
> 
> 
>