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/09/01 15:36:59 UTC
Re: Derby and Triggers
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