You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Joachim G Stumpf <jo...@de.ibm.com> on 2005/08/31 10:48:42 UTC
Derby and Triggers
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
Re: Derby and Triggers
Posted by Joachim G Stumpf <jo...@de.ibm.com>.
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" <de...@db.apache.org>
To: Derby Discussion <de...@db.apache.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
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
Re: Derby and Triggers
Posted by Kristian Waagan <Kr...@Sun.COM>.
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
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
Re: Derby and Triggers
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
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@mail.gmail.com%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@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