Hi Bryan, A possible solution that occurred to me was, if we can change the structure of DataDictionaryImpl.getTriggerActionString() such that the order of columns in trigger's REFERENCING clause is changed.
Instead of looking up at the columns in action node first, if we put the columns in after clause before and have a structure (Status, Id) The column position of status will be same for the trigger's When clause, i.e. (Status) and our problem, I suppose will be solved. What I haven't been able to think as of now is that, will it give rise to any other kind of bug. I'm studying DataDictionaryImpl.getTriggerActionString to see if it's possible to change the structure of the string in this way. On Sat, May 30, 2015 at 1:11 AM, Bryan Pendleton (JIRA) <j...@apache.org> wrote: > > [ > 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/%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) >
