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