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: [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
