Derby
  1. Derby
  2. DERBY-4610

Error attempting delete with cascade and triggers

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
    • Fix Version/s: 10.5.3.1, 10.6.2.1, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
    • Bug behavior facts:
      Regression

      Description

      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.

      This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.

      1. enable_test.diff
        1.0 kB
        Knut Anders Hatlen
      2. junit.diff
        2 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          No work has yet been logged on this issue.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Eric Long
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development