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 22:13:29 GMT
Created DERBY-6783 for UPDATE TRIGGER with WHEN clause behavior.

On Fri, Dec 12, 2014 at 1:43 PM, Kevin Luo <kevin.cy.luo@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 <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