[
https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14565277#comment-14565277
]
Bryan Pendleton commented on DERBY-6783:
----------------------------------------
My theory is that the root cause of this bug is the following code from
CreateTriggerNode.bindReferencesClause():
//Now that we have verified that are no invalid column references
//for trigger columns, let's go ahead and transform the OLD/NEW
//transient table references in the trigger action sql.
transformedActionText = getDataDictionary().getTriggerActionString(a
ctionNode,
oldTableName,
newTableName,
originalActionText,
referencedColInts,
referencedColsInTriggerAction,
actionNode.getBeginOffset(),
triggerTableDescriptor,
triggerEventMask,
true,
actionTransformations);
// If there is a WHEN clause, we need to transform its text too.
if (whenClause != null) {
transformedWhenText =
getDataDictionary().getTriggerActionString(
whenClause, oldTableName, newTableName,
originalWhenText, referencedColInts,
referencedColsInTriggerAction,
whenClause.getBeginOffset(),
triggerTableDescriptor, triggerEventMask, true,
whenClauseTransformations);
}
Note that this code calls getTriggerActionString() twice.
The first time, we are working with the Trigger "action node", which references
the column "id":
UPDATE t1 SET done_date=current_date WHERE id=newrow.id;
and also references the column "status":
AFTER UPDATE OF status ON t1
The second time, we are working with the Trigger "when node", which references
the column "status":
WHEN (newrow.status='d')
and also references the column "status":
AFTER UPDATE OF status ON t1
The net result is that, the first time through, we determine that we will have
two columns (id, status),
but the second time through, we incorrectly determine that the 'newrow' will
have only one column (status),
and therefore we generate the reference to column 1, thinking we will be
referencing the column "status",
but instead we end up referencing the column "id".
To fix this bug, we have to somehow change the compilation of the trigger so
that it
computes the set of columns that we will need in 'newrow' exactly once, and use
that
set of columns for compiling both the action node and the when node, instead of
computing their column sets independently.
> 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)
