db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fernanda Pizzorno (JIRA)" <derby-...@db.apache.org>
Subject [jira] Resolved: (DERBY-1064) Delete cascade causes NULL values inserted into table when after delete Trigger fires
Date Fri, 07 Jul 2006 08:37:31 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1064?page=all ]
     
Fernanda Pizzorno resolved DERBY-1064:
--------------------------------------

    Resolution: Fixed
    Derby Info:   (was: [Patch Available])

> 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, 10.2.0.0
>     Reporter: Susan Cline
>     Assignee: Fernanda Pizzorno
>      Fix For: 10.2.0.0
>  Attachments: derby-1064.diff, derby-1064.stat
>
> 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