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

Delete cascade causes NULL values inserted into table when after delete Trigger fires

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.1.1.0, 10.2.1.6
    • 10.2.1.6
    • SQL
    • None

    Description

      When an after delete trigger which inserts into a table is created on a table that has a foreign key that references a primary key and uses the on delete cascade constraint, nulls are inserted into the table by the trigger.

      The SQL below shows that the cascade delete works correctly:

      ij> CREATE TABLE TABLE1 ( X INT PRIMARY KEY );
      0 rows inserted/updated/deleted
      ij> CREATE TABLE TABLE1_DELETIONS ( X INT );
      0 rows inserted/updated/deleted
      ij> CREATE TABLE TABLE2 (
      Y INT,
      CONSTRAINT Y_AND_X FOREIGN KEY(Y) REFERENCES TABLE1(X) ON DELETE CASCADE
      );
      0 rows inserted/updated/deleted
      ij> CREATE TABLE TABLE2_DELETIONS ( Y INT );
      0 rows inserted/updated/deleted
      ij> INSERT INTO TABLE1 VALUES (0);
      1 row inserted/updated/deleted
      ij> INSERT INTO TABLE2 VALUES (0);
      1 row inserted/updated/deleted
      ij> INSERT INTO TABLE1 VALUES (1);
      1 row inserted/updated/deleted
      ij> INSERT INTO TABLE2 VALUES (1);
      1 row inserted/updated/deleted
      ij> select * from table1;
      X
      -----------
      0
      1

      2 rows selected
      ij> select * from table2;
      Y
      -----------
      0
      1

      2 rows selected
      ij> DELETE FROM TABLE1;
      2 rows inserted/updated/deleted
      ij> select * from table1;
      X
      -----------

      0 rows selected
      ij> select * from table2;
      Y
      -----------

      0 rows selected

      Now insert the rows again, create the triggers, delete the rows from the primary key table, verify the cascade delete worked and observe the values in the tables used by the triggers:

      ij> INSERT INTO TABLE1 VALUES(0);
      1 row inserted/updated/deleted
      ij> INSERT INTO TABLE2 VALUES(0);
      1 row inserted/updated/deleted
      ij> INSERT INTO TABLE1 VALUES(1);
      1 row inserted/updated/deleted
      ij> INSERT INTO TABLE2 VALUES(1);
      1 row inserted/updated/deleted
      ij> CREATE TRIGGER TRIGGER1
      AFTER DELETE ON TABLE1
      REFERENCING OLD AS OLD_ROW
      FOR EACH ROW MODE DB2SQL
      INSERT INTO TABLE1_DELETIONS VALUES (OLD_ROW.X);
      0 rows inserted/updated/deleted
      ij> CREATE TRIGGER TRIGGER2
      AFTER DELETE ON TABLE2
      REFERENCING OLD AS OLD_ROW
      FOR EACH ROW MODE DB2SQL
      INSERT INTO TABLE2_DELETIONS VALUES (OLD_ROW.Y);
      0 rows inserted/updated/deleted
      ij> DELETE FROM TABLE1;
      2 rows inserted/updated/deleted
      ij> select * from TABLE1;
      X
      -----------

      0 rows selected
      ij> select * from TABLE2;
      Y
      -----------

      0 rows selected
      ij> SELECT * FROM TABLE1_DELETIONS;
      X
      -----------
      0
      1

      2 rows selected
      ij> SELECT * FROM TABLE2_DELETIONS;
      Y
      -----------
      NULL
      NULL

      The TABLE2_DELETIONS table contains NULLs instead of the correct values which should be 0 and 1.

      Attachments

        1. derby-1064.diff
          4 kB
          Fernanda Pizzorno
        2. derby-1064.stat
          0.3 kB
          Fernanda Pizzorno

        Activity

          People

            fernanda Fernanda Pizzorno
            slc Susan Cline
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: