db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: trigger with cascade delete problem
Date Mon, 16 Jun 2014 08:07:47 GMT
On 13/06/2014 13:59, Rick Hillegas wrote:

> Maybe
> you can share a simplified problem which shows why this aggregation is
> so tricky.
> Thanks,
> -Rick

Hi Rick

Thanks for your patience!
I've tried to create a minimal version that describes the problem:

create table XXX (
     XXX_ID int primary key generated always as identity

create table GROUPING (
     GROUPING_ID int primary key generated always as identity,
     XXX_ID INT,
     constraint GROUPING2XXX foreign key (XXX_ID) references XXX 
(XXX_ID) on delete cascade

create table MAIN (
     MAIN_ID INT generated always as identity,
     YYY_ID INT,
     NAME VARCHAR(50),
     constraint MAIN2GROUPING foreign key (GROUPING_ID) references 
GROUPING (GROUPING_ID) on delete cascade

create table AGGREGATES (
     AGGREGATES_ID int generated always as identity,
     XXX_ID INT,
     YYY_ID INT,

The key parts is that AGGREGATES contains data that is grouped by YYY_ID 
(from MAIN) and XXX_ID (from XXX/GROUPING).
So when a row from MAIN is deleted its CORRESPONDING row in AGGREGATE 
needs updating, which I'm doing by doing a delete followed by an INSERT.
The row to delete is the one defined by the corresponding YYY_ID and 
XXX_ID values. And to get the corresponding XXX_ID values I need to join 
back to the GROUPING table. But in the case of a cascade delete caused 
by deletion of a row in GROUPING the row I need to join on is no longer 
present, as its just been deleted.

And I can't do a cascade delete from GROUPING to AGGREGATES as that 
would delete all AGGREGATES with the corresponding XXX_ID, not just 
those with the correct XXX_IDs.

I hope this is clearer!


View raw message