Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <23778003.1151399011433.JavaMail.jira@brutus>
Date: Tue, 27 Jun 2006 09:03:31 +0000 (GMT+00:00)
From: "Fernanda Pizzorno (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-1064) Delete cascade causes NULL values
 inserted into table when after delete Trigger fires
In-Reply-To: <30642303.1141082574909.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

     [ http://issues.apache.org/jira/browse/DERBY-1064?page=all ]

Fernanda Pizzorno updated DERBY-1064:
-------------------------------------

    Attachment: derby-1064.diff
                derby-1064.stat

The problem is happening because the trigger TRIGGER2 is running with the trigger execution context (TEC) of TRIGGER1. Nulls will be inserted because this this is happening before the before and after result sets for the trigger execution context for TRIGGER1 are set.

When deleting a row causes a cascade delete Derby will execute the following steps on both the target table and it's dependent tables:
 (1) get the affected rows; 
 (2) fire before triggers;
 (3) delete the rows;
 (4) fire after triggers.

The problem described in this JIRA issue is caused by the way the execution of before and after triggers happens. The TEC for triggers that are being executed are kept in a Vector. During the execution of a trigger, the TEC used by the trigger will be the last element in this Vector.
During the execution of before triggers, the following steps will happen: 
 (1) for each dependent table, the TEC is added at the end of this Vector and the before trigger event is fired causing the execution of the before trigger on that table;
 (2) the TEC for the main table is added at the end of the Vector and the before trigger event is fired causing the execution of the before trigger on that table.

In the example in this JIRA issue, the Vector would now contain {TEC for TABLE2, TEC for TABLE1}.

During the execution of after triggers, the following steps will happen:
 (1) for each dependent table, the after trigger even is fired causing the execution of the after trigger on that table;
 (2) the after trigger event is fired for the main table, causing the execution of the after trigger on that table;

The content of the Vector has not changed for the execution of the after trigger, so both after triggers execute with the TEC that is the last element of the Vector (TEC for TABLE1). The before and after result sets are still added to the correct TEC, as this happens when the trigger event is fired if there is a trigger for that event. When TRIGGER2 is executed, the before and after result sets are set on the correct TEC (TEC for TABLE2) but the trigger is executed with the wrong TEC (TEC for TABLE1). TEC for TABLE1 at that moment still does not have before and after result sets, causing NULLs to be inserted.

This patch (derby-1064.diff) fixes this problem by adding some steps to the execution of before and after triggers.

For the before trigger this patch will cause the TEC for those triggers that have been executed to be removed from the Vector, and for the after trigger the TEC for each trigger will be added again to the Vector (in the same order as done for before triggers), and will be removed after the trigger is executed.

I have successfully run derbyall with this patch. Can someone please review it?

> 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
>  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