Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII
Date: Fri, 09 Apr 2010 10:02:34 +0200
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Possible bug with cascade delete and triggers
In-reply-to: <1efc5fe73cc3627898fea58058f65180.squirrel@alstongrove.com>
Sender: Knut.Hatlen@Sun.COM
To: derby-user@db.apache.org
Message-id: <4BBEDF1A.9050303@sun.com>
References: <1270731606.61985.ezmlm@db.apache.org>
 <1efc5fe73cc3627898fea58058f65180.squirrel@alstongrove.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US;
 rv:1.9.1.9) Gecko/20100318
 Lightning/1.0b1 Thunderbird/3.0.4

On 04/ 8/10 03:32 PM, Eric Long wrote:
> I think I've encountered a bug, and I'm looking to confirm it is a bug and find any possible
> workarounds for it.  I am using Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07.  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))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
>
> 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.

Hi Eric,

I see this error too when I follow the steps you posted. I agree that it
looks like a bug. It would be great if you could file a bug report at
https://issues.apache.org/jira/browse/DERBY so that we don't lose track
of the issue. Thanks.

-- 
Knut Anders