db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Eric Long" <el...@jumpmind.com>
Subject Possible bug with cascade delete and triggers
Date Thu, 08 Apr 2010 13:32:18 GMT
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 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

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
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.


View raw message