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