db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Abhinav Gupta <abhinavgupta2...@gmail.com>
Subject Re: [jira] [Updated] (DERBY-6783) WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below
Date Tue, 16 Jun 2015 21:51:52 GMT
Hi Bryan,

I was trying to understand the truncation error, I changed the values of
the field marks1 and marks2 in the derbyTest-6783_1 and that '20' is
basically the value of the integer field marks1 that I think is being cast
as a char value. But I haven't been able to track where this error is
occurring.


On Fri, Jun 12, 2015 at 6:42 AM, Bryan Pendleton (JIRA) <jira@apache.org>
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_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