db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Date Fri, 12 Dec 2014 20:35:37 GMT
I ran the repro script you provided against DB2 and it worked fine so
definitely seems like a problem with Derby. I will go ahead and add a jira
issue for it.

On Fri, Dec 12, 2014 at 11:40 AM, Mamta Satoor <msatoor@gmail.com> wrote:

> That's right, it was introduced in 10.11.
>
> I looked at the manual for CREATE TRIGGER
> http://db.apache.org/derby/docs/10.11/ref/rrefsqlj43125.html and for the
> when clause, there needs to be a Boolean expression which is explained at
> http://db.apache.org/derby/docs/10.11/ref/rrefsqlj23075.html#rrefsqlj23075. I
> am looking there to see if the syntax for Boolean expression in the CREATE
> TRIGGER eg you provided is incorrect but from the initial looking, it looks
> like you have it correct.
>
> On Fri, Dec 12, 2014 at 11:34 AM, Alex <alex3kov@zoho.com> wrote:
>
>>  Reference Manual says that WHEN clause has no meaning in releases prior
>> to 10.11:
>> http://db.apache.org/derby/docs/10.11/ref/index.html
>>
>>  ------------------------------
>>
>> *From:* Mamta Satoor <msatoor@gmail.com> <msatoor@gmail.com>
>> *Sent:* Friday, December 12, 2014 10:07PM
>> *To:* Derby Discussion <derby-user@db.apache.org>
>> <derby-user@db.apache.org>
>> *Subject:* Re: Unexpected behavior of WHEN clause in CREATE TRIGGER
>> statement
>>  I get the same behavior. I will give it a try on earlier releases and
>> report the results
>>
>> Mamta
>>
>> On Fri, Dec 12, 2014 at 1:50 AM, Alex <alex3kov@zoho.com> wrote:
>>
>>>  Hello,
>>> In the below example, I expect trigger to fire and update the
>>> 'done_date' column after update of 'status', but it doesn't. The database
>>> is a freshly created 10.11. Is this a bug in derby, or am I doing something
>>> wrong?
>>>
>>> ij version 10.11
>>> ij> connect 'jdbc:derby:MyDbTest;create=true';
>>> ij> CREATE TABLE t1 (id INTEGER, done_date DATE, status CHAR(1));
>>> 0 rows inserted/updated/deleted
>>> ij> CREATE TRIGGER tr1 AFTER UPDATE OF status ON t1 REFERENCING NEW AS
>>> newrow FOR EACH ROW WHEN (newrow.status='d') UPDATE t1 SET
>>> done_date=current_date WHERE id=newrow.id;
>>> 0 rows inserted/updated/deleted
>>> ij> insert into t1 values (1, null, 'a');
>>> 1 row inserted/updated/deleted
>>> ij> SELECT * FROM t1;
>>> ID         |DONE_DATE |STA&
>>> ---------------------------
>>> 1          |NULL      |a
>>>
>>> 1 row selected
>>> ij> UPDATE t1 SET status='d';
>>> 1 row inserted/updated/deleted
>>> ij> SELECT * FROM t1;
>>> ID         |DONE_DATE |STA&
>>> ---------------------------
>>> 1          |NULL      |d
>>>
>>> 1 row selected
>>> ij> exit;
>>> --
>>> *--Regards, Alex*
>>>
>>
>>  *--Regards, Alex*
>>
>
>

Mime
View raw message