db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alex <alex3...@zoho.com>
Subject Unexpected behavior of WHEN clause in CREATE TRIGGER statement
Date Fri, 12 Dec 2014 09:50:37 GMT
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/

Mime
View raw message