[ http://issues.apache.org/jira/browse/DERBY-1064?page=all ]
Fernanda Pizzorno reassigned DERBY-1064: ---------------------------------------- Assign To: Fernanda Pizzorno > 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 > Assignee: Fernanda Pizzorno > > 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