[
https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14604889#comment-14604889
]
ASF subversion and git services commented on DERBY-6783:
--------------------------------------------------------
Commit 1688062 from [~bryanpendleton] in branch 'code/trunk'
[ https://svn.apache.org/r1688062 ]
DERBY-6783: WHEN clause in CREATE TRIGGER for UPDATE is not working.
This patch was contributed by Abhinav Gupta (abhinavgupta2004 at gmail dot com)
The main challenge raised by this issue involved the analysis of which
columns are referenced by the trigger. Trigger statements can reference
columns in a number of ways, including the "AFTER UPDATE OF" clause, the
trigger's action statement, and the trigger's "WHEN" clause.
When the WHEN clause support was introduced, this added a new way in which
a TRIGGER could reference columns, and the compiler code which translated
the trigger's logic into specific column references at runtime wasn't
getting the right set of columns.
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)
