[
https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14597586#comment-14597586
]
Abhinav Gupta commented on DERBY-6783:
--------------------------------------
Hi Bryan,
In the error-stacktrace.out, it shows that the error occurs at line 379 of
TriggerTest.java. That happens to be an update command.
s.executeUpdate("update atdc_16_tab1 set b1=22,c1=222");
If I run the queries of this particular test in ij, I don't get any error
and the output is correct.
So I'm currently trying to understand that if the update command executes
correctly on ij, why does it give an error in the test run.
On Mon, Jun 22, 2015 at 5:21 AM, Bryan Pendleton (JIRA) <j...@apache.org>
> 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: 6783.diff, 6783_allTestsPass.diff, 6783_moreTests.diff,
> 6783_moreTests_bryan.diff, 6783_newTest.diff, 6783_newTest_bryan.diff,
> 6783_newTests.diff, cleanedUpDiff.patch, diagnostics.diff,
> error-stacktrace.out, sortFunction.diff, testTriggerWhenClause.diff
>
>
> Following sql script was shared on
> derby-user(http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%3c548aba6d.8000...@zoho.com%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)
