Created DERBY-6783 for UPDATE TRIGGER with WHEN clause behavior. On Fri, Dec 12, 2014 at 1:43 PM, Kevin Luo <[email protected]> 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:[email protected]] > *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 <[email protected]> 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 <[email protected]> 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 <[email protected]> <[email protected]> > *Sent:* Friday, December 12, 2014 10:07PM > *To:* Derby Discussion <[email protected]> > <[email protected]> > *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 <[email protected]> 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* > > > > >
