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] Commented: (DERBY-4610) Error attempting delete with cascade and triggers
Date Wed, 28 Apr 2010 03:24:37 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861660#action_12861660

Bryan Pendleton commented on DERBY-4610:

Hmmm. That's a fairly subtle interaction. Maybe Dan wasn't thinking about
triggers when he did the DERBY-3049 work?

What does the call stack look like when the repro script reaches the
code at line 251 of EmbedResultSet?

I see the following interesting code at around line 185 of UpdateResultSet:

        ResultDescription resultDescription;
                if(passedInRsd ==null)
                        resultDescription = activation.getResultDescription();
                        resultDescription = passedInRsd;
                ** We NEED a result description when we are going to
                ** to have to kick off a trigger.  In a replicated environment
                ** we don't get a result description when we are replaying
                ** source xacts on the target, which should never be the
                ** case for an UpdateResultSet.
                if (SanityManager.DEBUG)
                        if (resultDescription == null)
                                SanityManager.ASSERT(triggerInfo == null, "triggers need a
result description to pass to result sets given to users");

Would it work to have UpdateResultSet send 'passedInRsd' up to the
superclass constructor, and then EmbedResultSet's constructor could
have similar logic to use either passedInRsd or the activation's resultDescription,
depending on whether passedInRsd was null or not?

> Error attempting delete with cascade and triggers
> -------------------------------------------------
>                 Key: DERBY-4610
>                 URL: https://issues.apache.org/jira/browse/DERBY-4610
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:,,,
>         Environment: Apache Derby and Sun JDK 1.6.0_07
>            Reporter: Eric Long
> The scenario is a parent and child table with a cascade delete and triggers on both tables.
 Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
>    public static void test(String str)
>    {
>    }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to
"testchild", and the triggers will be called for each delete.  The actual result is that the
delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data
value of
> type 'INTEGER'.
> SQLState:  XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error.  If only one trigger
is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables
between Derby databases.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message