Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-5120

Row from SYSDEPENDS gets deleted when a table has update triggers defined on it and an upate is made to the table

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.2.2.0, 10.8.1.2
    • 10.8.2.2, 10.9.1.0
    • SQL
    • None

    Description

      I have an ij script below which shows that the number of rows in SYSDEPENDS go down by 1 for the following test case after an update is made to a table with update triggers defined on it. Am not sure what kind of problems the missing dependnecy might cause.

      connect 'jdbc:derby:c:/dellater/db1;create=true';
      CREATE TABLE ATDC_13_TAB1(c11 int, c12 int);
      insert into ATDC_13_TAB1 values (1,11);

      create table ATDC_13_TAB2(c21 int, c22 int);
      insert into ATDC_13_TAB2 values (1,11);

      create table ATDC_13_TAB3(c31 int, c32 int);
      insert into ATDC_13_TAB3 values (1,11);

      create table ATDC_13_TAB1_backup(c11 int, c12 int);
      insert into ATDC_13_TAB1_backup values (1,11);

      create trigger ATDC_13_TAB1_trigger_1 after update
      on ATDC_13_TAB1 for each row mode db2sql
      INSERT INTO ATDC_13_TAB1_BACKUP(C11)
      SELECT C21 from ATDC_13_TAB2;

      create trigger ATDC_13_TAB1_trigger_2 after update
      on ATDC_13_TAB1 for each row mode db2sql
      INSERT INTO ATDC_13_TAB1_BACKUP
      SELECT C31, C32 from ATDC_13_TAB3;

      – following shows 14 rows
      select * from sys.sysdepends;
      update ATDC_13_TAB1 set c12=11;
      – following shows only 13 rows

      I tried this on 10.2 and 10.8 and saw the same behavior on both. It seems like the dependency that gets dropped is between the stored prepared statement and a table. Have not spent enough time to find out more details but I thought it is worth pointing out the behavior
      select * from sys.sysdepends;

      Attachments

        1. DERBY5120_patch1_diff.txt
          1 kB
          Mamta A. Satoor
        2. DERBY5120_patch1_stat.txt
          0.2 kB
          Mamta A. Satoor
        3. DERBY5120_patch2_diff.txt
          3 kB
          Mamta A. Satoor
        4. DERBY5120_patch2_stat.txt
          0.3 kB
          Mamta A. Satoor
        5. DERBY5120_patch3_diff.txt
          7 kB
          Mamta A. Satoor
        6. DERBY5120_patch3_stat.txt
          0.4 kB
          Mamta A. Satoor
        7. DERBY5120_patch4_diff.txt
          14 kB
          Mamta A. Satoor
        8. DERBY5120_patch4_stat.txt
          0.5 kB
          Mamta A. Satoor
        9. DERBY5120_patch5_diff.txt
          15 kB
          Mamta A. Satoor
        10. DERBY5120_patch5_stat.txt
          0.5 kB
          Mamta A. Satoor

        Issue Links

          Activity

            People

              mamtas Mamta A. Satoor
              mamtas Mamta A. Satoor
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: