[
https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14557858#comment-14557858
]
Bryan Pendleton commented on DERBY-6783:
----------------------------------------
I was able to download the latest testcase and apply it; when I run it with
ant, I get:
java.sql.SQLSyntaxErrorException: Column 'DONE_DATE' is either not in any table
in the FROM list or appears within a join specification and is outside the scope
of the join specification or appears in a HAVING clause and is not in the GROUP
BY list. If this is a CREATE or ALTER TABLE statement then 'DONE_DATE' is not
a column in the target table.
in fail\Embedded_40\TriggerWhenClauseTest\testDerby6783\error-stacktrace.out
When I change the last query in the new test case from 'SELECT done_date FROM
t1'
to 'SELECT result FROM t1', the new test case then runs, and I get:
junit.framework.AssertionFailedError: Column value mismatch @ column 'RESULT',
row 1:
Expected: >completed<
Found: >null<
in my error-stacktrace.out, which I think is the expected behavior since we
haven't fixed the bug yet.
So I think the new test case demonstrates the bug, once the typo in the last
SQL statement is fixed.
> 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
> Assignee: Abhinav Gupta
> Attachments: TriggerTest.diff, testTriggerWhenClause.diff
>
>
> 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)
