[ 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
