Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Wed, 31 Aug 2005 13:59:53 +0200
From: Kristian Waagan <Kristian.Waagan@Sun.COM>
Subject: Re: Derby and Triggers
In-reply-to: 
 <OFDE6D5006.39072611-ONC125706E.003032FD-C125706E.003067AA@de.ibm.com>
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <43159BB9.3070904@Sun.com>
Organization: Sun Microsystems Inc.
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US; rv:1.7.10) Gecko/20050720
 Thunderbird/1.0.6 Mnenhy/0.7.2.0
References: 
 <OFDE6D5006.39072611-ONC125706E.003032FD-C125706E.003067AA@de.ibm.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