[
https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14564136#comment-14564136
]
Bryan Pendleton commented on DERBY-6783:
----------------------------------------
In DataDictionaryImpl.getTriggerActionString, there is some very complex logic
which computes the "table position" of each column in the trigger versus the
"trigger position" of that column (the terms "table position" and "trigger
position"
are mine; they do not arise directly in the code).
In this code, in our test case, the data structures correctly represent that the
STATUS column is column 3 in the base table:
[junit] Trigger column is STATUS, so columnDescriptor is columnName: STATUS
[junit] columnPosition: 3
[junit] columnType: CHAR(1)
But then, at the point where that method builds the
"triggerColsAndTriggerActionCols"
variable, it thinks that "STATUS" is the only column that will be retrieved,
and so
it sets the column position to 1.
But, in fact, the execution of the query retrieves *two* columns: ID and
STATUS, to
the correct column position should have been 2.
So I think that the bug arises in DataDictionaryImpl.getTriggerActionString.
Interestingly, we seem to call this routine *twice*: once with the set of
columns (ID,STATUS),
and once with only (STATUS) in the column list.
So possibly the flaw is that either (a) we should only be calling this routine
once, or
(b) we should be calling it twice, but in each case the column list should be
(ID,STATUS), but in the second case the column list is wrong, it is only
(STATUS).
Abhinav, perhaps you can focus your attention on
DataDictionaryImpl.getTriggerActionString
for a while and tell me what you think about how this method is interpreting
the trigger
definition in this particular test case.
> 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: diagnostics.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)
