db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joachim G Stumpf <joachim.stu...@de.ibm.com>
Subject Re: Derby and Triggers
Date Fri, 02 Sep 2005 07:52:50 GMT
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 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    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 





Mime
View raw message