db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joachim G Stumpf <joachim.stu...@de.ibm.com>
Subject Re: Derby and Triggers
Date Tue, 06 Sep 2005 10:21:00 GMT
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:   (+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
Please respond to "Derby Discussion" <derby-user@db.apache.org> 
To:     Derby Discussion <derby-user@db.apache.org>
cc:      
Subject:        Re: Derby and Triggers


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

Satheesh

Jean T. Anderson 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
>
> Joachim G Stumpf wrote:
>
>>
>> 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:   (+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
>>
>>
>> Please respond to "Derby Discussion" <derby-user@db.apache.org>
>>
>> To:        Derby Discussion <derby-user@db.apache.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_
>> <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
>>
>> 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@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_ <mailto: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_
>> <
http://www-106.ibm.com/developerworks/forums/dw_forum.jsp?forum=492&cat=19
>
>>
>>
>>
>>
>
>
>



Mime
View raw message