db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6783) WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below
Date Fri, 12 Jun 2015 01:12:00 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Bryan Pendleton updated DERBY-6783:
-----------------------------------
    Attachment: 6783_newTest_bryan.diff

Hi Abhinav, I spent some time today studying your latest patch.

I think we are making progress!

I made a few small changes to your patch, and have attached mine.

1) In CreateTriggerNode, I removed the "whenCols" variable, and just
   used the "cols" variable in both places, because I think we want to
   combine the two sets of variables into one list.

2) In DataDictionaryImpl.examineTriggerNodeAndCols(), I added a
   couple lines of code to initialize triggerColsAndTriggerActionCols
   from referencedColsInTriggerAction, if it is passed, so that the
   columns we computed in the first invocation get carried through
   into the second invocation.

3) In DataDictionaryImpl.getTriggerActionString(), I changed the
   handling of "cols = null". We still want to execute most of the code
   in this method even if cols = null, but in that case we need to
   set triggerColsAndTriggerActionCols to contain the entire set of
   columns in the table.

The overall set of changes from your patch to mine was very small;
the easiest way to see this is to "diff the diffs" -- that is, download
your patch and my patch and put them together in a directory
and diff them.

With the patch that I've attached, we now pass all of the test
cases in TriggerWhenClauseTest except for the new "testDerby6783_1"
test, which I think is a brand new test that you have just added. That
test gets the error:

ERROR 22001: A truncation error was encountered trying to shrink CHAR '20' to length 1.

So clearly there are still some issues with our patch, but I felt like
this was good progress and wanted to share it with you.

Do try to download my latest patch and let me know what you think!

> 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_newTest.diff, 6783_newTest_bryan.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/%3c548ABA6D.8000509@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)

Mime
View raw message