db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Susan Cline (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1064) Delete cascade causes NULL values inserted into table when after delete Trigger fires
Date Mon, 27 Feb 2006 23:22:54 GMT
Delete cascade causes NULL values inserted into table when after delete Trigger fires
-------------------------------------------------------------------------------------

         Key: DERBY-1064
         URL: http://issues.apache.org/jira/browse/DERBY-1064
     Project: Derby
        Type: Bug
  Components: SQL  
    Versions: 10.1.1.0    
    Reporter: Susan Cline


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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message