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* >> > >
