db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: trigger with cascade delete problem
Date Thu, 12 Jun 2014 15:05:18 GMT
On 6/12/14 5:33 AM, Tim Dudgeon wrote:
> OK, so here's a simplified example. Didn't want to raise a JIRA as I'm 
> really not sure this is a bug.
>
> Tim
>
> drop table MAIN;
> drop table DELETIONS;
> drop table CHANGED;
>
> create table MAIN (
>     MAIN_ID INT generated always as identity,
>     GROUPING_ID INT,
>     NAME VARCHAR(50)
> );
>
> create table DELETIONS (
>     DELETIONS_ID int generated always as identity,
>     MAIN_ID INT,
>     NAME VARCHAR(50),
>     WHENITHAPPENED TIMESTAMP
> );
>
> create table CHANGED (
>     CHANGED_ID INT generated always as identity,
>     MAIN_ID INT,
>     NAME VARCHAR(50),
>     WHENITHAPPENED TIMESTAMP
> );
>
> insert into MAIN (GROUPING_ID, NAME) values
> (1, 'banana'),
> (1, 'lemon'),
> (1, 'grapefruit'),
> (2, 'apple'),
> (2, 'broccoli'),
> (3, 'strawberry'),
> (3, 'redcurrent'),
> (3, 'cherry');
>
> select * from MAIN;
>
> -- this trigger records what has been deleted from MAIN
> CREATE TRIGGER trg_delete1 AFTER DELETE ON MAIN
> REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL
> INSERT into CHANGED (MAIN_ID, NAME, WHENITHAPPENED)
> SELECT MAIN_ID, NAME, CURRENT_TIMESTAMP FROM OLD;
>
> -- tries to record all rows with the same GROUPING_ID as any of the 
> deleted rows
> CREATE TRIGGER trg_delete2 AFTER DELETE ON MAIN
> REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT MODE DB2SQL
> INSERT into DELETIONS (MAIN_ID, NAME, WHENITHAPPENED)
> SELECT MAIN.MAIN_ID, MAIN.NAME, CURRENT_TIMESTAMP FROM MAIN, OLD
> WHERE MAIN.GROUPING_ID = OLD.GROUPING_ID;
>
> delete from MAIN where MAIN_ID = 1;
> select * from MAIN; -- banana deleted as expected
> select * from CHANGED; -- banana present as expected
> select * from DELETIONS; -- lemon and grapefruit present as expected, 
> but no banana
Hi Tim,

This result looks correct to me. The trg_delete2 trigger fires AFTER the 
deletion and inside the same transaction. So it sees the state of the 
MAIN table AFTER the banana tuple was deleted. From its perspective, the 
banana table should not appear in the join.

Or did I misunderstand your question?

Hope this helps,
-Rick
>
>
>
>
>
>


Mime
View raw message