[
https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-6783:
-----------------------------------
Description:
Following sql script was shared on
derby-user(http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%[email protected]%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;
was:
Following sql script was shared on derby-user. The UPDATE TRIGGER with the
WHEN clause 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;
> WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script
> below
> --------------------------------------------------------------------------------
>
> Key: DERBY-6783
> URL: https://issues.apache.org/jira/browse/DERBY-6783
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.1.1
> Reporter: Mamta A. Satoor
>
> Following sql script was shared on
> derby-user(http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%[email protected]%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;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
