Derby and Triggers

[Joachim G Stumpf]

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: [phone]
Internet: stumpfj-tA70FqPdS9bQT0dZR+***@public.gmane.org Mobil: ([phone]

Developersite: http://www.ibm.com/software/data/developer
Forum:
http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

[Kristian Waagan]

Hello,

I have found using triggers in Derby a little difficult myself. There
are several things to watch out for. I will try to give you a few
pointers to get you going, but I am no expert, and would appreciate if
someone with more knowledge corrected me and provided further information.

For your information, the CREATE TRIGGER statement is described in the
Reference manual and more information can be found in the Developer's Guide.

First of all, you can only specify a single SQL statement for the
trigger. You can have several triggers for the same event on the same
table. If you can't express the required actions in a single SQL
statement, or with multple triggers, you can write a database-side JDBC
procedure and invoke it in the trigger.

Second, a trigger with (NO CASCADE) BEFORE does not allow UPDATE, INSERT
or DELETE statements as the triggered SQL statement.

So my proposal for the trigger, which I am very uncertain if is the best
one, is:

CREATE TRIGGER ATV_tr1
AFTER INSERT
ON tab1
REFERENCES NEW AS NEW
FOR EACH ROW MODE DB2SQL
UPDATE tab1 SET neu_date = CURRENT_DATE
WHERE tab1.'some-unique-field' = NEW.'some-uniqe-field';

Is there a way to operate only on the affected row(s), instead of using
the WHERE clause to select to correct row?


Other comments?



--
Kristian

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer
http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

[Joachim G Stumpf]

Hi Kristian,
thanks for your answer, but i want to avoid breaking one transaction into
2.
The syntax before trigger is available , but there is nothing inside how
to change or provide data to the actual insert that should happen after
the trigger is fired.
I don't want to do an additional insert.
There is no sample which describes how to do this.

mfg
Joachim Stumpf
Please respond to "Derby Discussion" <derby-user-***@public.gmane.org>
To: Derby Discussion <derby-user-***@public.gmane.org>
cc:
Subject: Re: Derby and Triggers


Hello,

I have found using triggers in Derby a little difficult myself. There
are several things to watch out for. I will try to give you a few
pointers to get you going, but I am no expert, and would appreciate if
someone with more knowledge corrected me and provided further information.

For your information, the CREATE TRIGGER statement is described in the
Reference manual and more information can be found in the Developer's
Guide.

First of all, you can only specify a single SQL statement for the
trigger. You can have several triggers for the same event on the same
table. If you can't express the required actions in a single SQL
statement, or with multple triggers, you can write a database-side JDBC
procedure and invoke it in the trigger.

Second, a trigger with (NO CASCADE) BEFORE does not allow UPDATE, INSERT
or DELETE statements as the triggered SQL statement.

So my proposal for the trigger, which I am very uncertain if is the best
one, is:

CREATE TRIGGER ATV_tr1
AFTER INSERT
ON tab1
REFERENCES NEW AS NEW
FOR EACH ROW MODE DB2SQL
UPDATE tab1 SET neu_date = CURRENT_DATE
WHERE tab1.'some-unique-field' = NEW.'some-uniqe-field';

Is there a way to operate only on the affected row(s), instead of using
the WHERE clause to select to correct row?


Other comments?



--
Kristian

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer

http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

[Jean T. Anderson]

The docs are really light on triggers (and functions and procedures).
Information about triggers in the reference guide is here and was
updated (slightly) for 10.1:

http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html

When implementing triggers, here are a couple things to keep in mind.

First, the body of the trigger can execute just one SQL statement.
Here's an example of someone who wrote a trigger that inserted data into
a shadow table, and used a CASE statement that needed some help to get
working (and I think the URL below points to the post that indicates
that problem was resolved):

http://mail-archives.apache.org/mod_mbox/db-derby-user/200508.mbox/%3c74130ca105081221081ad4a563-JsoAwUIsXosN+***@public.gmane.org%3e

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-N3a1/***@public.gmane.org%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

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer
http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

[Satheesh Bandaram]

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Jean,<br>
<br>
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 <a
href="http://issues.apache.org/jira/browse/DERBY-551">Derby-551</a>
enhancement request. I believe Derby can be easily changed to support
stored procedures in a trigger.<br>
<br>
Satheesh<br>
<br>
Jean T. Anderson wrote:<br>
<blockquote cite="mid43172E30.5000601-N3a1/***@public.gmane.org" type="cite"><br>
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:
<br>
<br>
<a class="moz-txt-link-freetext" href="http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3c42A0E031.60003-N3a1/***@public.gmane.org%3e">http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3c42A0E031.60003-N3a1/***@public.gmane.org%3e</a>
<br>
<br>
Often when I'm looking for an example on how to do something, I look at
the functional tests:
<br>
<br>
<a class="moz-txt-link-freetext" href="http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/">http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/</a>
<br>
<br>
If none of this information helps, please feel free to post more
questions.&nbsp; 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.
<br>
<br>
&nbsp;-jean
<br>
<br>
<br>
<br>
Joachim G Stumpf wrote:
<br>
<blockquote type="cite"><br>
Hi,
<br>
i have to convert SQL Syntax to DERBY from Interbase.
<br>
I use Derby 10 and customer uses 10.1 . So i downloaded Alpha version
of reference Doku.
<br>
<br>
Now i have to define a trigger
<br>
<br>
Original
<br>
CREATE TRIGGER "ATV_tr1" FOR "tab1"
<br>
ACTIVE BEFORE INSERT POSITION 0
<br>
as
<br>
declare variable bId integer;
<br>
begin
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select id_&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from tab2 where bez_ = user into :bId;
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if ( bId IS NULL ) then bId = 0;
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.xn_b_id_ = bId;
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.xad_b_id_ = bId;
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.neu_date_&nbsp;&nbsp;&nbsp; = 'now';
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new.aend_date_&nbsp;&nbsp; = 'now';
<br>
end
<br>
&nbsp;;
<br>
<br>
I found db2 syntax which is similar to Derby.
<br>
<br>
create trigger atv_basis
<br>
&nbsp; no cascade before
<br>
&nbsp; insert on ADM_TR_VORGABE_BASIS
<br>
&nbsp; referencing new as new
<br>
&nbsp; for each row mode db2sql
<br>
&nbsp; begin atomic
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set new.neu_datum_&nbsp;&nbsp;&nbsp; = CURRENT_DATE;
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set new.aend_datum_&nbsp;&nbsp; = CURRENT_DATE;
<br>
<br>
end;
<br>
<br>
This isn't working too.
<br>
Can somebody help me out?
<br>
<br>
<br>
mfg
<br>
Joachim Stumpf&nbsp;&nbsp; DB2 Technical presales support
<br>
Tel.: (+49) -7034-15-3276&nbsp;&nbsp;&nbsp;&nbsp; Fax:&nbsp;&nbsp; ([phone]
<br>
Internet: <a class="moz-txt-link-abbreviated" href="mailto:stumpfj-tA70FqPdS9bQT0dZR+***@public.gmane.org">stumpfj-tA70FqPdS9bQT0dZR+***@public.gmane.org</a>&nbsp;&nbsp;&nbsp; Mobil: ([phone]
<br>
<br>
Developersite: <a class="moz-txt-link-freetext" href="http://www.ibm.com/software/data/developer">http://www.ibm.com/software/data/developer</a>
<br>
Forum:
<a class="moz-txt-link-freetext" href="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&amp;cat=19</a>
<br>
</blockquote>
<br>
<br>
<br>
</blockquote>
</body>
</html>

[Joachim G Stumpf]

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: [phone]
Internet: stumpfj-tA70FqPdS9bQT0dZR+***@public.gmane.org Mobil: ([phone]

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-***@public.gmane.org>
To: Derby Discussion <derby-user-***@public.gmane.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-N3a1/***@public.gmane.org%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: [phone]
Internet: stumpfj-tA70FqPdS9bQT0dZR+***@public.gmane.org Mobil: ([phone]

Developersite: http://www.ibm.com/software/data/developer
Forum:
http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

[Jean T. Anderson]

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

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer
http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19
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
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
_
Often when I'm looking for an example on how to do something, I look at
_
__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
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: [phone]
([phone]
Developersite: _http://www.ibm.com/software/data/developer_
_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>

[Satheesh Bandaram]

No, I don't think so.. It does solve many other problems though...

Satheesh

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

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer
http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19
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
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
_
Often when I'm looking for an example on how to do something, I look
_
__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
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: [phone]
([phone]
Developersite: _http://www.ibm.com/software/data/developer_
_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>

[Joachim G Stumpf]

Hi Satheesh , Hi Jean,
I realized that
1. BEFORE INSERT/UPDATE Trigger doesn't work changing data in the table
it's defined for.
2. An AFTER INSERT Trigger works , but is a second (update) transaction.
This doesn't allow to use an AFTER UPDATE Trigger , because this Trgger
would be fired by the SFTER INSERT Trigger with update.

So my outcome from this exercise are two Requirements:

1. Allow a BEFORE INSERT/UPDATE Trigger to change/add data of the insert
or update statements to the current table..
This is for functionality and Performance , because we are then able to
change and insert data in one transaction.

2. Allow multiple Statements in one Trigger with BEGIN and END. This makes
it easier to code and enhances performance because it reduces lines of
code.

My workaround are
1. use only one AFTER Transaction Trigger , which causes two
transactions.
2. Avoid or move the second trigger to somewhere els if possible.
3. Use Default values if possible like Current_date which only works for
inserts.

mfg
Joachim Stumpf DB2 Technical presales support
Tel.: (+49) -7034-15-3276 Fax: [phone]
Internet: stumpfj-tA70FqPdS9bQT0dZR+***@public.gmane.org Mobil: ([phone]

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-***@public.gmane.org>
To: Derby Discussion <derby-user-***@public.gmane.org>
cc:
Subject: Re: Derby and Triggers


No, I don't think so.. It does solve many other problems though...

Satheesh

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

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer

http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

Post by Jean T. Anderson

Post by Joachim G Stumpf
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
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
_
Often when I'm looking for an example on how to do something, I look
_

__http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_

Post by Jean T. Anderson

Post by Joachim G Stumpf
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
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: [phone]
([phone]
Developersite: _http://www.ibm.com/software/data/developer_

_http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19_

Post by Jean T. Anderson

Post by Joachim G Stumpf
<

http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

[Joachim G Stumpf]

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

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer

http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

Post by Joachim G Stumpf
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
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
_
Often when I'm looking for an example on how to do something, I look at
_

__http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_

Post by Joachim G Stumpf
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
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: [phone]
([phone]
Developersite: _http://www.ibm.com/software/data/developer_

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

[Jean T. Anderson]

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

Post by Satheesh Bandaram
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
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

Post by Joachim G Stumpf
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: [phone]
Developersite: http://www.ibm.com/software/data/developer

http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19

Post by Joachim G Stumpf
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
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
_
Often when I'm looking for an example on how to do something, I look at
_

__http://svn.apache.org/viewcvs.cgi/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_

Post by Joachim G Stumpf
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
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: [phone]
([phone]
Developersite: _http://www.ibm.com/software/data/developer_

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