[
https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14604846#comment-14604846
]
Abhinav Gupta commented on DERBY-6783:
--------------------------------------
A short description for workingPatch.patch
The intent of this patch is to successfully run sql queries in derby that
trigger updates in a table, affecting only a certain rows of data that is
determined by the when clause. This patch tries to cover a wide range of
possible ways in which rows are conditionally updated after an event. Before
this patch such trigger commands failed to execute correctly and left the data
in an inconsistent state.
> 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,
> workingPatch.patch
>
>
> 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)
