Hi Bryan, The reason I had added the sortTriggerColsAndTriggerAction was that, earlier when we had not separated the getTriggerActionString, during the first call of getTriggerActionString, the array triggerColsAndTriggerAction had values [1,2] where 1 referenced to the ID field and 2 referenced the STATUS.
In the second call, the array had the value [1] and it referenced status. The the function that I had added, would put STATUS first in the array during the first call. And would have given STATUS the same column index of 1 when relative column positions are calculated. I think my logic was flawed since all test pass after removing it. There was one test that failed testDerby6783_2 but I just realized that it was due to a typo in the test query. On Fri, Jun 19, 2015 at 10:50 AM, Bryan Pendleton (JIRA) <[email protected]> wrote: > > [ > https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel > ] > > Bryan Pendleton updated DERBY-6783: > ----------------------------------- > Attachment: 6783_moreTests_bryan.diff > > Slightly-tweaked copy of 6783_moreTests.diff; see the discussion > on derby-dev for some background about the change related to > sortTriggerColsAndTriggerActionCols() > > > > 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_moreTests.diff, > 6783_moreTests_bryan.diff, 6783_newTest.diff, 6783_newTest_bryan.diff, > 6783_newTests.diff, diagnostics.diff, sortFunction.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) >
