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 Alex <al...@zoho.com> on 2014/12/12 10:50:37 UTC
Unexpected behavior of WHEN clause in CREATE TRIGGER statement
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/
Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Posted by Mamta Satoor <ms...@gmail.com>.
Created DERBY-6783 for UPDATE TRIGGER with WHEN clause behavior.
On Fri, Dec 12, 2014 at 1:43 PM, Kevin Luo <ke...@gmail.com> wrote:
> Well at first I thought it was the reply to my earlier question. It’s
> not. My first hour in the maillist loop so bear with the newbie’s snoopy.
> In the following is the copy of my earlier email to the derby faq group:
>
>
>
> >>>
>
>
>
> WriteDdlToDatabase Verbose:
>
> >>….
>
> >>Returning connection org.apache.commons.dbcp.PoolableConnection@fc23cf
> to data source.
>
> >>Remaining connections: None
>
>
>
> Exception:
>
> F:\NBProject\DBTool\dist\build.xml:33: which is* <ddlToDatabase
> schemaFile="project-schema.xml" verbosity="debug">*
>
> org.apache.ddlutils.io.DataSinkException: java.sql.SQLException:
> Connection is closed.
>
> at
> org.apache.ddlutils.io.DataToDatabaseSink.end(DataToDatabaseSink.java:221)
>
> at
> org.apache.ddlutils.task.WriteDataToDatabaseCommand.execute(WriteDataToDatabaseCommand.java:187)
>
> at
> org.apache.ddlutils.task.DatabaseTaskBase.executeCommands(DatabaseTaskBase.java:341)
>
> at
> org.apache.ddlutils.task.DatabaseTaskBase.execute(DatabaseTaskBase.java:381)
>
> at
> org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)
>
> at sun.reflect.GeneratedMethodAccessor120.invoke(Unknown Source)
>
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>
> at java.lang.reflect.Method.invoke(Method.java:601)
>
> at
> org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
>
> at org.apache.tools.ant.Task.perform(Task.java:348)
>
> at org.apache.tools.ant.Target.execute(Target.java:392)
>
> at org.apache.tools.ant.Target.performTasks(Target.java:413)
>
> at
> org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
>
> at org.apache.tools.ant.Project.executeTarget(Project.java:1368)
>
> at
> org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
>
> at org.apache.tools.ant.Project.executeTargets(Project.java:1251)
>
> at
> org.apache.tools.ant.module.bridge.impl.BridgeImpl.run(BridgeImpl.java:283)
>
> at
> org.apache.tools.ant.module.run.TargetExecutor.run(TargetExecutor.java:541)
>
> at
> org.netbeans.core.execution.RunClassThread.run(RunClassThread.java:153)
>
> Caused by: java.sql.SQLException: Connection is closed.
>
>
>
> I followed the instructions every steps away written in the derby official
> site http://db.apache.org/derby/integrate/db_ddlutils.html. And my derby
> db is well configured can export db to xml with no glitches, Can anyone
> please shed some lights on where could it possibly go wrong?
>
> Thanks
>
> Kev
>
>
>
>
> ------------------------------
>
> *From:* Mamta Satoor [mailto:msatoor@gmail.com]
> *Sent:* Saturday, December 13, 2014 4:36 AM
> *To:* Derby Discussion
>
> *Subject:* Re: Unexpected behavior of WHEN clause in CREATE TRIGGER
> statement
>
>
>
> 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 <ms...@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 <al...@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 <ms...@gmail.com> <ms...@gmail.com>
> *Sent:* Friday, December 12, 2014 10:07PM
> *To:* Derby Discussion <de...@db.apache.org>
> <de...@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 <al...@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*
>
>
>
>
>
RE: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Posted by Kevin Luo <ke...@gmail.com>.
Well at first I thought it was the reply to my earlier question. It's not.
My first hour in the maillist loop so bear with the newbie's snoopy. In the
following is the copy of my earlier email to the derby faq group:
>>>
WriteDdlToDatabase Verbose:
>>..
>>Returning connection org.apache.commons.dbcp.PoolableConnection@fc23cf to
data source.
>>Remaining connections: None
Exception:
F:\NBProject\DBTool\dist\build.xml:33: which is <ddlToDatabase
schemaFile="project-schema.xml" verbosity="debug">
org.apache.ddlutils.io.DataSinkException: java.sql.SQLException: Connection
is closed.
at
org.apache.ddlutils.io.DataToDatabaseSink.end(DataToDatabaseSink.java:221)
at
org.apache.ddlutils.task.WriteDataToDatabaseCommand.execute(WriteDataToDatab
aseCommand.java:187)
at
org.apache.ddlutils.task.DatabaseTaskBase.executeCommands(DatabaseTaskBase.j
ava:341)
at
org.apache.ddlutils.task.DatabaseTaskBase.execute(DatabaseTaskBase.java:381)
at
org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)
at sun.reflect.GeneratedMethodAccessor120.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
at org.apache.tools.ant.Task.perform(Task.java:348)
at org.apache.tools.ant.Target.execute(Target.java:392)
at org.apache.tools.ant.Target.performTasks(Target.java:413)
at
org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
at org.apache.tools.ant.Project.executeTarget(Project.java:1368)
at
org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.j
ava:41)
at org.apache.tools.ant.Project.executeTargets(Project.java:1251)
at
org.apache.tools.ant.module.bridge.impl.BridgeImpl.run(BridgeImpl.java:283)
at
org.apache.tools.ant.module.run.TargetExecutor.run(TargetExecutor.java:541)
at
org.netbeans.core.execution.RunClassThread.run(RunClassThread.java:153)
Caused by: java.sql.SQLException: Connection is closed.
I followed the instructions every steps away written in the derby official
site http://db.apache.org/derby/integrate/db_ddlutils.html. And my derby db
is well configured can export db to xml with no glitches, Can anyone please
shed some lights on where could it possibly go wrong?
Thanks
Kev
_____
From: Mamta Satoor [mailto:msatoor@gmail.com]
Sent: Saturday, December 13, 2014 4:36 AM
To: Derby Discussion
Subject: Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
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 <ms...@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 <al...@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 <ma...@gmail.com> <ms...@gmail.com>
Sent: Friday, December 12, 2014 10:07PM
To: Derby Discussion <ma...@db.apache.org>
<de...@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 <al...@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
Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Posted by Mamta Satoor <ms...@gmail.com>.
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 <ms...@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 <al...@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 <ms...@gmail.com> <ms...@gmail.com>
>> *Sent:* Friday, December 12, 2014 10:07PM
>> *To:* Derby Discussion <de...@db.apache.org>
>> <de...@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 <al...@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*
>>
>
>
Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Posted by Mamta Satoor <ms...@gmail.com>.
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 <al...@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 <ms...@gmail.com> <ms...@gmail.com>
> *Sent:* Friday, December 12, 2014 10:07PM
> *To:* Derby Discussion <de...@db.apache.org>
> <de...@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 <al...@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*
>
Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Posted by Alex <al...@zoho.com>.
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 <ms...@gmail.com>
*Sent:* Friday, December 12, 2014 10:07PM
*To:* Derby Discussion <de...@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
<ma...@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 <http://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/
Re: Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Posted by Mamta Satoor <ms...@gmail.com>.
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 <al...@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*
>