[DERBY-6783] WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Fix Version/s: 10.12.1.1
• Component/s: SQL
• Urgency: Normal
• Assignee: Abhinav Gupta
• Reporter: Mamta A. Satoor
• Created: 12/Dec/14 22:05
• Updated: 01/Oct/16 17:53
• Resolved: 18/Jul/15 18:25

Description

Following sql script was shared on derby-user(http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/[e-mail]%3e).

The UPDATE TRIGGER with the WHEN clause below does not fire as expected. Same script works fine on DB2.
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;

Issue Links

• relates to: DERBY-6726 NPE from trigger
• relates to: DERBY-534 Support use of the WHEN clause in CREATE TRIGGER statements