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 Fri, 02 Sep 2005 17:24:51 GMT
No, I don't think so.. It does solve many other problems though...

Satheesh

Jean T. Anderson 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